April 7, 2005 at 8:39 am
I am trying to get bcp working to get a comma separated file into a simple table. When I run the command I keep getting an error saying: Unexpected EOF encountered in BCP data-file.
I have done a lot of research and I have tried it with the -t and -r switches and I can't seem to find the right combination. I was wondering what the row terminator was so I opened the data file in MS Word with all characters showing to see what it was. When I look at it in Word, the row terminator is the paragraph mark. (I don't know if this will be seen as a newline character or carriage return so I've tried both.) Below are examples of my data file and the output table.
Data File:
123456 ,03/28/05 ,08:20 ,03/28/05 ,09:00 ,0.27 ,0.22 ,14.650
123456 ,03/28/05 ,09:00 ,03/29/05 ,09:00 ,24.00 ,27.79 ,14.650
123456 ,03/29/05 ,09:00 ,03/30/05 ,09:00 ,24.00 ,27.40 ,14.650
123456 ,03/30/05 ,09:00 ,03/31/05 ,09:00 ,24.00 ,27.27 ,14.650
123456 ,03/31/05 ,09:00 ,04/01/05 ,09:00 ,24.00 ,26.90 ,14.650
123456 ,,,,,,,14.650
,,,,,,,
The layout of the output table is:
CREATE TABLE dbo.MyTable(
field1 varchar(20),
field2 varchar(10),
field3 varchar(10),
field4 varchar(10),
field5 varchar(10),
field6 varchar(10),
field7 varchar(20),
field8 varchar(10)
)
Here is the bcp command I am using:
bcp "myDB.dbo.MyTable" in "D:\Temp\AWGDWNLD.csv" -F3 -L12 -c -t"," -S"MyServer" -T
*** Forgot to put this in the first time ***
Here is the format file that was created when I ran bcp to create a format file:
8.0
8
1 SQLCHAR 0 20 "," 1 field1 SQL_Latin1_General_CP850_CI_AS
2 SQLCHAR 0 10 "," 2 field2 SQL_Latin1_General_CP850_CI_AS
3 SQLCHAR 0 10 "," 3 field3 SQL_Latin1_General_CP850_CI_AS
4 SQLCHAR 0 10 "," 4 field4 SQL_Latin1_General_CP850_CI_AS
5 SQLCHAR 0 10 "," 5 field5 SQL_Latin1_General_CP850_CI_AS
6 SQLCHAR 0 10 "," 6 field6 SQL_Latin1_General_CP850_CI_AS
7 SQLCHAR 0 20 "," 7 field7 SQL_Latin1_General_CP850_CI_AS
8 SQLCHAR 0 10 "\r\n" 8 field8 SQL_Latin1_General_CP850_CI_AS
I have also tried using \r\n as the row terminator and I still get the problem.
Can anyone see what I am doing wrong and tell me what I need to do to make it right.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 7, 2005 at 2:03 pm
I finally got it figured out. There was some malformed data hidden deep inside the data file.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 8, 2005 at 10:08 am
Just for future reference on bcp errors ... do not forget the "-e errfile" command line parameter. If your bcp uis good, the file size will always be "0 bytes". If there is some type of error it will put the offending source data line(s) in the file and even mark them. The marking is very crude using @'s for the line and ordinal column along with a very, very brief messagfe.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply