Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing commas and quotes from numeric fields in csv file using SSIS Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 1:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 10, 2012 7:16 PM
Points: 12, 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!
Post #1232977
Posted Wednesday, January 11, 2012 4:00 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:52 AM
Points: 634, Visits: 809
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
Post #1233865
Posted Wednesday, January 11, 2012 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,386, Visits: 9,962
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
Post #1233874
Posted Wednesday, January 11, 2012 5:04 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:58 AM
Points: 1,460, Visits: 3,008
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
Post #1233884
Posted Wednesday, January 11, 2012 6:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 10, 2012 7:16 PM
Points: 12, 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.
Post #1234499
Posted Wednesday, May 15, 2013 5:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:00 PM
Points: 29, Visits: 346
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
Post #1453293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse