Bulk Insert with no last column

Question

Post reply

Bulk Insert with no last column

CurtM

Mr or Mrs. 500

Points: 572
More actions
October 20, 2006 at 11:18 am

#170841

Hi all, I have an issue where we want to bulk insert a series of files. The fielse are from a 3rd party where fields are | separated and rows are newline separated.
We are attempting to use bulk insert, but are running into a problem where the last row, last column of data has no data and does not end in a newline. When this condition occurs, the row is ignored by bulk copy. Example below, the last line is not inserted.
Does anyone have a solution (other than to wrap it in a script to add a newline to the end of the file)?
3528602|1000|AA1549|
3529264|1000|RB0572|data might be here
3529264|1001|RB0573|

Thanks!
Curt

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

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Two options.

1.As the vendow to pull "Blank or Null data" when the columns does not have values that way ur file definition is same.

2.Load it into temp table that have one varchar column. Then parse it.

Thanks

Sreejith

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I don't see any issue here. BULK INSERT from files like you described work in my system for almost 2 years, no problems with missing data in last column.

You must just make sure that last column is nullable.

Problem may appear if your files look like this:

3528602|1000|AA1549

3529264|1000|RB0572|data might be here

3529264|1001|RB0573

_____________
Code for TallyGenerator

CurtM Mr or Mrs. 500 Points: 572 More actions · Answer 3

Hmm.. Do you have a newline at the end of the last line? Columns are all nullable. Our solution du jour is to tack a newline at the end which is working.