Importing Text File into DataBase Slow ! 
Author Message
 Importing Text File into DataBase Slow !

Hi All,

I know that SQL doen't have any import file capabilities ---
I need to import text file into Access database -- I loop through
the file, read variable myString, create new record:

        Data1.Recordset.Edit
        Data1.Recordset.AddNew
        Data1.Recordset(0) = myString
        Data1.Recordset.Update

Seems to be very slow even when I have 200 lines to import --
Does anybody know faster way to import text file into DB ?
Would appreciate your advice -- TIA

VB5, Win95

Thanks, Davie.



Sat, 01 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !


writes

Quote:
>Hi All,

>I know that SQL doen't have any import file capabilities ---
>I need to import text file into Access database -- I loop through
>the file, read variable myString, create new record:

>        Data1.Recordset.Edit
>        Data1.Recordset.AddNew
>        Data1.Recordset(0) = myString
>        Data1.Recordset.Update

>Seems to be very slow even when I have 200 lines to import --
>Does anybody know faster way to import text file into DB ?
>Would appreciate your advice -- TIA

>VB5, Win95

>Thanks, Davie.

why the 'data.recordset.edit' line
other than that I use same code, but with relativly small update files
and lots of validation and haven't considered the speed aspect yet. if
there is a faster way to do this I'd be interested too.
--
John McGuire


Sat, 01 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

Assuming the text file is already in a table-like format (e.g.,
tab-delimited, same number of columns per row, etc.) a way to do this is to
attach the file to the Access database using the ISAM text driver, and then
SELECT the data into a table.

There were a couple articles about this general subject in Visual Basic
Programmer's Guide, at the end of 1997. You might check out their web-site
(www.windx.com) and see what's there to download.

--
We must know. We shall know.
   -- David Hilbert

Quote:


>writes
>>Hi All,

>>I know that SQL doen't have any import file capabilities ---
>>I need to import text file into Access database -- I loop through
>>the file, read variable myString, create new record:

>>        Data1.Recordset.Edit
>>        Data1.Recordset.AddNew
>>        Data1.Recordset(0) = myString
>>        Data1.Recordset.Update

>>Seems to be very slow even when I have 200 lines to import --
>>Does anybody know faster way to import text file into DB ?
>>Would appreciate your advice -- TIA

>>VB5, Win95

>>Thanks, Davie.

>why the 'data.recordset.edit' line
>other than that I use same code, but with relativly small update files
>and lots of validation and haven't considered the speed aspect yet. if
>there is a faster way to do this I'd be interested too.
>--
>John McGuire



Sat, 01 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

Quote:

>Hi All,

>I know that SQL doen't have any import file capabilities ---
>I need to import text file into Access database -- I loop through
>the file, read variable myString, create new record:

>        Data1.Recordset.Edit
>        Data1.Recordset.AddNew
>        Data1.Recordset(0) = myString
>        Data1.Recordset.Update

>Seems to be very slow even when I have 200 lines to import --
>Does anybody know faster way to import text file into DB ?
>Would appreciate your advice -- TIA

>VB5, Win95

>Thanks, Davie.

For starters:

Remove the Data1.Recordset.Edit line from your code.  It is not needed
and would cause the import operation to become slow, because you are
trying to edit the current record, then canceling the edit command
with the addnew command.    

Here is an example of an import sub I wrote to import over 30,000
lines from a 2.5MB text file dumb from a mainframe, to create a access
database to preform searches, sorting, etc.... .  I takes about 2
minutes to read the 30,000+ entries on a P133 - 32MB.

Public Sub Import(importFile as String, databaseFile as String)

    Dim textLine As String, tempChar As String
    Dim fooRst As Recordset

    Set db = ws.OpenDatabase(databaseFile)
    Set fooRst = db.OpenRecordset("tblData", dbOpenTable)
    Open importFile For Input As #3
    ws.BeginTrans
    While Not EOF(1)
       Line Input #1, textLine
       With fooRst
          .AddNew
          !field1 = textLine
          etc....
         .Update
      End With
    Wend
    ws.CommitTrans
    Close #1
 Exit Sub



Sun, 02 Jul 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Importing Text File into DataBase Slow !

2. Importing text file slower with VB6???

3. Importing text files to a second database w/in Access

4. How to import text file to access database?

5. Import Text files to the database?

6. Importing text file into Access database via VB5

7. HELP - Importing Text file into database

8. how to import text file into access database

9. Import Text File in database with ISAM driver

10. Import a text file to Database

11. Import Text file into VB Database Table?

12. HOw to import a text file with fixed column in an access database

 

 
Powered by phpBB® Forum Software