CODEWITHSUNDEEP
csvssis
KhD
KhD

Reputation: 453

Removing commas and quotes from numeric fields in csv file using SSIS

I am creating SSIS package which reads data from a csv file and stores in SQL Server database. There are a few numeric fields in the csv files. They sometimes contain value like "1,008.54"

How do I remove the quotes and comma from the value?

I have successfully separated the rows with this kind of data by using Conditional Split Transformation. (SUBSTRING([Column 9],1,1) == "\"")

After this, I tried using Derived Column Transformation to REPLACE comma and quotes with empty string. But it is not working.

Please advise.

Thanks!

Upvotes: 1

Views: 12553

Answers (1)

David Benham
David Benham

Reputation: 1174

I tested your sample value "1,008.54" in a Data Flow where my source was:

SELECT '"1,008.54"' AS [Column]

I then placed the following expression in a Derived Column Transformation (imitating what you did attempted)

REPLACE(REPLACE(Column,",",""),"\"","")

and successfully achieved your request: Using Derived Column Transformation, REPLACE comma and quotes with empty string.

Here's the result as presented by a data viewer after the Derived Column Transformation:

Column  Derived Column 1
"1,008.54"  1008.54

Upvotes: 2

Related Questions