SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 bcp import
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 10/13/2008 :  18:27:36  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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 - 10/13/2008 :  19:08:36  Show Profile  Reply with Quote
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



Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 10/13/2008 :  19:24:11  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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!

Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 10/13/2008 :  19:38:19  Show Profile  Reply with Quote
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
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 10/13/2008 :  19:52:47  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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)
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 10/13/2008 :  19:57:15  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 10/13/2008 :  20:02:35  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000