BULK INSERT file with quoted strings, but no quotes when NULL

Hello,
I've put together a format file for a text file that I need to import
using ",\"" and "\",\"", etc. as column delimiters. This would normally
cover the whole problem with quoted strings. However, in this
particular file if a string value is NULL then that column doesn't have
the double quotes around it for that record.
Is there any way to handle this using BULK INSERT? I know that I can do
it in DTS, but can it be done with BULK INSERT?
Thanks!
-Tom.I should also point out that some of these files can be a couple
hundred million rows, so I would really prefer not to load them in with
the quotes and then use REPLACE() to remove those quotes.
Thanks,
-Tom.
Aardvark wrote:
> Hello,
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
> Thanks!
> -Tom.|||My suggestion since you do not want to use the REPLACE function is to
strip the " character using the Operating System or a text editor..
Maybe you could use cygwin and SED/AWK.
JD.|||Aardvark (tom_hummel@.hotmail.com) writes:
> I've put together a format file for a text file that I need to import
> using ",\"" and "\",\"", etc. as column delimiters. This would normally
> cover the whole problem with quoted strings. However, in this
> particular file if a string value is NULL then that column doesn't have
> the double quotes around it for that record.
> Is there any way to handle this using BULK INSERT? I know that I can do
> it in DTS, but can it be done with BULK INSERT?
Unless there are some very fortunate circumstances with the file format,
the answer is no. The best suggestion I can give is to write a program
that reads the file, strips the quotes and the uses the bulk-copy API
to insert from variables. But you probably prefer to use DTS instead.
(And what those fortunate cicrumstances may be, I don't really know.)
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.mspx