0

I have not used SQL Server much (I usually use PostgreSQL) and I find hard to believe / accept that one simply cannot insert NULL values from a text file using BULK INSERT, if the file has a value that indicates null or missing data (NULL, NA, na, null, -, ., etc.).

I know BULK INSERT can keep NULL if the field is empty (link, and this is not a nice solution for my case because I have > 50 files, all of them relatively big > 25GB, so I do not want to). But I cannot find a way to tell SQL Server / BULK INSERT that certain value should be interpreted as NULL.

This is, I would say, pretty standard in importing data from text files in most tools. (e.g. COPY table_name FROM 'file_path' WITH (DELIMITER '\t', NULL 'NULL') in PostgreSQL, or readr::read_delim(file = "file", delim = "\t", na = "NULL") in R and the readr package, just to name a couple of examples).

Even more annoying is the fact that the file I want to import was actually exported from SQL Server. It seems that by default, instead of leaving NULL as empty fields in the text files, it writes the value NULL (which makes the file bigger, but anyway). So it seems very odd that the "import" feature (BULK INSERT or the bcp utility) of one tool (SQL Server) cannot properly import the files exported by default by the very same tool.

I've been googling around (link1, link2, link3, link4) and cannot find a workaround for this (different than editing my files to change NULL for empty fields, or import everything as varchar and later work in database to change types and so on). So I would really appreciate any ideas.

For the sake of a reproducible example, here is a sample table where I want to import this sample data stored in a text file:

Sample table:

CREATE TABLE test 
(
    [item][varchar](255) NULL,
    [price][int] NULL
)

Sample data stored in file.txt:

item1, 34 
item2, NULL 
item3, 55

Importing the data ...

BULK INSERT test
FROM 'file.txt'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

But this fails because on the second line it finds NULL for an integer field. This field, however, allows NULL values. So I want it to understand that this is just a NULL value and not a character value.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
elikesprogramming
  • 2,506
  • 2
  • 19
  • 37
  • I found an old SO for a similar issue: https://stackoverflow.com/questions/17351099/sql-server-import-wizard-treats-null-as-literal-string-null --- it looks as though on import sql server treats the value as a literal string 'NULL'. The answer says to either scrub the file, or use an intermediate table then copy with a script that converts the literal string to the value NULL. Outside of this for the import, I would investigate further the export function and determine if there is a setting to export NOTHING for NULL instead of the literal string 'NULL'. – user7396598 Mar 12 '18 at 18:37
  • On bulk import empty fields are NULL, or column default if it has one. – user7396598 Mar 12 '18 at 18:38

0 Answers0