Skipping rows in the datafile that contains NULL for a column that does not allow NULLs

I recently ran into a customer who wanted to import data into a table that did not allow NULL for one column. However, his data file had some rows that had NULL for that column. He tried the following Bulk Insert command

bulk insert customer.dbo.test from 'c:data.txt' with (maxerrors = 20) but this command failed with the following error

Msg 4869, Level 16, State 1, Line 1
The bulk load failed. Unexpected NULL value in data file row 2, column 1. The destination column (c1) is defined as NOT NULL

Maxerror does not help in this case because the NULL enforcement is done at much later stage and this constraint cannot be disabled. Interestingly, OPENROWSET can easily handle this as follows

insert into customer.dbo.test
select * from OPENROWSET (BULK 'c:data.txt',FORMATFILE='c:format.xml') as t1
where c1 is not NULL.

Have fun with OPENROWSET.

Comments

• Anonymous
  September 29, 2006
  But why do we have to use an formatfile that has to reside on the DISK?, why couldn't we provide the formatfile content in an varchar field or something so we didn't need to depend on disk availability???
  
• Anonymous
  September 29, 2006
  Thanks for your comments. Can you please describe your scenario? If you want to use the statement with different foramts, you may want to consider sp_executesql?
• Anonymous
  September 29, 2006
  My point is that I need to import some CSV files, sometimes it happens that the csv file are exported with different column order than the table I want to file inside, and using format file you can change to order. (I have some other scenarios also, like some tools that export using " around fields, and comma as separator, others only using semikolon as separator), and I cannot use formatfile since I have to store the formatfile on disc.
  So I need to import in one table, cleanup the data and then move to other table.
  I see that using openrowset I can get away with some of the problems, but the posibility to use the formatfile would make lot ot things easier, but as I said, I cannot store the file anywere..
  And since you now can use an formatfile with XML, it would be very nice to only put the xml into an varchar variable and send that into the formatfile attribute for openrowset... (Or BULK INSERT method)
• Anonymous
  September 29, 2006
  Ok. At this time, we only allow providing formatting information in a file but not as a program variable. However, I have added this suggestion in our database.
  
  Thanks
  Sunil