SQL Server Bulk Insert from Fixed Width File- How do I get NULLs for strings?

Question

I'm doing a bulk insert from a fixed width text file using

INSERT INTO blah
SELECT blah1, blah2 
  FROM OPENROWSET(BULK 'filename.txt', FORMATFILE='format.xml');

It's working fine, except that I want NULLs for the empty (all spaces) fields in the file. This is no problem for fields marked in the format file as SQLINT, SQLDATETIME, etc., but SQLNVARCHAR types just come in as empty strings.

I suppose that does make sense, but how would I get it to import NULLs instead?

Answer 1

I always import to a staging table and do the necessary clean up and then insert to production tables.

Answer 2

Use a CASE statement

SELECT CASE
           WHEN RTRIM(blah2) = '' THEN NULL
                                  ELSE blah2
       END