query to add data from a csv file to specific fields in an existing sql table

Question

I am trying to write query to add data from a csv file to specific fields in an existing sql table in mssms.

Errors

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 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 3, 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 4, column 1 (Id).

Msg 4865, Level 16, State 1, Line 1

Cannot bulk load because the maximum number of errors (2) was exceeded.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Written code:

INSERT INTO dbo.Item (Details, DatePost, Description, Amount, Type, Balance, CheckSlipNumber)
FROM 'C:\Users\User1\Desktop\Import\Chase2066_Activity_20230213.csv'
WITH 
(FIRSTROW = 2, 
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
)

Answer 1

Use BULK INSERT if you have the target table dbo.Item already:

BULK INSERT dbo.Item

FROM 'C:\Users\User1\Desktop\Import\Chase2066_Activity_20230213.csv'

WITH (

FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'

)

Answer 2

Check the format of the data, and check it with the datatypes of columns specified. It is the format of the data issue.

Answer 3

The error message suggests that there is a mismatch between the format and the actual file. Can you attach a sample file?

Also, I'm a little curious, against what you are running this? The statement obviously runs, but I don't recognize this as legal syntax in the "normal" SQL Server. What does "SELECT @@version" report?

Answer 4

Hi @Dean Everhart

You can use BULK INSERT to replace your code.

For more information about BULK INSERT, you can refer to this link.

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16

At the same time, for the error message you encounter, you can refer to these two cases.

https://stackoverflow.com/questions/33259735/bulk-load-data-conversion-error-type-mismatch-or-invalid-character-for-the-spec

https://stackoverflow.com/questions/13056929/bulk-load-data-conversion-error-type-mismatch-or-invalid-character-for-the-spec

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.