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
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
You can use a Derived Column Transformation with the REPLACE (SSIS Expression) function to remove unwanted characters.
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
6 people are following this question.
SSIS Scale Out and its services on local group policies
Where do I download SSDT July 2016 Update (ver. 14.0.60629.0) from?
we hope Microsoft can develop databases like mysql
integration services ne figure pas dans la liste des fonctionnalités partagées de sql server
SSIS - getting data from Oracle - not working preview and metadata refresh