Posted by Erland Sommarskog on 01/06/07 22:22

Erland Sommarskog (esquel@sommarskog.se) writes:
> 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.

In addition to Bernard's post, is not Excel able to read that format?
In such case open in Except, and save as a tab-delimited file and importing
that should be a breeze. (Assuming, of course, there are no tabs in the
data!)

--
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

• Next in forum: Idea: new database object that defines a FROM clause
• Prev in forum: Re: Import flat file into SQL Server 2005 Express
• Thread view: Re: Import flat file into SQL Server 2005 Express

[Reply to this message]