bcp utility (importing data) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp utility (importing data)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-11 : 00:43:54

Harry writes "I have a problem importing data with the BCP utility.
For many, many hours i have worked to find a way importing underneath data, but without any success.

I have received the following data, which i need to import

"0000000002","000000580","","","AG","","","","","","","","","","","","","","00006","ROLLINS HUDIG HALL SEQ 3, EFFE DATE 1995-11-23"
"0000000002","000000643","","","AG","","","","","","","","","","","","","","00006","ROLLINS HUDIG HALL SEQ 3, EFFE DATE 1995-11-23"

Be aware that a comma can be found in the data of the last column.
The field separator is a comma.

Each column starts and ends with a double quote.

This is the command i use:
bcp ADB_database.dbo.table in d:\mssql7\Script\datas.txt -c -q -t "," -e d:\cerr.log -Uuser -Sserver -Ppassword

I receive the error message:
SQLState = 22001 NativeError = 0
Error = [Microsft][ODBC SQL Server Driver]: String data, right truncation

We are using SQL Server version 7.0 with Windows NT

Thank you in advance.

Harry van der Tol
The Netherlands"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-12 : 15:10:50

unfortunately, you can't use BCP in that situation. BCP is a low-level utility which doesn't recognize the "" field escaping that some software uses when exporting to a CSV format.

DTS does recognize it, though. It should work ok.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-12 : 16:00:27

This article details how to do this using BULK INSERT:

http://www.sqlteam.com/item.asp?ItemID=3207

And I *think* you could write a bcp format file that handles the quotation marks, but you'll have to experiment with that. The article will give you some ideas on how to proceed if you use bcp with a format file (format files are detailed in Books Online, look for "bcp" and all its entries)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources