May 7, 2002 at 9:19 am
Hi.
I have a number og text files that I am bulkInserting into the db. THese files are generated by an external system which I have no control over. These text files have an inconsistant number (7-8) of Carriage returns appended at the end of the data.
When I run the Transact BulkInsert statement, I get the ff error;"[Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
". If I delete these Carriage returns , the statement succeeds.
How can I programmatically remove these Carriage returns ? However I don't want to use 1. DTS, 2. To read the text files line by line and test for each line if it is blank, until AtEndOfStream is true. This method is too slow (Time is critical for me here)
I'd appreciate help in this matter.
May 7, 2002 at 1:49 pm
There is a readall method which is faster (think set based vs cursor). In some cases you find it faster to use the older Vb Open style file access. I have not tried the stream object as far as timing it but you might be able to pick up the entire file more efficiently still with it. If...and a big if..you can identify a pattern, you could load into memory, do a replace, then write it back to disk, then do the bulk insert.
Andy
May 8, 2002 at 5:19 am
I have also heard of being done with ADO to bring in as a recordset and update the file like a datasource. But I have not seen an example.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 4:30 pm
You can use UltraEdit 32 program to fix anything inside the .txt file and after that you can imported to sql.
August 29, 2002 at 10:50 am
Hi,
I'm new to using SQL-DMO in vb6. I just wanted to know if you have a sample or an example of using the BULK INSERT from VB6 to put records in the tables on the SQL Server.
Any help will be appreciated.
Thanks,
Insha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply