SQL Server BULK INSERT error

Question

This is my source data in CSV format:

4,23,2AY5623,7235623
4,23,2GP1207,1451207
4,23,2GQ6689,4186689

Table:

CREATE TABLE [dbo].[Table1](
    [idCodeLevel] [int] NOT NULL,
    [idFirm] [int] NOT NULL,
    [valCodeFrom] [varchar](15) NOT NULL,
    [valCodeTo] [varchar](15) NOT NULL
) ON [PRIMARY]

This the code I am using to bulk import:

USE Test
GO

TRUNCATE TABLE Table1
GO

BULK INSERT Table1
  FROM 'C:\Temp\test.csv'
  WITH (
    FIELDTERMINATOR = ',',
    MAXERRORS=0,
    ROWTERMINATOR = '\n'
  )
GO

Error I am getting is:

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

Can you please someone tell me why is it failing?

I googled and found out that I might have to use the format .fmt. But how can I convert a csv file to fmt. I have seen code to create fmt file from sql table.

Thanks a lot for your help!

What happens if you take out the 'ROWTERMINATOR = '\n'' line? — PowerUser
– PowerUser, Commented Feb 26, 2013 at 17:20
Hmm.... what version of SQL Server are you using? I cannot reproduce this - works just like a charm - on SQL Server 2008 R2 — marc_s
– marc_s, Commented Feb 26, 2013 at 17:24

Russell Fox · Accepted Answer · 2013-02-26 17:39:28Z

1

Does the csv have a row at the top of field names? If so you'll need to add "FIRSTROW = 2" to your bulk statement. If not, try creating a new table that is all VARCHAR fields, then check the data: you probably have something strange in your data that you aren't expecting, like a non-printing character. Import as text and then try something like "SELECT ISNUMERIC([FIELD1]) FROM NEWTABLE".

answered Feb 26, 2013 at 17:39

Russell Fox

5,4651 gold badge26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1666952 Over a year ago

I did not have any thing other than the data in the first row (as mentioned above). But adding "FIRSTROW = 2" worked. Really really weird. But It loaded everything except the first row. So, i made a fake first row and ran the bulk insert and it loaded every thing. THANKS SO MUCH> killed an hour atleast on this.

RBarryYoung Over a year ago

@user1666952 I suspect that you've got a UNICODE Byte Order Mark, or some other invalid character at the beginning of your text file that just happens to be invisible to Notepad. Such a character would of course be invalid for an integer field, so you would get a conversion error.

Russell Fox Over a year ago

Weird - there must be a hidden character in there somewhere. I'd just make sure that first row of actual data is, in fact, in your imported data!

Abhijit · Accepted Answer · 2013-02-26 18:10:37Z

0

use the sql import wizard to import data from external file.

Right click on database--->task--->import----> specify the flat file as source and select the destination server.

for more information please visit Import CSV data to SQL

answered Feb 26, 2013 at 18:10

Abhijit

6832 gold badges18 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL Server BULK INSERT error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related