BCP and CSV - my conclusions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

There have been several threads here on reading/writing CSV files
using BCP, and I suspect that they will continue to crop up for ever.
The conclusion that I have come to is that you might, if you are
persistent and lucky, be able to get something working. Be prepared,
however, for it to be tricky to achieve, and a little contrived, in a
lot of real-life circumstances. It may even prove to be impossible if
you are really unlucky with your data. So far I have only explored
exporting to CSV, because that's all I have needed to do. If anyone
has wrangled with importing CSVs and come to any useful conclusions,
please post a follow-up.

CSV is more than just putting "," in between all the fields using a
format file and then jumping through a hoop to get the leading and
trailing quotes in as well before and after the first and last fields
respectively.

For a start, numeric fields should not have quotes around them, so
your format file is going to have to be crafted so that each field
terminator is correct in respect of its own data type and the data
type of the following field. Secondly, any double-quotes characters
within a field need to be doubled up, so that the text [a piece of
wood 2" long] becomes "a piece of wood 2"" long". This means that you
can't just give a table name, you have to write an SQL query. It also
makes the query considerably longer, all character fields that might
contain a double-quotes character need to be wrapped in a translation
function that replaces " with "". Now you aren't likely to hit the
8192 character limit for the length of Command Prompt commands, but
this does make for some pretty unwieldy statements.

The icing on the cake is how to deal with embedded carriage return
characters in your data. Excel seems to be able to cope with this;
when it comes across a line-end it seems to check to see if it is
within a field that is enclosed in quotes, and accepts the line-end
character as part of the cell value. I have no idea how BCP would cope
with this - presumably if you are exporting data then it will happily
write the line-end characters out, and it is up to the receiving
program to handle them, as I said Excel seems to do this. As to what
BCP would do with line ends inside a delimited field at the import
stage, I have no idea.

Phil.

.

Relevant Pages

  • Re: BCP and CSV - my conclusions
    ... Tab-delimited format. ... using BCP, and I suspect that they will continue to crop up for ever. ... contain a double-quotes character need to be wrapped in a translation ... Excel seems to be able to cope with this; ...
    (microsoft.public.sqlserver.tools)
  • ANNOUNCE: Text-CSV_XS 0.28
    ... Added t/45_eol.t, eol tests ... surrounding the separation character is removed when parsing. ... even though it violates the CSV specs. ...
    (comp.lang.perl.modules)
  • Re: how can i change the text delimiter
    ... we receive the data in csv format ... ... Defines the character used to quote fields that ... Defines the character that will be used to separate ... Extracts fields from the CSV record in string. ...
    (comp.lang.python)
  • Re: Reading messy files with Fortran
    ... In my case the messy files are csv extracts from a database (whose ... I discovered that Fortran sees spurious EOR markers within ... character fields and I couldn't see a rhyme or reason why. ...
    (comp.lang.fortran)
  • Re: Interating over the characters in a string
    ... double-quote character can be embedded within a string. ... Also, commas can occur in the quoted string too, so Splitmay not work ... >> The problem is I have a CSV parser that will successfully parse out ... > Although Unicode allows surrogate pairs, ...
    (microsoft.public.dotnet.framework)