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 !

: Seems to be very slow even when I have 200 lines to import --
: Does anybody know faster way to import text file into DB ?

screw addnew and update.  i think this should be much faster..if im wrong,
i apololgize:  use SQL INSERT INTO STATEMENTS.

ie.

dim db as database
set db = opendatabase(whatever)

while (text file not empty)
        db.execute ("INSERT INTO MYTABLE (FIELDNAME) VALUES
        ('nextlinefromtextfile')")
wend

that's assuming you cant find some way of attatching a text file as a
table.. but for a 200 line file it hardly seems worth it.



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

First, you don't have to edit before you addnew.

Second, you might tryp putting these things inside transactions.  If you
are breaking the file into lines and putting them into the database, you
should be keeping the entire operation inside a transaction anyway, in case
you have to roll back due to an error.  Errors happen in file handling all
the time.  The transaction will speed up your operation as well.

Third, if you can, just dump the whole file as is into a long-binary
string.  That is probably the fastest way to get it into the database, if
you don't need to be able to get at each line record by record.


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.



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

I just had to convert an ASCII database into an access database using VB#.0
and yes, it took a while. At 1980 records, it took about 10 1/2 minutes to
perform.... UGHH  I never did find a faster way?  Sorry.


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.



Tue, 04 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

I do this quite a bit. If you wrap the entire thing around beginTrans and
committrans it goes much faster.
like this

Sub getText()
    BeginTrans
    For x = 1 To numberOfThingsToImport
        Data1.Recordset.AddNew
        Data1.Recordset(0) = myString(x)
        Data1.Recordset.Update
    Next
    CommitTrans
End Sub

You may have to commit periodically if you are importing A LOT of text
(memory space) but I regularly import 4 MB or so and it only takes a few
minutes.
The other advantage to this is if something happens and your app crashes
before it can finish, nothing gets added so you don't have to go into the
database and figure out what's there and what isn't.

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.



Sun, 09 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

Have you tried an SQL Query?
     Insert into mutable(fields) VALUES(you can use variables here)
I have processed files in a fraction of that time



Quote:
> I just had to convert an ASCII database into an access database using
VB#.0
> and yes, it took a while. At 1980 records, it took about 10 1/2 minutes
to
> perform.... UGHH  I never did find a faster way?  Sorry.



> > > 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.



Tue, 11 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

Have you tried this:

1.) Attach the table using the .txt ODBC
2.) Then "INSERT INTO <TABLE1> SELECT * FROM <TABLE2>"

Kevin



Tue, 11 Jul 2000 03:00:00 GMT  
 Importing Text File into DataBase Slow !

You must have something in your code that slows it down.
I have an application that processes multiple files with on average 10000
records a day into an access MDB database, then moves it to a SQL server
table.  This process usually takes about 15 minutes.  That includes all the
setup and clean up routines.
Are you using a data control bound to a database on a form?
If so maybe that is slowing you down.  I code all my database connections
and use the opendatabase and open recordset commands in code.  So instead
of Data1.Recordset.AddNew I have rs.addnew, rs("FieldName")=myString,
rs.update
Plus, in your example you had Data1.Recordset.Edit, Data1.Recordset.Addnew.
You can get rid of the .Edit before the addnew.  It does you no good. Let
me know if you have any questions.



Sun, 16 Jul 2000 03:00:00 GMT  
 
 [ 8 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