|
|
Posted by Erland Sommarskog on 10/02/50 11:55
Ted (r.ted.byers@rogers.com) 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@sommarskog.se
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
Navigation:
[Reply to this message]
|