x

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 '10 at 10:15 PM in Default

Joji John gravatar 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 '10 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 '10 at 06:45 PM Joji John
I would suggest either sticking to bcp or moving to SSIS then.
Dec 08 '10 at 12:04 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

3 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 '10 at 01:04 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Sorry I've deleted by mistake a WilliamD comment instead of mine. :-(
Dec 07 '10 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 '10 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..)][2] (at the bottom of that page)?

[2]: http://msdn.microsoft.com/en-us/library/ms178129.aspx
more ▼

answered Dec 07 '10 at 01:02 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x39
x31

asked: Dec 06 '10 at 10:15 PM

Seen: 3221 times

Last Updated: Dec 06 '10 at 11:54 PM