bcp import - 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

General SQL Server Forums

New to SQL Server Programming

bcp import

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-13 : 18:27:36

Hello, i have migrated from sql2000 to sql2005, i used to use a dts package to import a donotcall list and now i would like to be able to import it via command-line...

i've tried:


bcp mydb.dbo.dnctest in dnctest.txt -f format.txt -SmyDBserv -T

and

bcp mydb.dbo.dnctest in dnctest.txt -c -SmyDBserv -T

both result in separate errors...

here's my format file:


8.0
3
1       SQLCHAR       0       3       ","      1     Col1         ""
2       SQLCHAR       0       7       ","      2     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

the data looks like:
555,5555555{LF}
555,6666666{LF}

any guidence would be greatly appreciated.

Thanks!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 19:08:36

I only see {LF} not {CR}{LF} so the format "\r\n" either have to be changed to "\n" or

bcp mydb.dbo.dnctest in dnctest.txt -SmyDBserv -c -t, -r \n -T

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-13 : 19:24:11

just changed it, and the following happened:

C:\>bcp mydb.dbo.dnctest in dnctest.txt -f format.txt -
SmyDBserv -T
SQLState = S1002, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

and i get:
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat
a-file

if i use your bcp command... any ideas?

also, on the format file, row 3, is the width right? that's just soemthing i found on the net, should it just be 1 or something?

Thanks!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 19:38:19

I also noticed that you only have 2 columns. but there's a 3rd column char(100) in your format file.

you should be able to generate your own format file:

Format file:

bcp mydbsql200.dbo.dnctest format nul -c -f format.txt -c -t, -T

Export File:

bcp mydbsql200.dbo.dnctest out dnctest.txt -f format.txt -T

Import File:

bcp mydbsql2005.dbo.dnctest in dnctest.txt -f format.txt -T

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-13 : 19:52:47

still no dice


C:\>del format.txt

C:\>bcp mydb.dbo.dnc format nul -c -f format.txt -t, -T -Smydbserv

C:\>bcp mydb.dbo.dnc in dnctest.txt -f format.txt -t, -T -Smydbserv
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total    44593

C:\>

but it works fine if i use the old DTS using the {LF} for the row terminator (comma deli)

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-13 : 19:57:15

OH OH!!! got a little out of it, i change the \r\n in the format file i generated from the bcp command to \n (since the data i'm using is just LF)

go this:

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

BCP copy in failed

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-13 : 20:02:35

BINGO! had an extra field i needed to drop that i add after the data is imported...

can you do any kind of transitions with bcp? for example, as of now the data is areacode,phone but i usually alter the table, and add another field tha tis all 10 digits... any recommendations?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources