Specifying NULL fields for SQL Server Bulk Import

Question

With SQL Server 2012 I am importing text files into temporary tables using BULK INSERT:

BULK INSERT #temp
    FROM 'filepath\mydata.txt'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = '\t',
        ROWTERMINATOR = '\n',
        KEEPNULLS
    )

The #temp tables I create vary in data type and include float, nvarchar, and datetime.

My problem is that my data has several ways of naming NULL values including DNE, #N/A, NA etc. The presence of these values in non-character columns leads to this kind of error when I try to run the above code:

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

I am wondering whether there is a way to specify a set of values (DNE, #N/A, NA etc.) in the table to automatically be considered NULL upon import.

you have to clean up the fields prior to changing the datatype one way seems to be using a format file msdn.microsoft.com/en-us/library/ms188365.aspx. Then you can specify everythign as varchar/nvarchar import then create code to update those values to null then convert to the desired datatype. — Matt
– Matt, Commented Aug 24, 2016 at 16:48

Warner · Accepted Answer · 2016-08-24 19:30:42Z

Have you tried this option,

Option 1: You should clean up your data input using C# code or any Language code and make the data clean before Bulk Insert.

Option 2: You have the constraints in the Bulk Insert option, where the data can be checked using these constraints and modified as per the table requirements, then the modified data can be imported to Bulk Insert operation.

Example of Importing a Numeric Value that Uses Scientific Notation This example uses the following table:

CREATE TABLE t_float(c1 float, c2 decimal (5,4));

The user wants to bulk import data into the t_float table. The data file,

C:\t_float-c.dat, contains scientific notation float data. For example:

 8.0000000000000002E-28.0000000000000002E-2

However, BULK INSERT cannot import this data directly into t_float, because of its second column, c2, uses the decimal data type. Therefore, a format file is necessary. The format file must map the scientific notation float data to the decimal format of column c2. The following format file uses the SQLFLT8 data type to map the second data field to the second column: To use this format file (using the file name C:\t_floatformat-c-xml.xml) to import the test data into the test table, issue the following Transact-SQL

statement:

 BULK INSERT bulktest..t_float  
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');  
GO

Option 3: You can Make the values as nVarchar and import to the table, then again use Cursor traverse through the table and then convert the values [This is not optimized way, which may require some time however based on data in some scenario this can also help]

Collectives™ on Stack Overflow

Specifying NULL fields for SQL Server Bulk Import

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related