Import tab delimited file with shifting fields (vba)

[Automattic]

I'd like to use docmd.transfertext to pull data from a series of text files into an access table. Here's my problem, each site has a semi-unique ordering of each parameter. So for example, the sixth field might be "discharge" or it might be "precipitation", but either way the "discharge" field is labeled the same way each time. With a static import specification file i'm finding that the fifth field is being loaded/labeled the same way no matter what's in that column.

I could do a decision tree of if/then statements to choose the appropriate Import Specification file, but I'm hoping there's a different way.

Example data:
http://waterdata.usgs.gov/nc/nwis/uv?cb_00060=on&cb_00045=on&format=rdb&period=1&site_no=02142000
http://waterdata.usgs.gov/nc/nwis/uv?cb_00060=on&cb_00065=on&format=rdb&period=1&site_no=02142000
http://waterdata.usgs.gov/nc/nwis/u...00045=on&format=rdb&period=1&site_no=02142000


Does this make sense?

-A

 

[boblarson]

You probably would be best served to have Access open the file in Excel and do some manipulation of the columns to get them into a standard order and then save and close them and then run the transfer.

 

[Norie]

Is the only problem that the name of that field is different?

Can't you just change the name after the import?

 

[boblarson]

Is the only problem that the name of that field is different?

Can't you just change the name after the import?

It sounds to me that the fields are in a different order. The same fields are there but not in the same order.

 

[Automattic]

It sounds to me that the fields are in a different order. The same fields are there but not in the same order.

Bob's got it. So when you design an Saved Import for one, all of the others get filed under the wrong fields. If I don't use the first line as column headings, is there a way to use something like an access/vba version of hlookup to determine which field to pull?

 

[Norie]

How exactly is this data imported?

Do you have to decide which columns are imported based on the data?

Are the two columns not the same and only one will appear in the data?

 

[Automattic]

How exactly is this data imported?

Do you have to decide which columns are imported based on the data?

Are the two columns not the same and only one will appear in the data?

1) The present method has been to copy data similar to links in original post into a text file. We've been running it through excel to clean it up before transfering it into access, but I've been trying to consolidate this process into access.

2) For the time being, I will always need to pull: site #, time/date, stage. If available (not all sites have each of these) I'd like to get discharge, and precipitation. So let's say these represent fields 1-5 (respectively), if I have a site that doesn't have discharge, my precipitation data will get thrown into the discharge field.

3) The basic identifiers will be at all of them (source, site #, time/date, tmz, and usually stage). The others may or may not.

Did this appropriately answer your questions?

I'm semi-reluctant to use this option, but I could just make the user select the import format. I'm just hesitant in case they chose the wrong option and the data gets thrown into the wrong field.

 

[dk]

Hi

If you're dealing with files like this where the layout and data vary each time I would consider using an alternative solution to TransferText. The example below shows how to use the FileSystemObject to do a basic import of the first four fields in each text file.

Although the code might look a little complicated it should in theory allow you to use one procedure to handle any file you throw at it (obviously you will need to build the logic into it to work out what is in each file after field number 4 but hopefully this skeleton code helps you).

HTH
DK

Sub TestIt()
 
ImportTextFile "C:\temp\testfile.txt"

End Sub


Function ImportTextFile(sFilename As String)

    Dim oFSObj As Object    'Scripting.FilesystemObject
    Dim oFSStream As Object 'Scripting.TextStream
    Dim sLine As String
    Dim arrFileHeader As Variant
    Dim arrLineVals As Variant
    Dim sSQL As String
    Dim lRecTotal As Long, lRecsInserted As Long
    
    On Error GoTo ErrHandler

    Set oFSObj = CreateObject("Scripting.FilesystemObject")

    Set oFSStream = oFSObj.OpenTextFile(sFilename, 1)   '1 is the constant ForReading
    
    'loop until header row is located
    While Not sLine Like "agency_cd*"
        sLine = oFSStream.ReadLine
    Wend

    
    If Not oFSStream.AtEndOfStream Then
    
        'Keep a count of how many records are inserted
        lRecTotal = 0
    
        arrFileHeader = Split(sLine, vbTab)
    
        While Not oFSStream.AtEndOfStream
        
            sLine = oFSStream.ReadLine
        
            arrLineVals = Split(sLine, vbTab)
        
        
            Stop
            
            'at this stage the arrFileHeader should be an array of values that represent the headers in the text file
            
            'arrLineVals should also be an array consisting of the first row of data from your text file (i.e the first
            'row beneath the header
        
            'You may now be able to interrogate the arrays and work out exactly what they contain and then build an
            'SQL statement that will allow you to insert the record into your Access table
            
            'This example will insert the first four values into a table.  For the sake of simplicity
            'I am assuming that your table has the same names as the headers in the text file
            
            sSQL = "INSERT INTO WATER_TABLE (AGENCY_CD, SITE_NO, [DATETIME], TZ_CD) VALUES ("
            sSQL = sSQL & "'" & arrLineVals(0) & "'," & _
                          "'" & arrLineVals(1) & "'," & _
                          "'" & arrLineVals(2) & "'," & _
                          "'" & arrLineVals(3) & "')"


            lRecsInserted = 0
            CurrentProject.Connection.Execute sSQL, lRecsInserted
            
            lRecTotal = lRecTotal + lRecsInserted
            
        
        
        Wend
        
        
    MsgBox lRecTotal & " records have been added.", vbInformation, "Import Complete"
    

    Else
    
        MsgBox "Could not locate text ""agency_cd"" in the text file.", vbExclamation, "File Error"
    
    End If
    
    

    
    'Close and clean up
    oFSStream.Close
    Set oFSStream = Nothing
    Set oFSObj = Nothing
    


    Exit Function

ErrHandler:
    MsgBox Err.Description, vbExclamation, "Error"

End Function

 

[Norie]

Maybe I'm missing something here but does it actually matter if the fields are in the wrong order, or some fields are missing?

If the fields that are in the import have the correct headers it shouldn't.

 

[Automattic]

If the fields that are in the import have the correct headers it shouldn't.

^that's the problem, when the parameters are in a different order, they get the wrong headers.