question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked ZoeHui-MSFT edited

How to handle Special Characters of CSV file data using SSIS

Hi Team,
Have a CSV input file and want to load into SQL database.
CSV file having some Special Characters is their, want to eliminate Special Characters while loading to DB. Please help on same

Sample Input data like bellow , please help on this
108654-image.png


sql-server-integration-services
image.png (20.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

You can use a Derived Column Transformation with the REPLACE (SSIS Expression) function to remove unwanted characters.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @balanarasimhac-1495,

As Olaf said that you could eliminate Special Characters with derived Column expression in SSIS.

Also you may directly import the csv to sql database with SSIS and then use the RemoveNonAlphabets function to eliminate Special Characters.

 CREATE Function [dbo].[RemoveNonAlphabets](@input VarChar(4000))
 Returns VarChar(4000)
 AS
 Begin
    
     Declare @Alphabets as varchar(50)
     Set @Alphabets = '%[^a-z]%'
     While PatIndex(@Alphabets, @input) > 0
         Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '')
    
     Return @input
 End

A simple sample as shown below.

   select id,dbo.RemoveNonAlphabets([value]) as [value] from test

108727-625.jpg

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




625.jpg (13.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.