2012年2月18日星期六

Bcp

I need to import a CSV file (strings are in quotations) into SQL server using BCP. Some of the fields contain quotations whereas some of them do not. When i try to import of file such as this an error regarding truncation appears and I am not able to import the data.
When I try to import the same file without quotations around the fields it imports correctly. If you have help regarding this it would be greatly appreciated.

"XXX","20040326080100",1040,"121732","100203223","0040227324003"

or

XXX,20040326080100,1040,121732,100203223,004022732 4003

Thanks
DrahosCan they be tab delimited?

The quotes are a pain|||No, actually I receive those delimited files with quoted strings from somewhere.|||Let's see your bcp command?

Did you try and DTS it in?|||I am using this command:

bcp "Received_Data.dbo.XXX" in "Received_CSV.csv" -ffmt_file.fmt -T

Data I am loading is huge and I was recomended to use bcp instead of DTS.
I have tried DTS and it could load the data . There is a choise for 'Text qualifier' - you can choose 'double quoted', so it is OK.

format file is attached

Thanks again
Drahos|||format file is attached|||Is there anyone who can help me with that?|||There's a workaround; tell bcp not to import specific columns. In your case, turn the quotes into columns. The example works for me and on the first column only:

8.0
8
1 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\"," 1 Col001 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 25 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 25 "," 3 Col003 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 25 "," 4 Col004 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 25 "," 5 Col005 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 25 "\r\n" 6 Col006 SQL_Latin1_General_CP1_CI_AS|||Is there anyone who can help me with that?

either use PERL/PYTHON to srip the quotes or use DTS. DTS will not be much slower than BCP but DTS will br 100 times easier to use and maintain.

没有评论:

发表评论