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:
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!!! :-(
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...
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:
Have you considered using [OPENROWSET(BULK..)] (at the bottom of that page)?: h ttp://msdn.microsoft.com/en-us/library/ms178129.aspx
answered Dec 07 '10 at 01:02 AM