Importing Text File with FileSystemObject-problem (1 Viewer)

[JoeCruse]

Good day,

I'm working on a project for a sister plant and their laboratory. We want their lab instruments to spit an instrument test result file out to that computer's hard drive, and then Access will check for its presence, and open the text file and import its contents into 2, parent-child related tables, and then kill the file and await the next one.

I've done something similar at our plant's lab a few years back, with a lot of help from Wayne Ryan and others here. The idea is pretty similar, but they are using different instruments, and the text file is not the same as what we set up for. After a week of researching this, I put together a module in a form that will do the import, using File System Object. It is working rather well, except for the very end, where it is hanging up and giving me a

"runtime error 9 subscript out of range" error.

The module actually throws all of the data into the correct tables, but it is apparently left hanging at the last line of the text file, and so is still looping and trying to assign values to the variables when there is nothing left to grab and assign. It stops there and errors, and then can' finish up the last few lines of code. If I sub out "While Not oFS.AtEndOfStream" (or Do Until) with an If statement, it runs all the way through w/o error, but ONLY pulls the first line of text.

The first 10 lines of this text file will be consistent, but the actual data lines after line 10 will vary in number, depending on the sample analyzed, and there is not EOF character. Here is the module code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim stext As String
Dim stext2 As String
Dim stext3 As String
Dim ResultID As Long
Dim labID As String
Dim ResultDateTime As Date
Dim sdate As Date
Dim program As String
Dim archive As String
Dim concentration As Double
Dim CompoundName As String
Dim SQL As String

Dim oFSO As New FileSystemObject
Dim oFS As TextStream
Dim Varsplit As Variant
Dim Varsplit2 As Variant
Const wMode = 1&

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblXRFResults", dbOpenDynaset, dbSeeChanges)
DoCmd.SetWarnings False
Set oFS = oFSO.OpenTextFile("c:\XRFResults\Analy100.TXT", wMode)
    oFS.SkipLine
    stext = oFS.ReadLine
      Varsplit = Split(stext, ":")
            ResultDateTime = Mid(stext, 25, 2) & "/" & _
                Mid(stext, 22, 2) & "/" & _
                Mid(stext, 28, 4) & " " & _
                Mid(stext, 16, 2) & ":" & _
                Mid(stext, 19, 2)

     stext = oFS.ReadLine
       Varsplit = Split(stext, ":")
       program = Mid(stext, 16, 15)
     oFS.SkipLine
     oFS.SkipLine
     oFS.SkipLine
     oFS.SkipLine
     stext = oFS.ReadLine
        stext2 = Replace(stext, ",", ":")
        Varsplit = Split(stext2, ":")
        archive = LTrim(CStr(Varsplit(1))) & CStr(Varsplit(3))
        labID = CStr(Varsplit(3)) & "-" & CStr(Varsplit(4))
rst.AddNew
rst!ResultDateTime = CDate(ResultDateTime)
rst!sdate = Date
rst!program = program
rst!archive = archive
rst!labID = labID
rst.Update
rst.Close

oFS.Close
Set oFSO = Nothing
Set oFS = Nothing

'Get ResultID PK value from parent table, open file to read again, and run SQL statement insert of instrument data into child table
Set rst = dbs.OpenRecordset("tblXRFResults", dbOpenDynaset, dbSeeChanges)
rst.MoveLast
     ResultID = rst!ResultID
rst.Close

Set oFS = oFSO.OpenTextFile("c:\XRFResults\Analy100.TXT", wMode)
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   While Not oFS.AtEndOfStream
         stext3 = oFS.ReadLine
         Varsplit = Split(stext3, ":")
         [COLOR="Red"]CompoundName = CStr(Varsplit(0))
         concentration = CStr(Varsplit(1))[/COLOR]

              SQL = "INSERT INTO [tblXRFResultsConcentration] (ResultID, Concentration, CompoundName)" & "Values(" & ResultID & "," & concentration & ", '" & CompoundName & "');"
              DoCmd.RunSQL SQL
    Wend

oFS.Close
Set oFSO = Nothing
Set oFS = Nothing

DoCmd.SetWarnings True
Kill "C:\XRFResults\Analy100.txt"
DoCmd.Close acForm, "Form2"
End Sub

I know I'm missing something simple, but cannot see it. It's hanging at the lines in red, where compoundname and concentration are being given values from the text file. Anyone see what I'm missing? Also attached one of the text files. Thank you in advance for your help.

Joe Cruse
Access 2007

 

[spikepl]

Your file contains empty lines at the end. Either prevent it from doing so, or handle the situation

 

[JoeCruse]

What a moron! I never even looked at the text file for empty lines at the end!!!

Thank you for the tip! Cannot get the empty lines out, as ARL, the instrument manufacturer (X-Ray Fluorescence spectrometer, if you were wondering), writes the software, including how the text files are structured. So, we have to deal with blanks. Here is what I did in the last section in question:

Set oFS = oFSO.OpenTextFile("c:\XRFResults\Analy100.TXT", wMode)
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   oFS.SkipLine
   While Not oFS.AtEndOfStream
         stext3 = oFS.ReadLine
         [COLOR="Red"]If Len(Trim(stext3)) > 0 Then[/COLOR]
         
         Varsplit = Split(stext3, ":")
         CompoundName = CStr(Varsplit(0))
         concentration = CStr(Varsplit(1))

              SQL = "INSERT INTO [tblXRFResultsConcentration] (ResultID, Concentration, CompoundName)" & "Values(" & ResultID & "," & concentration & ", '" & CompoundName & "');"
              DoCmd.RunSQL SQL
        End If
    Wend

oFS.Close
Set oFSO = Nothing
Set oFS = Nothing

DoCmd.SetWarnings True
Kill "C:\XRFResults\Analy100.txt"
DoCmd.Close acForm, "Form2"
End Sub

The little If/Then statement lets the While statement progress as long as the line is not blank.

Thanks again!

 

[spikepl]

Kudos on supplying code AND data, i.e. all the relevant info, allowing a diagnosis to be postulated.

Many here seem to think we can read minds and screens remotely

 

[boblarson]

If you use the

Open FilePath For Input As #i

method, it doesn't generate an error.

Rewrite your code using this method and see how it works for you. A sample is below:

Function ImpText(strFile As String)
Dim i As Integer
Dim strHold As String
i = FreeFile
Open strFile For Input As #i
Do Until EOF(i)
    Line Input #i, strHold
    Debug.Print strHold
Loop
End Function

 

[JoeCruse]

Ha!! You CAN'T read minds????

Thanks again!

Bob, thanks for the post. I actually set up another import form (the form is to be opened every so many seconds by the switchboard form to look for these text files and import them if they are there, then the import form closes, all in the background) to do this using the method you're enumerating. Haven't worked it all the way through, because I started reading on the File System Object way of reading through text files, and I wanted to see if I could make it work here.

It's taken me nearly a week to research through all the forums and blogs out there (especially here) to learn more about FSO, as well as the Split function and text parsing. A good exercise that should come more in handy for helping our sister plants out as they expand the amount of data they capture.

Regards and thanks to all,

Joe