5

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???

vikrant rana
  • 4,509
  • 6
  • 32
  • 72
Erik Johnson
  • 858
  • 1
  • 7
  • 29

3 Answers3

10

KISS

Pre-process it, Replace all "NULL" with "".

ie the .csv file will have

,,

Instead of 

NULL,NULL,

Seems to do the job for me.

Max Robbertze
  • 406
  • 4
  • 11
3

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
Air
  • 8,274
  • 2
  • 53
  • 88
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 1
    This is a horrible, horrible solution. "NULL" is a perfectly valid string and not the same as null, so don't treat it as such. Things like this are exactly what lead to troubles for all the poor people with a "Null" lastname. – Voo Aug 22 '16 at 14:22
  • 2
    @Voo, Did you read the question? Null was not a valid value for the OP. – HLGEM Aug 22 '16 at 14:25
  • 2
    @HLGEM The OP is also talking about string columns and nowhere says anything whether "NULL" is valid or not - just that "NULL" will throw an error in integer columns. – Voo Aug 22 '16 at 15:11
  • Ha, what a necrothread. I popped in to check on my old question and saw this. @Voo, while you are correct that this would have caused issues if NULL was valid input, in my case it was not and discarding these values was correct. – Erik Johnson Nov 03 '20 at 00:59
0

Adding to HLGEM's answer, I do it dynamically, I load into staging table here all column types are VARCHAR and then do:

DECLARE @sql VARCHAR(MAX) = '';
SELECT @sql = CONCAT(@sql, '
    UPDATE [staging].[',[TABLE_NAME],']
    SET [',[COLUMN_NAME],'] = NULL
    WHERE [',[COLUMN_NAME],'] = ''NULL'';
    ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_SCHEMA] = 'staging' 
    AND [TABLE_NAME] IN ('MyTableName');
SELECT @sql;
EXEC(@sql);

Then do:

INSERT INTO [dbo].[MyTableName] ([col1], [col2], [colN])
SELECT [col1], [col2], [colN]
FROM [staging].[MyTableName]

Where table [dbo].[MyTableName] is defined with the desired column types, this also fails and tells you in type conversion errors...

Morad
  • 734
  • 5
  • 14