Error: Cannot bulk load. Invalid number of columns in the format file

Hi Team,

I am trying to do a Bulk Insert from a monster data file into a sql server 2005 Table with a format file. Here's the command I use:

bulk insert AdventureWorks..BulkTestTable from '\\\\john\\data\\report.dat' with (formatfile = '\\\\john\\formats\\report.fmt',TABLOCK,batchsize=10000)

The format file is of version 7.0 and it has 1211 columns in the host file. Of which I am trying to insert 882 columns into my table. The Data file i use is contiguous in nature and each column is defined with specific widths. I have checked and rechecked the format file and the data file for leading spaces and whether all the length of data and their positions match and all seem to be fine. Still I am getting this error.

Msg 4822, Level 16, State 1, Line 1 Cannot bulk load. Invalid number of columns in the format file "\\\\john\\formats\\report.fmt"

Interesting fact is that, when i try to do the same thing using BCP command line Utility , i am able to load the data file successfully into my table with the same format file.

PLEASE please HELP!!! :-(

more ▼

asked Dec 06, 2010 at 10:15 PM in Default

avatar image

Joji John
11 1 1 2

Thanks William,

But I tried out the openrowset(bulk..) functionality but I get the same error "Cannot Bulk Load..invalid number of columns...

Fatherjack >>> I have only 6 rows in the test file...and this is a single time task...there are so many columns that i am leaving out from datafile....where i am placing zero for columns to be ignored.... and i have checked this so many times...still the same error...

Dec 07, 2010 at 01:26 AM Joji John

Hi William, I tried bulk insert with an xml format file this time but the same error is appearing...i have no idea why this is happening...

Dec 07, 2010 at 06:45 PM Joji John

I would suggest either sticking to bcp or moving to SSIS then.

Dec 08, 2010 at 12:04 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Depending on how many rows there are in the file and whether this is a recurring task, I would just import the whole thing into a dev table and then ship the necessary across.

Why not use BCP to do this?

The error is pretty self-explanatory, the code is seeing a difference in the column count. Is it something simple like the delimiter you have chosen being part of a column name in the data?

If you import a sample set of data using BCP, can you create a fmt file from the table that would then let you import the whole set? You might see the difference in the FMT files then too...

more ▼

answered Dec 07, 2010 at 01:04 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

Sorry I've deleted by mistake a WilliamD comment instead of mine. :-(

Dec 07, 2010 at 10:36 PM Pavel Pawlowski

@Fatherjack, it will not be possible to import whole file into a working table as it is SQL2005 and the file as 1211 columns.

Dec 07, 2010 at 10:54 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Have you tried to use a different format file?

You can use the "old" style text file, which I assume you are using, or the "new" style which is an XML format file:

Using a Format File to Map Table Columns to Data-File Fields

Have you considered using OPENROWSET(BULK..) (at the bottom of that page)?

more ▼

answered Dec 07, 2010 at 01:02 AM

avatar image

26.2k 18 35 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 06, 2010 at 10:15 PM

Seen: 5734 times

Last Updated: Dec 06, 2010 at 11:54 PM

Copyright 2017 Redgate Software. Privacy Policy