Import CSV by BCP

[Ken Chan]

Dear All

I try to import an CSV file by BCP.
But there is a problem, The CSV file is using " as text qualtifier and /n
as reord terminator
For Example

"B","fdsa","fdsaf"
"B","fdsafsa","fdsafdsaf","fdsfdsa"

And some row have 3 columns and the other have 4 columns.

Format file
8.0
5
1 SQLCHAR 0 100 "," 1 Col001
""
2 SQLCHAR 0 100 "," 2 Col002
""
3 SQLCHAR 0 100 "," 3 Col003
""
4 SQLCHAR 0 100 "," 4 Col004
""
5 SQLCHAR 0 1 "\n" 5 ENDREC
""

Table
CREATE TABLE test (Col001 CHAR(100) NULL
,Col002 CHAR(100) NULL
,Col003 CHAR(100) NULL
,Col004 CHAR(100) NULL
,ENDREC CHAR(1) NULL
)

There is a sample csv file bcp.csv

What should I do? I know there is something wrong in the format file.

Thanks in advance.
Ken

[Peter Floros]

Hi Ken,

I happen to be working on the same problem here Ken (" as qualifier). I still don't have a complete solution but a quick thing is to define you columns 2 bigger and then run an update query to remove the left and right quotes, either with a replace or a substring.

As for having fewer fields than the table definition, I'm not sure how BCP handles this. If there are not too many rows like this use the -e option on BCP to create a file of bad rows. Then use a text editor to stick ,"" on each of these and import them after editing.

I hope this helps and will post a message if I find a better solution.

Peter

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!