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

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 SQ开发者_StackOverflow中文版LNVARCHAR types just come in as empty strings.

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

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

Use a CASE statement

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