Re: format files for use with bulk insert

Ted (r.ted.byers@xxxxxxxxxx) writes:
This works great, but there is a fly in the ointment.

Suppose we have records like:

"field1","field2","field3",4,5

But for which field2 can be null. E.G.

"value11","value12","value13",1,2
"value21",,"value23",12,22
"value31","value32","value33",13,23
"value41",,"value43",14,24

Ouch. Yes, this is a case where you lose.

I don't see a way for a format file to handle this except possibly by
using only a comma or tab or end of record string as the delimiter or
field terminator, leaving the quotes included with the text in the
field, and then use a combination of the functions SUBSTRING and LEN to
remove the leading and trailing quote character after the data has been
loaded.

And this fails is you have that delimiter in the text. I guess the
point with the quotes is to cover the case that the delimiter appears
in a field value.

If you use Perl, you can preprocess the file with:

perl -pi -e "s/\x22,,\x22/\x22,\x22\x22,\x22/g"

although neither this is foolproof: a field may include the string
",," - but is far less probable than a single comma.

It's possible that SQL Server Integration Services can handle this
sort of situation. No, I'm not suggesting that you go back to the
Import wizard, but that you write packages of your own. Unfortunately,
I'm not into SSIS myself at all.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Text Qualifier in a CSV file from results in SQL Server 2k5 Man. S
    ... delimiter, but I can't find a place to put in a text qualifier. ... nifty option that permits you to include the grid header when you ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Query requiring split
    ... each delimiter which is a / ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Query requiring split
    ... each delimiter which is a / ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... thanks but is there a SQL script that can be written without CLR ...
    (microsoft.public.sqlserver.server)
  • Re: INSERT Query problem with Quotes & Apostrophes
    ... I've got front-ends that go against both Jet and SQL Server databases. ... InputText As String, _ ... >> Delimiter, Delimiter & Delimiter) ... dDateTime, ...
    (microsoft.public.access.modulesdaovba)
  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... >I'm sorry that your jerry-rigged spreadsheet program isn't flexible ... Single quotes are used to delimit different syntactic tokens than ... >I'm so sorry that you work for a stupid company; SQL Server is WINNING ... it doesn't take as many IBM mainframes running DB/2 to do the same work ...
    (microsoft.public.excel)