question

Joji John avatar image
Joji John asked

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!!! :-(
bcpbulk-insert
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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...
0 Likes 0 ·
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...
0 Likes 0 ·
I would suggest either sticking to bcp or moving to SSIS then.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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][1] Have you considered using [OPENROWSET(BULK..)][2] (at the bottom of that page)? [1]: http://msdn.microsoft.com/en-us/library/ms191175.aspx [2]: http://msdn.microsoft.com/en-us/library/ms178129.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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...
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack, it will not be possible to import whole file into a working table as it is SQL2005 and the file as 1211 columns.
2 Likes 2 ·
Sorry I've deleted by mistake a WilliamD comment instead of mine. :-(
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.