Bulk Insert - 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 Insert

Author

Topic

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 09:18:23

Hi Everyone,

Please don't laugh at me but when I say I'm new to SQL I mean it. At work we started working with databases and so I've decided to try and up-skill myself by self-teaching SQL. I soon realised I'm going to need some help.

My question is this. I have created the following code to create a table called Colours and to import data from a csv file using BULK INSERT.

CREATE TABLE Colours
(Colour VARCHAR(10),
Shape VARCHAR (10));

BULK INSERT Colours
FROM 'c:\users\matt\documents\colours.csv'
WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

The input csv file looks like this:

Red,Square
Red,Circle
Blue,Square
Green,Triangle

This code works and create the results below:

Colour Shape
"Red Square"
"Red Circle"
"Blue Square"
"Green Triangle"

Question 1: How do I get rid of the “ marks in the data table as my code is putting it in all rows?

Question 2: I’m now trying to import a tab delimited file as below but I don’t know how to adjust my code in order to import it into my table?

Red Square
Red Circle
Blue Square
Green Triangle

When I try it with a text file (non-comma delimited) I get the error below:

Msg 4832, Level 16, State 1, Line 6
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 6
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 6
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any help welcome. Thanks I advance and apologies again if this is a bit "basic" for this forum.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 09:38:49

Does your csv file have those double-quotes? If not, I don't have an explanation of how they showed up in the data table. In any case, you can remove them using the following query:

Update Colors set Colour=replace(Colour,'"',''), Shape = replace(Shape,'"','')

;

For importing tab delimited files, specify field terminator as the tab like this:

...
WITH (FIELDTERMINATOR = CHAR(9),
...

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 09:49:26

Thanks. No it doesn't. I tried it with another table of six columns and the " only appear at the start of the first column and the end of the last.

I'll give your suggestions a try and see how I get on, but any other advice welcome too. Really appreciate the quick response.

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 09:53:59

Hi I changed that to CHAR(9) - what does this actually mean? The code now looks like below, but I'm still getting an error -

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'CHAR'.

CREATE TABLE Colours2
(Colour VARCHAR(10),
Shape VARCHAR (10));

BULK INSERT Colours2
FROM 'c:\users\matt\documents\colours.txt'
WITH (FIELDTERMINATOR = CHAR(9),
ROWTERMINATOR = '\n')

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 10:05:09

replace Char(9) with '\t'.......e.g.

FIELDTERMINATOR = '\t',

Cheers
MIK

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 10:13:55

Amazing - thanks MIK 2008!

mathomas73
Starting Member

23 Posts

Posted - 2013-06-05 : 10:26:12

I have one last question now then. I've not added a first column of Id which is an interger, but in its current form it needs an ID number in the first column of my txt file. Is there a way to bring in my tab delimited file but to give it an autonumber in the first column, and how would I do it?

Thank you

CREATE TABLE Colours4
(Id INT,
Colour VARCHAR(10),
Shape VARCHAR (10));

BULK INSERT Colours4
FROM 'c:\users\matt\documents\colours4.txt'
WITH (FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n')

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-05 : 10:48:23

there might be other ways too, here are the two
1) add the auto number data in the file for each row, and thus it will be read and inserted via the bulk insert commmand it self. In case if this is not possible then
2) create a new table as "create table #tmp (ID int identity, colour varchar(10),shape varchar(10))" and insert the loaded data from colours4 into this new table .. e.g. INSERT Into #tmp (colour,shape) select colour,shape from colours4;

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources