How to handle Special Characters of CSV file data using SSIS

Question

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

Answer 1

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

Answer 2

Hi @Bala Narasimha Challa ,

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  

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