Search code examples
sqlsql-serverbulkinsertbulk-loadansi-sql

Bulk insert SQL command cannot insert first row

I'm using a bulk insert command for SQL Server but for some reason the first row isn't being inserted. Why can't I insert data from the first row? Is bulk insert expecting headers as default and how can i circumvent this? If I add a dummy row and set WITH to FIRSTROW = 2 then the first row is inserted without a problem but I don't think this is a nice solution.

Error code:

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

Command:

BULK INSERT TableData
FROM 'C:\Users\Oscar\file.csv'
WITH (FIELDTERMINATOR = ';',
      ROWTERMINATOR = '\n',
      KEEPNULLS,
      KEEPIDENTITY)

Sample data:

1;Text 1;1;0;;
2;Text 2;1;0;;
3;Text 3;1;0;;
4;Text 4;1;0;;
5;Text 5;1;0;;
Solution

  • The script is probably utf-8 and you're trying to load it from cmd with cp-1252 or something, the UTF-Bom at the beginning freaks out the interpreter. Look with a hexeditor and you'll see it.

    Save as ANSI and try again.