Importing ASCII Data (1 Viewer)

JackCrackers · Apr 4, 2011

Hello,

I am writing using VBA inside of autocad or VB 6.0. (So I may be in the wrong place). From either environment I import data to an Access .mdb file. The data is in text files separated by either commas or spaces (fixed width columns). My problem is that these files are very large and it takes to long to load them into the Access file. These files contain numeric data (co-ordinates) and are stored as long integers in Access. I am using Access 2003 type .mdb files. These files can be several millions of lines long.
Here is some typical code that works, but is too slow.

Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("d:\temp3.mdb")
Set rstObj = dbsObj.OpenRecordset("tblcoorsJHL2", dbOpenTable)

'separated by commas
Open "c:\temp\data.txt" For Input As #1
'Start reading text file to temp.mdb

Do
Input #1, ya$
Input #1, xa$
Input #1, za$
yt# = Val(ya$)
xt# = Val(xa$)
If OptionButtonAddMinus Then
zt# = Val(za$) * -1
Else
zt# = Val(za$)
End If
rstObj1.AddNew
rstObj1!yxdata = CLng(yt# * 100)
rstObj1!Xxdata = CLng(xt# * 100)
rstObj1!Zxdata = CLng(zt# * 100)
rstObj1.Update

Loop Until EOF(1)
Close 1

Is there anyway to make this process go fast?

Thanks for reading my post

John

ChrisO · Apr 5, 2011

Best I can do is 17.7 seconds per 1,000,000 records.

In the local application

Code:

Public Declare Function timeGetTime Lib "Winmm.dll" () As Long


Sub TestImport()
    Dim lngStart As Long
    
    Const conDatabasePathAndName As String = "C:\db3.mdb"
    
    lngStart = timeGetTime()

    With CreateObject("Access.Application")
        .OpenCurrentDatabase conDatabasePathAndName
        [color=green]' Pass -1 or 1[/color]
        .Run "ImportData", -1
    End With

    MsgBox (timeGetTime() - lngStart) / 1000    [color=green]' 17.7 seconds[/color]
    
End Sub

In "C:\db3.mdb"

Code:

Public Sub ImportData(OptionButtonAddMinus As Integer)
    Dim ya As String
    Dim xa As String
    Dim za As String
    Dim yt As Double
    Dim xt As Double
    Dim zt As Double
    
    CurrentDb.Execute "Delete * From tblcoorsJHL2"

    With CurrentDb.OpenRecordset("tblcoorsJHL2", dbOpenTable)

        [color=green]' Separated by commas[/color]
        Open "c:\temp\data.txt" For Input As #1
    
        [color=green]' Start reading text file to temp.mdb[/color]
        Do Until EOF(1)
            Input #1, ya, xa, za
    
            yt = Val(ya)
            xt = Val(xa)
            zt = Val(za) * OptionButtonAddMinus

            .AddNew
                !Yxdata = CLng(yt * 100)
                !Xxdata = CLng(xt * 100)
                !Zxdata = CLng(zt * 100)
            .Update
    
        Loop
    End With

    Close 1
    
End Sub

80% to 85% of the time taken is with the: -

Code:

            .AddNew
                !Yxdata = CLng(yt * 100)
                !Xxdata = CLng(xt * 100)
                !Zxdata = CLng(zt * 100)
            .Update

1.
Strings, Doubles, Singles, Longs tend not to make any real difference.

2.
A bit surprising but substituting: -
Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("d:\temp3.mdb" )
Set rstObj = dbsObj.OpenRecordset("tblcoorsJHL2", dbOpenTable)
with
CurrentDb.OpenRecordset("tblcoorsJHL2", dbOpenTable)
Dropped the time from 32 to 17 seconds.

3.
Only used for testing…
CurrentDb.Execute "Delete * From tblcoorsJHL2"
If you use it you may want to set "C:\db3.mdb" to compact on close.

Chris.

JackCrackers · Apr 5, 2011

Hello,
1st, thanks for a quick reply. 17 seconds is much much faster! I hope that I can make your code work in Access. I once took a MS course in programming Access with VBA using Access 97. Since then I have only used VBA inside of AutoCad or VB 6.0 to use Access database files, so Access does not need to be installed on the machine that is running the program. If I understand, the code that you wrote is on a form using VBA inside of Access. This is a possible solution to my problem. Is it possible to run this code from VBA in AutoCad or VB 6.0?

Many thanks,
John

ChrisO · Apr 5, 2011

The code marked as In the local application should be run from ACAD although I don’t have ACAD to test it on. The other code should be in the db3.mdb file.

I had assumed you had Access installed and can’t test it otherwise.

Chris.

JackCrackers · Apr 6, 2011

Hello again

After reading your last reply I put the code for Access in a module in the .mdb file. The other part of the code went on to my form in VBA for AutoCad. It worked the first time out. Ran about 3 times faster than what I was doing before. I have Access installed on my computer, but not always on machines that are running my code. Is there any way to prevent the Access window from showing on the screen? You wrote

' Pass -1 or 1
.Run "ImportData", -1

What is the difference between 1 and -1? I tried both, didn't seem to make a difference. In any case thanks again for your time, much appreciated.

John

ChrisO · Apr 6, 2011

The: -
.Run "ImportData", -1
Should make the zt positive or negative with: -
zt = Val(za) * OptionButtonAddMinus

Code:

With CreateObject("Access.Application")
    .OpenCurrentDatabase "C:\db3.mdb"
    .Run "ImportData", -1
End With

MsgBox "Done"

Save the above code as Test.vbs and run it.
For me, with XP and Access 2003, it does not display Access.
Maybe AutoCad is handling it differently???

Chris.

stopher · Apr 6, 2011

I would have thought that just doing a straight import (e.g. docmd.transfertext) and then running a query to adjust the values would be faster than iterating recordsets.

Just an idea.

ChrisO · Apr 6, 2011

G’day Chris.

Yes, it would certainly be worth a try.

This is what I used to produce the test file: -

Code:

Sub MakeFile()
    Dim lngCount As Long

    Open "c:\temp\data.txt" For Output As #1

    For lngCount = 1 To 1000000
        Write #1, "3.33", "5.55", "7.77777777"
    Next lngCount

    Close #1

End Sub

By all means give it a shot and post back the code and timing results because we never really know till it’s tested.

Regards,
Chris.

DCrake · Apr 6, 2011

ChrisO

Can you clarify the following line

Input #1, ya, xa, za

If the text line contains comma delimiters is it parsing them to the different variables?

ChrisO · Apr 6, 2011

Yep, that’s what it’s doing.

For any given line in the text file such as: -

"3.33","5.55","7.77777777"

Input #1, ya, xa, za

Will put
3.33 into ya
5.55 into xa
7.77777777 into za

Chris.

stopher · Apr 6, 2011

I was using my work laptop which isn't renown for its speed.

Using the method I suggested I got approx 43 secs

Using ChrisO's method I got approx 40 secs.

I thought of another method of creating a link table to the txt file and then just running an append qry. This came in at approx 38 secs.

Not a lot in it.

Chris

ChrisO · Apr 8, 2011

If AutoCad can write this to the file: -

3.33,5.55,7.77777777

rather than this: -

"3.33","5.55","7.77777777"

then the time can be reduced to 16.1 seconds with: -

Code:

Public Sub ImportData(OptionButtonAddMinus As Integer)
    Dim yt As Double
    Dim xt As Double
    Dim zt As Double
    
    CurrentDb.Execute "Delete * From tblcoorsJHL2"

    With CurrentDb.OpenRecordset("tblcoorsJHL2", dbOpenTable)

        Open "c:\temp\data.txt" For Input As #1
    
        Do Until EOF(1)
            Input #1, yt, xt, zt
    
            zt = zt * OptionButtonAddMinus

            .AddNew
                !Yxdata = CLng(yt * 100)    [color=green]' Int(yt * 100) is 0.5 seconds slower[/color]
                !Xxdata = CLng(xt * 100)    [color=green]' Int(xt * 100) is 0.5 seconds slower[/color]
                !Zxdata = CLng(zt * 100)    [color=green]' Int(zt * 100) is 0.5 seconds slower[/color]
            .Update
    
        Loop
        
        Close 1
        
    End With
    
End Sub

It removes the need to import as String and the subsequent need to take the Val() of the String.

Unless you need it don’t use Compact on Close, it can raise an error if the database is re-opened while compacting.

And that’s it from me…maybe.

Chris.

Importing ASCII Data (1 Viewer)

JackCrackers

Registered User.

ChrisO

Registered User.

JackCrackers

Registered User.

ChrisO

Registered User.

JackCrackers

Registered User.

ChrisO

Registered User.

stopher

AWF VIP

ChrisO

Registered User.

DCrake

Remembered

ChrisO

Registered User.

stopher

AWF VIP

ChrisO

Registered User.

Similar threads

Users who are viewing this thread