Hi.

I have a file that is comma delimited. One of the fields contanis strings,
enclosed by double quotes. Is there a way to get BULK INSERT to recognise
these double quotes as string delimiters, and ignore any characters between
them?

I am having issues with double quote-enclosed strings containing newline
characters, since these are being treated as new records.

Cheers

Neil

You could change the ROWTERMINATOR for BULK INSERT, but then you'd have to
reformat the source file.

Can you change the way the source file is created?


ML

I am trying to avoid interfering with the sourcefile at all, since it comes
in sporadically from an outside source. I could possibly ask the supplier of
the data to omit newline characters from within strings, but I would really
rather avoid it.

Currently, importing the file into either Excel or Access allows us to
locate the newlines within fields and get rid of them, but it is a bit of
pre-processing that it would be nice to do without.

Maybe I am going about this the wrong way, and using the wrong tool for the
job. The aim is for the supplier to send us a file, and it is automatically
loaded. Currently we use BULK INSERT. Can you point me at a list of other
ways to import files that might provide the required flexibility?

Cheers,

Neil

Show quoteHide quote

How about XML? Can data be exported as XML at the source?

Or if at least they could use a different character as the row-delimiter.

Bulk insert is quick, but there's at least one major downside - a corrupted
source is detected only *after* the data has been imported. With XML and
linked servers (i.e. text files as linked servers) data can be validated
before the actual insert.


ML

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)