0

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

Community
  • 1
  • 1
42cornflakes
  • 193
  • 4
  • 15
  • 2
    I think the csv is not in the expected format. For keeping null the records should be 1, 11114,, in each row. Other option is to remove the last two columns in header. – Kiran Hegde Jul 09 '14 at 15:27
  • I am omitting the header with FIRSTROW = 2. But the ','s did the trick. I feel silly now. Thanks – 42cornflakes Jul 09 '14 at 15:30
  • @KiranHegde You should post it as an answer since it solved the OP's question. – Jorge Campos Jul 09 '14 at 16:04

1 Answers1

1

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.

Kiran Hegde
  • 3,651
  • 1
  • 16
  • 14