I am imported a CSV text file (comma) delimeter, produced
from a third party piece of software.

All text fields as surrounded by double quotes

E.G. "SMITH","JOHN",55.12,8.9

I have millions of records and wanted to use BULK INSERT
excectuted as a SQL statement, as this is the fastest.

However, I also want to drop those double quotes.

Any ideas? A DTS package has a "text qualifier" option,
but it seems as if BULK INSERT does not !?!?

Many thanks for any help,

Jonathan.

In article <088b01c3ae00$d499d430$a001280a (AT) phx (DOT) gbl>, JonathanK
<anonymous (AT) discussions (DOT) microsoft.com> writes
You can partially kludge it by using a format file and including the "
as part of the delimiter.

Lookup format files in Books Online, in particular have a look at the
"Using Format Files" topic. Your terminator becomes "","" between the
first and second column for example. (You may need to double those
quotes to escape them.)

You may be able to use dummy columns, such that the terminator is
treated as a single ", which means you have no data in that column, so
as per the format file spec you do not have top assign a destination
column to all source columns.

A text connection and DataPump is easier, and not that much slower if
you set the bulk load options correctly.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org