Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

csv file data into a table Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 100, Visits: 335
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,
Post #1513566
Posted Tuesday, November 12, 2013 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 36,778, Visits: 31,234
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1513571
Posted Tuesday, November 12, 2013 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 2:28 PM
Points: 11, Visits: 41
Just to expand a little on Jeff's answer. You can use as the row terminator (new line character).

Post #1513582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse