BCP Import Format Error

[asotmhg]

I am getting an error message when I run the following command:

bcp trucks.dbo.ImportFile in roadnetimport.txt -f trucksformat.dat -T

Database: trucks
Owner: dbo
Table: ImportFile
Data File: roadnetimport.txt
Format File: trucksformat.dat

The Format File ooks like this:

9.0
14
1 SQLCHAR 0 8 "" 1 DeliveryDate ""
2 SQLCHAR 0 10 "" 2 RouteID ""
3 SQLCHAR 0 2 "" 3 StopNumber ""
4 SQLCHAR 0 10 "" 4 PreviousLocationID ""
5 SQLCHAR 0 10 "" 5 CurrentLocationID ""
6 SQLCHAR 0 5 "" 6 TravelTime ""
7 SQLCHAR 0 6 "" 7 LegMiles ""
8 SQLCHAR 0 5 "" 8 ExpectedStopTime ""
9 SQLCHAR 0 5 "" 9 ExpectedTimeofArrival ""
10 SQLCHAR 0 1 "" 10 Blank1 ""
11 SQLCHAR 0 5 "" 11 DeliveryUnits ""
12 SQLCHAR 0 5 "" 12 DeliveryPounds ""
13 SQLCHAR 0 5 "" 13 Blank2 ""
14 SQLCHAR 0 1 "\n" 14 Blank3 ""

The Data file looks like this:

08/02/15500 1 1 0258 00:050000.000:0011:05 21 5 0
08/02/15500 2 0258 1258 02:380127.800:0013:43 285 274 3
08/02/15500 3 1258 2258 00:050000.000:0013:48 178 158 2
08/02/15500 4 2258 3258 00:050000.000:0013:53 20 31 0
08/02/15500 5 3258 4258 00:050000.000:0013:58 7 13 0
08/02/15500 6 4258 5258 00:050000.000:0014:03 0 1 0
08/02/15500 7 5258 6258 00:050000.000:0014:08 26 48 0
08/02/15500 8 6258 8258 00:050000.000:0014:13 59 36 0
08/02/15500 9 8258 9258 00:050000.000:0014:18 75 50 1
08/02/15500 109258 0336 00:580037.000:0015:16 4 1 0
08/02/15500 110336 1336 00:050000.000:0515:21 208 228 2

The error says: Error while reading BCP format.

How should the format file be setup? I used tabs in the format file to seperate information in the format file. Can you see anything wrong with it. I am a total newbie to using the bcp and format file. Everything is lined up in the data and format files eventhough they don't look it when I copied the contents to this post.

 

[gmmastros]

What version of SQL Server are you using?

If you are using SQL2000, then you need to use a version 8.0 format file. When doing so, you need to specify the collation for each varchar column (if there are any).

Also, I would remove the \n from the last column and use the -r switch on the bcp command instead. I don't know if this will make a difference, but it's something worth trying.

Lastly, I've seen format files complain if there is a blank line at the end of the format file. I recommend you double check this.

I should mention that I was able to import your data, using your format file in to SQL2005 without any changes.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom