Code to parse out selected fields of .txt to new table

[dk4300]

Context-
I have a text file (.txt) that is comma delimited and has each text field surrounded in “”. It does not contain column headers.

I need to import 50 of the 300 rows so am using code instead of linking or importing (via 'get external data') since it exceeds the 255+ limit. I am building a database in Access 2007 off this file.

I have a table that shows every field number and field name in the text file. I added a Y/N box where I marked the fields I want to import and built a query to show only those fields (QRY_IMPORT). Column [FieldID] is the location and [FieldName] is the name. There are 50 records, so I need [FieldID] = 1,2,61,115,154, 155,166,167, etc…

I would like to allow the flexibility to add fields in the future which is why I am planning on building a new table each time with the selected [FieldName] being the column headers.

Question-
What is the best way to go about this?

Step one, I would think is to build a new table with fields selected in the [FieldName] of QRY_IMPORT

Step two, would be to parse out each line, create a count for what field it’s on, see if that count matches one of the fields that I’m looking for and it so, write it to the table. I have written procedures that go though each line of a query using .MoveNext and could go through each of the 50 records in the query, then loop through the code until it gets to that field, write that field to a table, then move to the next line in the query, etc, but then it would have to cycle through 50 fields or query and text of potentially 4000 rows.

I would appreciate any pointers, examples in any level of detail for how this would be done (expertly, efficiently). If the answer involves arrays, I have never used them and don’t really know how they work (is it time I learn?) :)

[Rabbit]

It's time you learned to use arrays.

Since it's delimited by a comma, you can read the row, and then use the Split() function to get an array. Then, if you need the first column, you can access that using arrayVariable(0 ). If you need the 8th column, then arrayVariable(7 ). And so on. Plus, you can just loop through an array or recordset containing the columns you want instead of coding it out manually.

[dk4300]

See, when you start talking about arrays I get lost. I'm having a hard time getting the (fuzzy to me) concept into practice for what I'm trying to accomplish. I think I need more hand holding.

To practice my parsing, I had grabbed some code from the web and updated it with a count and an if/then statement to randomly pull fields 1 and 93. Instead of having the variables in the code (in this case 1 and 93) I would want to pull all the intergers in [QRY_IMPORT]![FieldID]. That's what you said in your response, but I don't know how to make it happen. Could you please help me update my code?

Sub ReadTxtFile()
   
   Dim strTxt As String, v1 As Variant, v2 As Variant, intColumnCount As Long
   
   Open "H:\filename.txt" For Input As #1
   
   Do While Not EOF(1)
      Line Input #1, strTxt
      v1 = Split(strTxt, ",")
      
      intColumnCount = 1
      For Each v2 In v1
        
        If intColumnCount = 1 Or intColumnCount = 93 Then
            Debug.Print v2;
        End If
        
        intColumnCount = intColumnCount + 1
      Next
   Loop
   Close #1
End Sub

[dk4300]

OK, now I have the contents of [FieldID] from QRY_IMPORT saved as a dynamic array. I still don't know how to use my new array (arrFieldID) to see if the text field I am on exists within that array. Because I don't know arrays. (But I'm learning)

[Rabbit]

Please use code tags when posting code.

An array is merely a bunch of variables. For example, Dim arrayVar(3) As Integer is merely 4 variables (4 because arrays start with index 0). They are accessed like this
arrayVar(0)
arrayVar(1)
arrayVar(2)
arrayVar(3)

So as you can see, there's no need for you to keep a count of which column you're at because the index number is directly related to the column number.

[dk4300]

I see, but I don't know how to apply.

Right now, my text parsing is still generally happening like in lines 7-12 above.
I am also looping through a query of column headers names, saving them as an array, then making a new table with those names as the field names. I'm currently stuck on writing the parsed text fields to their variable columns.

[Rabbit]

Basically, if I had a recordset of numbers that specify which column positions I want from a text file, I could code it like this.

' Code that opens text file for reading
' Code that returns recordset containing numbers representing the columns I want from the file.

Do Until objTextFile.AtEndOfStream
   strTemp = objTextFile.ReadLine()
   arrTemp = Split(strTemp, ",")
   strResult = ""
   rstVariable.MoveFirst

   Do Until rstVariable.EOF
      strResult = strResult & arrTemp(rstVariable("FieldName")) & " | "
      rstVariable.MoveNext
   Loop

   Debug.Print strResult
Loop

[dk4300]

Thanks for your help. This is going to kill me.

[Rabbit]

What part are you stuck on? The code itself is fairly straightforward . All it does it read through a file line by line and get the fields it needs.

[dk4300]

I'm stuck on my inexperience. I get how straightforward it is. Unless you're me. New to arrays, objFSO, etc.

I have an error "Object Required" on strTemp* = *objTextFile.Re adLine()

Sub TestText()
    
    Dim strTemp, strResult As String
    Dim arrTemp() As Long
    Dim rstVariable As Recordset
    Dim db As Database
    Dim objTextFile
    Dim objFSO
    
    myFile = "C:\text.txt"
    
    Const ForReading = 1
    Set db = CurrentDb
    Set rstVariable = db.OpenRecordset("TBL_IMPORT")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile(myFile, ForReading)

    Do Until objTextFile.AtEndOfStream
        strTemp* = *objTextFile.ReadLine()
        arrTemp = Split(strTemp, ",")
        strResult = ""
        rstVariable.MoveFirst
        
        Do Until rstVariable.EOF
            strResult = strResult & arrTemp(rstVariable("FieldName")) & " | "
            rstVariable.MoveNext
        Loop
    
        Debug.Print strResult
    Loop
       
End Sub

[dk4300]

I copied and pasted and now when I put it back in this post I see some *s snuck into line 19. No wonder.

[Rabbit]

Those asterisks shouldn't be there. It was probably a copy and paste error. It does that sometimes on this site.

[dk4300]

That * thing happened to me every time I pasted code. It was making me crazy because you can't see them in the code window, but the code errors out on it.

I'm ever closer, thank you! Can you help me understand how to handle the "FieldName" portion?

strResult = strResult & arrTemp(rstVari able("FieldName ")) & " | "

How should I be updating the "FieldName" to show my variable approx 50 field names? If that question doesn't make sense, it's because I don't get this line. Darn arrays! :)

 Sub TestTextWeb()
    
    Dim strTemp As String
    Dim strResult As String
    Dim arrTemp As Variant
    Dim rstVariable As Recordset
    Dim db As Database
    Dim objTextFile
    Dim objFSO
    
    myFile = "C:\text.txt"
    
    Const ForReading = 1
    Set db = CurrentDb
    Set rstVariable = db.OpenRecordset("TBL_IMPORT")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile(myFile, ForReading)

    Do Until objTextFile.AtEndOfStream
        strTemp = objTextFile.ReadLine()
        arrTemp = Split(strTemp, ",")
        
        strResult = ""
        rstVariable.MoveFirst
        
        Do Until rstVariable.EOF
            strResult = strResult & arrTemp(rstVariable("FieldName")) & " | "
            rstVariable.MoveNext
        Loop
    
        Debug.Print strResult
    Loop
       
End Sub

[dk4300]

Oh, I see I am reading the list of fields from the field list table. With that update, now I have a type mismatch on line 27.

[Rabbit]

"FieldName" is the name of the field that contains the column numbers you want to retrieve. You may have to subtract 1 since arrays start at 0.