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.
没有评论:
发表评论