BULK INSERT with inconsitant number of columns

Question

I am trying to load a large amount data in SQL server from a flat file using BULK INSERT. However, my file has varying number of column, for instance the first row contains 14 and the second contains 4. That is OK, I just want to make a table with the max number of columns and load the file into it with nulls for the missing columns. I can play with it from that point. But it seems that SQL Server when reaching the end of the line and having more columns to fill for that same row in the destination table, just moves on to the next line and attempts to put the data on that line to the wrong column of the table.

Is there a way to get the behavior that I am looking for? Is there a table hint that I can use the specify this? Has anyone run into this before?

Here is the code

BULK INSERT #t
FROM '<path to file>'
WITH 
(
  DATAFILETYPE = 'char',
  KEEPNULLS,
  FIELDTERMINATOR = '#'
)

Answer 1

Try specifying a ROW terminator along with your field terminator.

BULK INSERT #t 
FROM '<path to file>' 
WITH  
( 
  DATAFILETYPE = 'char', 
  KEEPNULLS, 
  FIELDTERMINATOR = '#',
  ROWTERMINATOR = '\n' --Or whatever signifies the end of a row in your flatfile.
) 

More info on this can be found here:

http://msdn.microsoft.com/en-us/library/ms191485.aspx

Answer 2

The varying number of columns means it can't be parsed by the bulk insert code. How does it know the correct number of columns? What if you supply too many?

You'll have to upload it to a table with 4 columns, and split out the rest later (or one big column) Or pre-process it to generate an equal number of columns.

Answer 3

BULK INSERT isn't particularly flexible. One work-around is to load each row of data into an interim table that contains a single big varchar column. Once loaded, you then parse each row using your own routines.