CODEWITHSUNDEEP
sqlsql-servercsvbulkinsert
slevin37
slevin37

Reputation: 33

Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

Im having an issue using a bulk insert command for bringing a .csv into a database. Here is a sample of the CSV I created

Here is the query i wrote:

BULK INSERT TBL_Staging FROM 'C:\Users\testdata.csv' With (FieldTerminator = ',', RowTerminator= '\n', KEEPNULLS);

This is getting me the following error message(s):

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

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

Upvotes: 0

Views: 33493

Answers (2)

Sev Roberts
Sev Roberts

Reputation: 1295

The first row of your CSV data contains column headers, and the error message is because SQL Server is trying to insert your header row as data. Either remove the column header row or specify FIRSTROW=2 

BULK INSERT TBL_Staging FROM 'C:\Users\testdata.csv' With (FIRSTROW=2, FieldTerminator = ',', RowTerminator= '\n', KEEPNULLS);

For the benefit of others who might read this without seeing the asker's data - the FIRSTROW=2 parameter workaround only works if your header row really does have the same number of columns and delimiters as your data - which in this case it did - but otherwise would error.

Edit: I notice that your data also has some values using a double-quote as a text qualifier around values containing a comma, so you are likely to see an error even after removing/skipping the header row. SQL Server BULK INSERT is notorious for not supporting CSV text qualifiers, leading to several different workarounds over the years, including but not limited to:

  • Using a field delimiter other than the comma, one which doesn't occur in your data, such as |
  • Specifying a 'format file' for the bulk insert

For more see these existing posts:

Bulk Insert Correctly Quoted CSV File in SQL Server

SQL Bulk import from CSV

Upvotes: 8

Dese
Dese

Reputation: 348

Have you tried to remove the header from your file ? The way I see it, it treats the header line as a data line and returns an error because it is not in the right format. If the header is the problem, then preprocess your files to remove it, because:

"Skipping headers is not supported by the BULK INSERT statement"

source https://msdn.microsoft.com/en-us/library/ms188365.aspx

Upvotes: 1

Related Questions