Bulk Insert CSV files

[dhulbert]

I'm trying to use bulk insert to import a large number of csv files into SQL server 2005. I have a problem in that some of the fileds in som eof the files contain extra commas in the data. The fileds are " delimited so they will import using the import wizard or SSIS packaged but I'm not inclined to create 200+ SSIS packages.

Any ideas how I can get the unwanted commas out or other methods to get the data in.

Cheers.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[foxdev]

If the fields contain quoted strings I'm surprised the embedded commas within the quoted strings are causing a problem.

Are you using a format file?

--------------

http://www.liquidsql.com

SQLS metasearch

 

[dhulbert]

No just using a strraight bulk insert, If I have to create a format file for each one I might as well create a SSIS package to drag each one in whcih I'm trying to avoid doing.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[foxdev]

I had a data file to import (USPS ZIP Code data) that had no line terminators (but otherwise fixed-length), and the ONLY way I could get Bulk Insert to work was with a format file.

If all the CSV files are in the format, then one format file can be used for all. If they are all in different formats...I feel for you.


--------------

http://www.liquidsql.com

SQLS metasearch