BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql