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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-10 : 09:18:19
|
| Kudah writes "HiI have huge text files I am wanting to bulk Insert into SQL 2000 tables. These files are generated by an external system which I have no control of. These files have an inconsistant number (6,7 or 8) of Carriage returns at the end of the file. When I use the SQL BulkInsert statement, it gives this error. "Server: Msg 4832, Level 16, State 1, Line 1Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.The statement has been terminated."If I delete these CR's it works. How can I programmatically and efficiently and quickly get rid of them? I've used DTS, it also has the same problem. I don't wanna use the ReadAll method of the Textstream object to read the contents because it is too slow, don't even mention a loop until AtEndOfStream, way too slow.Please help." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-10 : 09:31:37
|
| you need to tell your text file provider to clean up their process.<O> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-10 : 09:37:05
|
| Try very hard to have them clean up the file, but in case they really can't fix it, you can try setting the ROWS_PER_BATCH setting of BULK INSERT to 1. It will slow down the import, but each row will be imported individually, so when it reaches the offending rows and errors out at least the rest of the file will have been imported (I hope, there's no guarantee) Don't be surprised if your import process literally takes 10 times as long with this setting. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-10 : 13:55:09
|
| If you can't get the data provider to clean up their file (you already know that's the best solution) then you might create a pre-process app in VB or something like that to strip out the extra CR's before you do your bulk insert. In fact, you could code VB to execute the bulk insert via osql after it has cleaned the file, if you want to. |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2002-05-14 : 14:28:33
|
| Actually, AWK is a great programming language, and there are DOS versions of it available for free. AWK is a tremendously fast text processing program that works on the assumption of text-in-text-out, one-line-at-a-time. It recognizes patterns in the input file using regular expressions and acts upon them, using an AWK script file.Hence, the entire program could be written in about three lines of AWK code. The result? A text pre-processor that is so powerful that you feel like you just wrote a custom executable just for the cleanup task.Edited by - smerrill on 05/14/2002 14:58:44 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 14:34:27
|
| Hey Shaun, FYI, that link you provided seems to be broken. I've tried 3 times to open it and it gives a "Cannot find Server" error. |
|
|
|
|
|
|
|
|
BulkInsert Problem