VBA for importing a formatted txt file

[Uncle Dickie]

Hi All,

I am trying to bring a text file in to an Access table using VBA.

My problem is that the generated text file has a mixture of delimited and non-delimited data and I can't seem to work out how to use the Import Wizard to save a particular format and then use it with

DoCmd.TransferText acImportDelim, "MySavedFormat", "TableName", "C:\File.txt", True

Maybe that is not even the right way of going about it.

An example of the text file is as follows:

<Sensor Name>Name
<Sensor ID>123
<Sample Rate>10
<Scale>C
time,temperature,sample
01/01/01 10:00,20,1
01/01/01 10:30,21,2
01/01/01 11:00,20,3

And I would like a table in the form:

SensorID  Scale  time,           temperature  sample
123       C      01/01/01 10:00  20           1
123       C      01/01/01 10:30  21           2
123       C      01/01/01 11:00  20           3

Any pointers would be very much appreciated.

Richard

[zmbd]

Uncle Dickie:
From what you have listed, the import via the wizards will not work.
Is this a dot-TXT type file?
If so then you can open the file for import and read the lines in and parse them.
The basics would be here:File I/O Using VBA

Once you have the file open, you can then read the line in, use the string functions to determine the headers and then parce the remaining.

Have you tried any vba yet?
If so, would you post your work?

[Uncle Dickie]

Thanks for the pointer.
I have not tried anything other than the TransferText function of my original post but I will now go through the File I/O parsing and see where I get. Once I have made some progress (or not!) I will post again.

[zmbd]

YEA!
I look forward to helping you do this... should be fairly straight forward once you get the file open, a loop, a select or if-then, and maybe a split (?).
If you do need to post back, please let us know which version of Access you are using as there are somethings you can do in the new versions and not in the older.

[Uncle Dickie]

Thanks ZMBD!

I am getting somewhere now, although not with your suggestion...

I have found another reference to using Microsoft Scripting Runtime and have been working through something with that (as below). Does that seem like a sensible route to follow or should I go back to your suggestion of 'Open x For Input'?

I must confess I got confused reading your link so haven't tried it, but then I got confused reading up on this solution as well and it wasn't until I tried it that it clicked so I am still happy to change tack if it seems more logical.

My code so far is:

'Need to ensure that Microsoft Scripting Runtime is enabled from the Tools, References menu
 
    Dim myFileName As String
    Dim myID As String
    Dim myScale As String
 
    myFileName = "C:\Instrument1_20.wdf"
 
    With New Scripting.FileSystemObject
        With .OpenTextFile(myFileName, ForReading)
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 1
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 2
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 3
            If Not .AtEndOfStream Then myID = .ReadLine         'Line 4 is Sensor ID
            myID = (Mid(myID, InStr(myID, ">") + 1))            'Remove the leading text
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 5
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 6
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 7
            If Not .AtEndOfStream Then myScale = .ReadLine      'Line 8 is Scale
            myScale = (Mid(myScale, InStr(myScale, ">") + 1))   'Remove the leading text
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 9
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 10
            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 11
 
'            MsgBox (myID + " " + myScale)
 
'            Do Until .AtEndOfStream
'                MsgBox (.ReadLine)
'            Loop
        End With
    End With

This has got me beyond the 'header' info in the file and I am intending to use the Do Until Loop to split the remaining lines of actual data in to an array.

I would then populate a table with myID, myScale and each entry in the array.

(Haven't worked out how to append the info to a table yet!)

BTW I am using Access 2003 but I am writing this for a specific company and I don't know what they are using yet so I may have to change a couple of things...

[zmbd]

You can use either what I've posted you to, or you can use the scripting as you've started to do; however, I prefer the method I linked you to in that it is native to the VBA code therefor does not require any additional library references.
I'm in one of my remote labs that doesn't have access installed for a short while today so I'll have to take a look at your code a tad bit later; hwoever, I know that Rabbit, NeoPa, and ADezii will be around shortly and can fix you up too...

As for suggestions - as a air-code:
Starting on line 11
Start a do while loop checking for the end of string
Read your line
Parse the first charactor in the string
select case statements
check for the "<" if found then find the the value you're after
check for the "t" and discard etc...
Check for numeric... then use the split function against the string and parse.
have a default case to either throw an error or somehting.
loop back...

the way you have it coded now, if there's any corruption within the file, missing lines, added lines, etc... you will not have any usuable information.

[Uncle Dickie]

Thanks again. I have something working (with absolutely no error trapping as you mentioned) as follows:

    'Need to ensure that Microsoft Scripting Runtime is enabled from the Tools, References menu
 
    DoCmd.SetWarnings False
 
 
    Dim myDirectory As String
    Dim myFile As File
'    Dim myFileCount As Integer
 
    Dim myFileName As String
    Dim myID As String
    Dim myScale As String
    Dim myValues() As String
    Dim myReadingNumber As Integer
    Dim i As Integer
    Dim myDate As String
    Dim myReading As Single
 
    myDirectory = "\\Lasfs01\win_apps\ENG\TEMP\Richard\WiFi Trial"
 
    With New Scripting.FileSystemObject
        With .GetFolder(myDirectory)
            For Each myFile In .Files
                If LCase(Right(myFile.Name, 4)) = ".wdf" Then
                    myFileName = myDirectory & "\" & myFile.Name
 
                    With New Scripting.FileSystemObject
                        With .OpenTextFile(myFileName, ForReading)
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 1
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 2
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 3
                            If Not .AtEndOfStream Then myID = .ReadLine         'Line 4 is Sensor ID
                            myID = (Mid(myID, InStr(myID, ">") + 1))            'Remove the leading text
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 5
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 6
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 7
                            If Not .AtEndOfStream Then myScale = .ReadLine      'Line 8 is Scale
                            myScale = (Mid(myScale, InStr(myScale, ">") + 1))   'Remove the leading text
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 9
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 10
                            If Not .AtEndOfStream Then .SkipLine                'Don't need Line 11
 
                            i = 0
 
                            Do Until .AtEndOfStream
                                myValues = Split(.ReadLine, ",")
                                myReadingNumber = myValues(3) + i
                                i = i + 1
                                myDateArray = Split(Left(myValues(0), 10), "-")
                                myTimeArray = Split(Trim(Mid(myValues(0), 11)), ":")
                                myDate = myValues(0)
                                myReading = myValues(1)
 
                                DoCmd.RunSQL "INSERT INTO DestTable (SensorID, TempScale, SampleNumber, [Time], Temperature) VALUES ('" & myID & "', '" & myScale & "', " & myReadingNumber & ", #" & myDate & "#, " & myReading & ");"
                            Loop
                        End With
                    End With
 
                    With New Scripting.FileSystemObject
                        If .FileExists(myFileName) Then
                            .DeleteFile myFileName
                        End If
                    End With
 
                End If
            Next
        End With
    End With
 
    DoCmd.SetWarnings True

I am now spent for the day but I will go back tomorrow and have a look at doing it the way you originally said as I fully take your point about additional libraries. Either way I'll also put in some error trapping and case statements as you suggest to try and make it robust!

You might notice in the above (complete) code that it actually checks a directory for all .wdf files and then brings data in from each one before deleting the file. The application I am dealing with is a bunch of sensors periodically sending packets of their data as a wdf file. A user has asked that these get concatenated in to a database from which they can do any form of data manipulation and Access was their choice.