bulk insert with csv file terminated with ascii end of file character.
-
Hi all,
I have a requirement to import data from csv files that originate outside my control. These files are traditional header row csv files except the last row has a ASCII end of file character in it.
I cannot construct the T-SQL bulk insert statement to account for the last line problem. I have been deleting the last line by hand and then can process them but this is not the optimal solution.
Has anybody dealt with this before? Is there a way to ignore the error on the last line of the file?
Thanks in advance
bille
-
I just tell it to ignore at least 1 error...
--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) -
Could you post a quicky sample with the error ignore. I couldn't get it to work
-
Sure... here's a modified example from BOL...
BULK INSERT AdventureWorks.Sales.SalesOrderDetailFROM 'f:\orders\lineitem.tbl'WITH(FIELDTERMINATOR =',',ROWTERMINATOR = '',MAXERRORS = '1')--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) -
danke
-
Drat... I forgot you said you had a header row... you'll need to add a FIRSTROW = 2 to that...
--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) -
Hello,
I have a similar problem, and the MAXERRORS command did nothing for me. I still got the EOF error messages.
I am using SQL Server 2000 for my DB version.
Any suggestions will be great.
Thank you.
Jay.
-
The "Unexpected end of file errors" may be something that you have to put up with if the data file has crud at the end of the file. That, not withstanding, can you post your BULK INSERT statement? If the import file isn't too big and contains no private or sensitive information, would you attach the file and post the table creation statement for the target table?
It would also be very helpful if you'd post the exact error you're getting.
Thanks.
--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)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply