SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
khushboo.dudani
khushboo.dudani
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 83
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!
gofrancesc
gofrancesc
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 812
Hello,
you can try to do it in Excel before loading data into SQL Server.
For instance, copy this text and create a new csv file:

a;1234.5;bbbbb
aa;"1,234.5";bbbb
aaa;1234.5;bbb
aaaa;1234.5;bb
aaaaa;1234.5;b

When you open this new file with Excel, the cell B2 has format "Number". If you the second column to format "General" and save the file, all data in the second column will take the same appearance.

Francesc
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14267 Visits: 15974
khushboo.dudani (1/10/2012)
But it is not working.

How is it not working? Error message? What is the error message? Data not transformed as you expected? How is the data being transformed? Also, what expression are you using for your derived column? Have you set it to replace the original column, or to be added as a new column?

John
Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1880 Visits: 3488
I'd normally bring the file in as all text fields not caring about the format then running sql against the data for format or format in a view/procedure and load from that into the target table. In that way its easier to create an error table with any rejected records for data formats not acounted for.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
khushboo.dudani
khushboo.dudani
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 83
Thank you all for replying.



Making the following changes solved the issue:

1. Setting Text Qualifier of Flat File Connection Manager to ".

2. Using REPLACE(REPLACE(Column,",",""),"\"","") in Derived Column Transformation.
Joe Zonum
Joe Zonum
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 461
Grasshopper,

I know this is an old thread. but I am hoping you can help me out. SSIS is new to me and I am having a hard time with it. I have a CVS file with multiple columns, I only need data from 4 columns.

Some columns look like this:

Linux File System-ALL,ALL,ALL,ALL"

the , causing issues for me during the import to SQL. as it reads the wrong column data.

I tried your code but it didn't work for me.
REPLACE(REPLACE(Column,",",""),"\"","")

My column name is Client; what would the expression look like?


Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search