How to use Bulk insert with file format "" and with out in cvs file

Question

Post reply

How to use Bulk insert with file format "" and with out in cvs file

william_do2000

SSC Enthusiast

Points: 102
More actions
January 19, 2009 at 3:39 pm

#383755

Hello all
I have cvs file format like this "0160","Abbeville","003",5,2
How to use Bulk insert with file format "" and with out in cvs file into sql table?
I use query below, I got error = Got error
Bulk load data conversion error (truncation) for row 2, column 4 (Quantity).
Msg 4863, Level 16, State 1, Line 17
BULK INSERT dbo.Test_new
FROM '\\test\Counts.csv'
WITH
(
DATAFILETYPE = 'CHAR',
FIRSTROW = 2,
FIELDTERMINATOR = '","',
ROWTERMINATOR = ''
)
Go
"0160","Abbeville","003",5
Got error
Bulk load data conversion error (truncation) for row 2, column 5 (Quantity).
Msg 4863, Level 16, State 1, Line 17

Viewing 5 posts - 1 through 5 (of 5 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

If your files are not very big it's easier to set up linked server connection to text files using ODBC driver.

That driver interpretes quoted values correctly.

_____________
Code for TallyGenerator

william_do2000 SSC Enthusiast Points: 102 More actions · Answer 2

Hi Sergiy,

My file is big. Do you have good way to do bulk insert? Thank you for help

LD

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Yes... create a format file. \" is the escape character for a single quote in the delimiter column. First character in the datafile must have a length of 1 and not be imported if it is a quote.

Also, what are you calling a "big file"?

--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)

Sergiy SSC Guru Points: 110209 More actions · Answer 4

We discussed this matter on this forum several times.

Here are couple of links for you:

http://www.sqlservercentral.com/Forums/Topic18289-8-2.aspx

http://www.sqlservercentral.com/Forums/Topic296166-8-2.aspx

_____________
Code for TallyGenerator