Bulk Import error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Import error

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-02-26 : 10:55:32

Hi,

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.

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-02-26 : 11:41:49

Do the column datatypes in the target table match the values in the CSV files?

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-02-26 : 11:49:06

It is matching.

It is failing on the first column in table Table1 which is mentioned above.
Column datatype is int and the the value I am passing is 4.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-02-26 : 14:30:20

quote:
Originally posted by dolphin123

It is matching.

It is failing on the first column in table Table1 which is mentioned above.
Column datatype is int and the the value I am passing is 4.

I have tried that now and it worked for me (SQL 2012)

try re-creating the csv file?

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-02-26 : 16:32:12

quote:
Originally posted by dolphin123

It is matching.

It is failing on the first column in table Table1 which is mentioned above.
Column datatype is int and the the value I am passing is 4.

Try using FLOAT

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2014-06-25 : 11:50:21

Great. Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources