January 10, 2012 at 1:45 am
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!
January 11, 2012 at 4:00 am
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
January 11, 2012 at 4:28 am
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
January 11, 2012 at 5:04 am
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.
January 11, 2012 at 6:24 pm
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.
May 15, 2013 at 5:46 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply