CODEWITHSUNDEEP
sqlsql-servert-sqlbulkinsert
42cornflakes
42cornflakes

Reputation: 303

Bulk Insert (TSQL) from csv file with missing values

Problem:

I have a table

CREATE TABLE BestTableEver 
    (
    Id INT,
    knownValue INT,
    unknownValue INT DEFAULT 0,
    totalValue INT DEFAULT 0);

And I have this CSV File (Loki.csv)

Id, knownValue, unknownValue, totalValue 
1, 11114
2, 11135
3, 11235

I want to do a bulk insert into the table and since I do not know the values of unknownValue and totalValue yet , I want them to be take up the default value (as defined in the table creation)

My approach so far

create procedure populateLikeABoss
@i_filepath NVARCHAR(2048)
DECLARE @thor nvarchar(MAX)
    SET @thor=
        'BULK INSERT populateLikeABoss
        FROM ' + char(39) + @i_filepath + char(39) +
        'WITH
        (
            FIELDTERMINATOR = '','',
            ROWTERMINATOR = ''\n'',
            FIRSTROW = 2,
            KEEPNULLS
        )'
    exec(@thor)
END

and calling the procedure to do the magic

populateLikeABoss 'C:\Loki.csv'

Error

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

References

Keeping NULL value with bulk insert

Microsoft

Similar question without the answer I need

StackOverflow

Upvotes: 0

Views: 1392

Answers (1)

Kiran Hegde
Kiran Hegde

Reputation: 3681

I think the csv is not in the expected format. For keeping null the records should be in the format 1, 11114,, in each row. Other option is to remove the last two columns in header.

Upvotes: 1

Related Questions