Bulk Insert

Question

Post reply

Bulk Insert

Maverick100877

SSCertifiable

Points: 5240
More actions
June 16, 2006 at 5:45 am

#60971

Hi

How I do the BUlk Insert from an excel file..When doing from a text file I can very well give the fieldterminator and the rowterminator but when I have a excel file how do I go about it..

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

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

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 1

In excel tab is the delimiter.

------------
Prakash Sawant

http://psawant.blogspot.com

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 2

Well fine tab is delimeter but how do I specify it and also what about row terminator

BULK INSERT Test.dbo.AusDCVData

FROM 'C:\test.xls'

WITH

(

FirstRow=2,

FieldTerminator=' '--What do I give here for tab

, RowTerminator=''-- what to use as rowterminator

)

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 3

Try this

FIELDTERMINATOR = ''\t'',-- this is for tab

ROWTERMINATOR = ''\n'' -- this is for new line

------------
Prakash Sawant

http://psawant.blogspot.com

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 4

It is giving error....

Server: Msg 4866, Level 17, State 66, Line 1

Bulk Insert fails. Column is too long in the data file for row 1, column 15. Make sure the field terminator and row terminator are specified correctly.

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 5

Sounds like your problem is exactly what the error says. The record number 15 in your data file is too large for whatever your column definition is in your target table.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 6

I think the data in file for column 15 in first row is more then the column size in DB.

------------
Prakash Sawant

http://psawant.blogspot.com

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 7

Well it is a varchar(200) column and the maximum length to be inserted is only 18.So that cannot be the error.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 8

May be your data is having tab in between the row's.

Why don't you try dts?

------------
Prakash Sawant

http://psawant.blogspot.com

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 9

Well I can do that but I was trying to do it through bulk insert.

The problem is that this has to be done through a vb program where they will use this for importing the records to sql.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 10

Did you check your data?, dose it has any extra tab in between to columns?

------------
Prakash Sawant

http://psawant.blogspot.com