Posted by Erland Sommarskog on 01/06/07 13:49

(cabrenner@optonline.net) writes:
> I am new to SQL Server, and migrating part of an Access application to
> SSE. I am trying to insert a comma delimited file into SSE 2005. I am
> able to run a BULK INSERT statement on a simple file, specifying the
> field (,) and row (\n) terminators. I can also do the same with a
> format file.
>
> Here is the problem. My csv file has 185 columns, with a mixture of
> datatypes. Sometimes, a text field will contain the field delimiter as
> part of the string. In this case (and only in this case) there will be
> double quotes around the string to indicate that the comma is part of
> the field, and not a delimiter.

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

There is now way to get BULK INSERT to handle this file in that shape.
If I were faced with this file, I would write Perl script that replaced
the commas outside the "" with a different delimiter and then removed the
"". And it would not be trivial.

Most other people would probably try to write a package in Integration
Services, but I have never used Integration Services myself. And for your
part - SQL Express does not come with Integration Services, I believe.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]