SQL Server Import Wizard treats NULL as literal string 'NULL'

Question

When I attempt to import a .csv comma-delimited flat file into a Microsoft SQL server 2008R2 64-bit instance, for string columns a NULL in the original data becomes a literal string "NULL" and in a numeric column I receive an import error. Can anyone please help???

Answer 1

Put the data into a staging table and then insert to the production table using SQL code.

update table1
set field1 = NULL
where field1 = 'null'

Or if you want to do a lot of fields

update table1
    set field1 = case when field1 = 'null' then Null else Field1 End
      , field2 = case when field2 = 'null' then Null else Field2 End
      , field3 = case when field3 = 'null' then Null else Field3 End