csv file data into a table

Question

csv file data into a table

RightOnTarget

Hall of Fame

Points: 3041
More actions
November 12, 2013 at 11:05 am

#301091

Hi all,
I am running SQL Server 2008R2 and trying to import .csv file data into a table.
The problem is that i have some values in the file that are missing (NULL).
Data:
1,
9999000002,
8888000004,
7777000019,0016000000KOCoG
7777000020,0016000000fGXCb
6666000021,
5555000022,
Table:
CREATE TABLE [dbo].[A](
[coid] [varchar](256) NULL,
[said] [varchar](15) NULL
);
Code:
bulk insert dbo.A
from 'C:\TEMP\temp.csv'
with (
fieldterminator = ','
,rowterminator = ''
);
Error:
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 (coid).
Question: is there way to filter NULL values? Perhaps there is another way, besides bulk insert to do it?
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

rightontarget (11/12/2013)
Hi all,
I am running SQL Server 2008R2 and trying to import .csv file data into a table.
The problem is that i have some values in the file that are missing (NULL).
Data:
1,
9999000002,
8888000004,
7777000019,0016000000KOCoG
7777000020,0016000000fGXCb
6666000021,
5555000022,
Table:
CREATE TABLE [dbo].[A](
[coid] [varchar](256) NULL,
[said] [varchar](15) NULL
);
Code:
bulk insert dbo.A
from 'C:\TEMP\temp.csv'
with (
fieldterminator = ','
,rowterminator = ''
);
Error:
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 (coid).
Question: is there way to filter NULL values? Perhaps there is another way, besides bulk insert to do it?
Thanks,

Stop using a blank as a row terminatior and this problem will go away.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CheckPoint Mr or Mrs. 500 Points: 562 More actions · Answer 2

Just to expand a little on Jeff's answer. You can use as the row terminator (new line character).