Posted by cabrenner on 01/08/07 13:22

Thank you both for your suggestions. Yes, I was thinking that BULK
INSERT was not going to be able to handle this. I had thought about
dumping the file into an Access table first, but the file could be very
large (200,000+ rows). I am going to try the Excel spreadsheet idea.

Erland Sommarskog wrote:
> 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

[Back to original message]