Batch Insert - delimiters, weird problem.

[MrPeds]

Hi,

I am using SQL Server DTS Batch Insert tool to populate a table from a csv file.

A comma is the column delimiter, CRLF the row delimiter, and the text is separated using quotes. For example:

"col1", "col2", "col3"
"someText", "moreText", "evenMoreText"

However, when i get something like this:

"col1", "col2", "col3"
"someText", "more,Text", "evenMoreText"

The insert does not work properly. The data in the second column is broken up, so instead of getting "more,Text" in one of my SQL tables columns I get "more" in col2 and "Text" in col3, e.g. in my SQL table i get:

"col1", "col2", "col3", "col4"
"someText", "more", "Text", "evenMoreText"

This is clearly wrong. SQL seems to be picking up the comma in a string when it shouldn't be, is there a workaround for this?

Should I simply use a DataTransformation instead?

Thanks,

MrPeds

 

[Kalpha7]

Hi,
Have you checked your source data for fields containing "DOUBLE QUOTES"?

 

[nigelrivett]

It's the way you are defining the import.
To handle quoted strings you need a format file. see

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MrPeds]

Hi,

I got my solution to work in the end, i simply changed the data source to a Text source connection and it went through without any problems.

I used a format file before but that did not seem to make any difference.

MrPeds