SQL won't insert null values with BULK INSERT

Question

I have a CSV file and each line looks similar to this:

EASTTEXAS,NULL,BELLVILLE AREA,NULL,BELLVILLE AREA,RGP,NULL,NULL,0,NULL,NULL,NULL,1,1,PM,PM Settings,NULL,NULL

I couldn't find any examples on how NULL values were supposed to be handled when doing BULK INSERT, so I assumed that was OK.

When I try to run the BULK INSERT, it gives me this error:

Msg 4864, Level 16, State 1, Line 28 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 12 (ClassificationPK).

Here's my table and what not:

CREATE TABLE #Assets
(
ParentID VARCHAR(255) NULL,
ClassificationID VARCHAR(255) NULL,
AssetID VARCHAR(255) NULL,
AssetType VARCHAR(255) NULL,
AssetName VARCHAR(255) NULL,
RepairCenterID VARCHAR(255) NULL,
LaborID VARCHAR(255) NULL,
Owner VARCHAR(255) NULL,
IsLocation VARCHAR(255) NULL,
AssetTypeDesc VARCHAR(255) NULL,
ClassificationName VARCHAR(255) NULL,
ClassificationPK INT NULL,
IsUp BIT NULL,
RequesterCanView BIT NULL,
PMCycleStartBy VARCHAR(255) NULL,
PMCycleStartByDesc VARCHAR(255) NULL,
PMCycleStartDate DATETIME NULL,
PMCounter INT NULL,
ParentPK INT NULL,
ParentName VARCHAR(255) NULL,
AssetPK INT NULL,
RepairCenterPK INT NULL,
RepairCenterName VARCHAR(255) NULL,
LaborPK INT NULL)

BULK
INSERT #Assets
FROM '\\cdmsqlint01\drop\assets.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
GO

SELECT * FROM #Assets

DROP TABLE #Assets

Any ideas on what I'm doing wrong here?

ClassificationPK isn't defined as a Primary Key is it... (not sure how it could be and be null but... *shrug* — xQbert, Jul 24 '14 at 19:42
@xQbert It's not. the table definition is in the code block. — ernest, Jul 24 '14 at 19:47
@GaryMcGill UTF, I think? Not sure about the code page. I'll have to look into that. — ernest, Jul 24 '14 at 19:47
If it's utf, there's no code page :-) however if it's utf (Unicode) then you'd be better using nvarchar rather than varchar, precisely because Unicode cannot be converted into ASCII without problems. — Gary McGill, Jul 24 '14 at 20:16

Kritner · Accepted Answer · 2016-01-07T15:47:04.757

18

According to:

http://msdn.microsoft.com/en-us/library/ms187887.aspx

null values can be inserted by having an empty field within your file.

Example file was:

1,,DataField3
2,,DataField3

Example method of importing file keeping nulls is:

USE AdventureWorks;
GO
BULK INSERT MyTestDefaultCol2
FROM 'C:\MyTestEmptyField2-c.Dat'
WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    KEEPNULLS
);
GO

Granted, this means you'll have to change your "NULL"s to "", and any empty strings you did want as empty string would be interpreted as nulls, but it might be enough to get you started? I would imagine to keep your empty string columns they would need to be changed from

field1,,field2

to

field1,"",field2

as example

edited Jan 07 '16 at 15:47

answered Jul 24 '14 at 19:53

Kritner

13,557
10
46
72

Thanks. I must've missed that part when I was looking at it earlier. It's strange, though. It sort of works. but now the last column is showing the same error. Even when I add data to that column, it says conversion error. I'm looking more into that. Thanks for the info. – ernest Jul 24 '14 at 20:09
@ernest could you have differing line breaks or potentially extra characters at the end of your lines? – Kritner Jul 25 '14 at 11:52
I think it was an issue with the fields. I had a certain number of fields in the table, but not all of the data in the CSV. I had assumed that the other fields would default to null. But when it threw the error, I added nulls to the CSV to make up for those fields and it worked. Thanks! – ernest Jul 25 '14 at 14:27

Eske Rahn · Answer 2 · 2017-06-22T13:46:54.300

A late PS.

Note that there seems to be a bug if the last column is a DATETIME type and the value is null, then you MUST have a (normally superfluous but accepted) field separator after the empty field, before the line-separator, or you'll get an error.

ADD: This does not consistently work (sometimes it does though!?!?), to make it stable change your ROWterminator to begin with the FIELDterminator

e.g. FIELDTERMINATOR = '#', ROWTERMINATOR = '#\n'

Below an example of the error you get when it messes things up:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 31 (MyDateTimeField).

(And yes I tried an all ASCII example with less than 7F chars and got the same)

Example:

The first bulks fails a lot. The other fails on the fifth only, despite the similarity with the second, but nothing is allowed at the end in this special case...

Test data:

1#2017-06-13 19:00:11.247#0##2017-06-13 19:00:11.247#
2#2017-06-13 19:09:08.817#0##2017-06-13 19:00:11.247#123#
3#2017-06-13 18:44:07.980#1###
4#2017-06-13 09:39:18.367#11## #
5#2017-06-13 09:39:18.370#4###123#

and sql

CREATE TABLE TEST_Bulk(
    id int,
    ts1 datetime,
    txt varchar(40) NULL,
    ts2 datetime,
    ts3 datetime,
) 
GO

BULK INSERT TEST_Bulk FROM 'D:\File.txt' WITH (FIRSTROW=1, FIELDTERMINATOR = '#', ROWTERMINATOR = '\n', CODEPAGE= 'ACP' ,TABLOCK)

BULK INSERT TEST_Bulk FROM 'D:\File.txt' WITH (FIRSTROW=1, FIELDTERMINATOR = '#', ROWTERMINATOR = '#\n', CODEPAGE= 'ACP' ,TABLOCK)

select * from TEST_Bulk

drop table TEST_Bulk

SQL won't insert null values with BULK INSERT

2 Answers2

Linked