My function is very slow. My users are very angry.

[OhioSteve]

My application sucks in huge text files and puts them into tables in an .mde application. The text files are about 125 mb apeice. I am doing this on a box running at 2.4ghz with 1gb of RAM. It takes about 30 minutes to suck up each monster text file.

I am using DoCmd.TransferText with an importSpec. The file is position-delimited. Is there anyway to speed up the process?

 

[CautionMP]

OhioSteve,
IMHO [tt]DoCmd.TransferText[/tt] is not the way to go for really large files. Once you start it you are committed and there is little you can do notify the users of the progress.

I don't know if it will speed things up or not (125 MB is a pretty big file) but here is a thought. Try opening the file in VBA and processing line by line. This should:[ul]
[li]Greatly reduce the memory overhead since you will only have the details of one record loaded in memory at a time.[/li]
[li]Moving one record at a time will allow you to throw a [tt]DoEvents[/tt] statement in every once in a while so the database can be used while the process in happening.[/li]
[li]Using the same method as above you can provide feedback to the user on the overall progress of the import.[/li][/ul]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MichaelRed]

search in these (Tek-Tips) fora for "basGrab" (or some minor variation). there are several threads with this handy (AND FAST) little utility at least a couple include a sample of how to also then parse the input and place into a table.




MichaelRed

 

[OhioSteve]

CautionMP,

I have done what you describe in java. However, java has classes that read files. I played with the vba interface and I could not find a class called "fileReader" or "reader".

 

[JerryKlmns]

I use a Schema.ini way which swallows a 165MB txt file (~80k records of ~240 fields) in 4min 30sec time. A thread for that thread181-1085235

 

[JoeAtWork]

OhioSteve - the class you are looking for is the FileSystem object, and it is available after you reference the Microsoft Scripting library.

 

[CautionMP]

OhioSteve,
A couple of thoughts.
[ul][li]You don't need the [tt]FileSystemObject[/tt] to open and read a file, there are native functions in VBA that will do it for you.[/li]
[li]Import specs provide a great GUI interface for building text file specifications that you can leverage in your code.[/li][/ul]

Here is a rough concept (sample) that incorporates the two ideas.

Sub ImportSomStuff()
On Error GoTo ImportSomStuff_Error
Dim rstImportSpec As DAO.Recordset, rstOutput As DAO.Recordset
Dim varImportSpec() As Variant
Dim intFileIn As Integer
Dim strBuffer As String

'Open the imposrt spec
[b]'***Change the SpecName below to your real filename[/b]
strBuffer = "SELECT FieldName, Start, Width " & _
                "FROM MSysIMEXSpecs " & _
                "INNER JOIN MSysIMEXColumns " & _
                "ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID " & _
                "WHERE SpecName='[b]FileList Link Specification[/b]' " & _
                "AND SkipColumn=False;"
Set rstImportSpec = CurrentDb.OpenRecordset(strBuffer)
'END 'Open the imposrt spec

'Open the output file
intFileIn = FreeFile
[b]'***Change the filename below to your real filename[/b]
Open "[b]C:\YourFIle.txt[/b]" For Input As #intFileIn
'END Open the output file

'Open the output recordset
Set rstOutput = CurrentDb.OpenRecordset("SELECT * FROM tblDestination;", dbOpenDynaset)
'Open the output recordset

'Loop through the input file
Do
  Line Input #intFileIn, strBuffer
  rstImportSpec.MoveFirst
  'Create a new record in the output and loop through the spec
  rstOutput.AddNew
  Do
    'Use the Mid() function and the import spec to parse the data
    rstOutput.Fields(rstImportSpec.Fields("FieldName")) = _
    Mid(strBuffer, rstImportSpec.Fields("Start"), rstImportSpec.Fields("Width"))
    rstImportSpec.MoveNext
  Loop Until rstImportSpec.EOF
  rstOutput.Update
Loop Until EOF(intFileIn)

ImportSomStuff_Exit:
On Error Resume Next
rstImportSpec.Close
Set rstImportSpec = Nothing
rstOutput.Close
Set rstOutput = Nothing
Close #intFileIn
Exit Sub

ImportSomStuff_Error:
Stop
Resume ImportSomStuff_Exit
End Sub

This is roughly typed and untested so I appologize for any errors, but since you've worked with Java...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[OhioSteve]

JerryKlmns-

I found a function on the ms website that makes schema.ini files. It worked great!

Now I need to use the schema.ini file with an ado connection. I plan to use your sample code in the other thread for inspiration. I will post again soon.

-OhioSteve

 

[OhioSteve]

Your sample code uses the adox object. What do I need to do so that Access has that class definition?

 

[OhioSteve]

I found some articles at support.microsoft.com that really helped! I was able to read the file into a recordset. I have a somewhat different question now, and I will post it as a separate thread.

 

[spizotfl]

OhioSteve, if you don't mind my asking, where did you find the function for creating the Schema.ini on the MS site? if you have it bookmarked or written down, that is cool. please don't spend too much time looking, though.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[OhioSteve]

to make a schema.ini file from a db table, consult~
from

http://support.microsoft.com/kb/210001

to read a text file into a recordset using a schema.ini file, consult~
from

http://support.microsoft.com/kb/q149090/

NOTE: This will only work if the db table and the text file have IDENTICAL specifications.

The next step is to import the DAO recordset into the db table. I started a thread on that topic in this forum.

 

[JerryKlmns]

OhioSteve

For ADOX add a reference to your version of Microsoft ADO Ext. 2.x For DDL and Security