Import new data from a text file based on certain field criteria.

[maudedog]

I have a little VB knowledge but this one is way beyond my scope. This is a multipart problem I am trying to find a solution.

We have a tab delineated text file with 11 fields that is constantly updated by another program. We would like to copy/import just two fields of that data, let’s say fields #9 & #11 into Excel but only bring in the new data since the previous import. So if initially there are 5 lines of data, that would come in and populate the first 5 rows and 2 columns in a worksheet. Then subsequently it would bring in only anything added after the initial copy/import. I have been able to write a routine that simply copies everything and overwrites what is there but this is not what we want.

The next part of this is that based on certain criteria, the data will be copied/imported into certain columns. The criteria variables will be in fields #2 and #6. So for example, if field #2 = 1, and field #6 = 2, then fields #9 & #11 will go in columns A & B. If field #2 = 2 and field #6 = 1, then fields #9 & #11 will go into columns C&D. There would most likely be 6 possible combinations of those variables (1/1, ½, 1/3, 2/1, 2/2, 2/3) so columns A-L could be populated. Note that field #9 could be blank in which case it would just bring in field #11 or just copy the blank text. These variable numbers could be referenced to cells in the workbook where we select which variables will correspond to which columns. For example if we wanted columns A & B to contain the data with the variables 1 & 2, cells G1 & H1 could have #1 & #2 in their cells if that is helpful. There may be times when we want that variable combo of 2/1 to go into cells A & B.

Here is the text file:

0 1 2 0 0 1 b 1648 1648 9:55:02.61 09:55:03
0 2 5 0 0 1 b 1648 1659 9:57:45.70 09:57:46
0 2 7 0 0 2 b 1649 1649 9:58:09.84 09:58:10
0 3 8 0 0 1 s 9:58:09.87 09:58:10
0 1 9 0 0 1 b 1656 1656 9:58:13.80 09:58:15
0 1 11 0 0 1 b 1657 1657 9:58:19.88 09:58:20
0 3 12 0 0 1 s 9:58:19.93 09:58:20
0 1 13 0 0 1 b 1663 1663 9:58:25.77 09:58:26
0 1 14 0 0 2 s 9:58:25.78 09:58:26
0 1 15 0 0 2 s 9:59:22.40 09:59:23
0 2 16 0 0 1 b 1664 1664 9:59:22.41 09:59:23
0 2 17 0 0 2 s 1671 1671 9:59:26.33 09:59:27

I have attached a copy of screenshots of the text file along with what the workbook could look like.

The last part is ideally this routine would only run when the text file has been updated but I am not sure how possible that is. Otherwise I can just put this on a timer that will run every minute or so.

Being a bit of neophyte with VBA I have found this forum to be an awesome resource so I hope I have explained this correctly. If this info is already posted somewhere please point me in that direction. I am always willing to learn but this one is bit beyond me right now.

As always, many thanks in advance.

 

[maudedog]

I got your data file. There were 2 problems.. First, you never said there was a blank record at the top of the file or there was a record of field names. Details matter. Next time, put information like that in your post. Second, while it is a tab deliminated as I suspected, one of the tabs (the one after column 6 with the single alphabet character) refused to be recognized as a deliminator. I found a way around it.

This is the code in its entirety. It works on all the test files I have. Note that in the last test file, every record has the same numbers in columns 2 and 6.

Sub TableMagic()
Dim strTextFile$, rngOutputAnchor As Range
Dim objFSO, strData$, ayDelim, tmpData$, ayLines, ayBody(), i%, ayOutput(), ayRows(), tmp
Dim c1%, c2%, ayOutputTable
Const ForReading = 1

'------------------------------------------------------
'name of the text file
strTextFile = "C:\Users\barneyrubble\Downloads\@TEST\TEST_DATA.txt"
'top left cell of your output table
Set rngOutputAnchor = ThisWorkbook.Worksheets("TESTTABLE").Range("$B$28")
'------------------------------------------------------

'Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Open the text file - strData now contains the whole file
strData = objFSO.OpenTextFile(strTextFile, ForReading).ReadAll
'let function pick a deliminator
ayDelim = pfChooseDeliminator(strData)

'create a body array
ayBody = pfStringToArray(strData, ayDelim(1))

ReDim ayOutput(1 To 12, 1 To 1)
ReDim ayRows(1 To 12)

'convery array to output table
For i = 0 To UBound(ayBody)

    'first output column is a function of ayBody (1) and (5)
    c1 = ((ayBody(i)(1) - 1) * 4) + ((ayBody(i)(5) - 1) * 2) + 1
    c2 = c1 + 1
   
    ayRows(c1) = ayRows(c1) + 1: ayRows(c2) = ayRows(c2) + 1
    If UBound(ayOutput, 2) < ayRows(c1) Then ReDim Preserve ayOutput(1 To 12, 1 To ayRows(c1))
   
    ayOutput(c1, ayRows(c1)) = ayBody(i)(8)
    ayOutput(c1 + 1, ayRows(c1)) = ayBody(i)(10)
   
Next i
    'transpose to get into right orientation and write to the spreadsheet
    ayOutputTable = Application.WorksheetFunction.Transpose(ayOutput)
    rngOutputAnchor.Resize(UBound(ayOutputTable, 1), 12) = ayOutputTable
   
'Cleanup
Set objFSO = Nothing
Set rngOutputAnchor = Nothing
Exit Sub

WriteTextFileToSpreadsheet:
    Dim r%, c%
    r = -1: c = -1
    With rngOutputAnchor
        For r = 0 To UBound(ayBody)
            For c = 0 To UBound(ayBody(r))
                .Offset(r, c) = ayBody(r)(c)
            Next c
        Next r
    End With
End Sub

Private Function pfStringToArray(ByVal strData$, ByVal strDelim) As Variant
Dim booSpace As Boolean, ayData, ayBody(), i%, lenStart%, lenEnd%, tmp

    'test for space deliminated (vs tab deliminated)
    booSpace = (Len(strData) - Len(Replace(strData, vbTab, ""))) = 0
   
    'create ayData to read and ayBody to write
    ayData = Split(strData, vbCrLf)
    ReDim ayBody(0 To 0)
   
    'create a deliminated array for each ayData line
    For i = 0 To UBound(ayData)
   
        'skip records that have no data or start wiht text (i.e., are the fields row)
        If Len(ayData(i)) > 0 And IsNumeric(Left(ayData(i), 1)) Then
           
            If booSpace Then 'get rid of all excess spaces & convert to vbTab deliminatd
                Do
                    lenStart = Len(ayData(i))
                    ayData(i) = Replace(ayData(i), "  ", " ")
                    lenEnd = Len(ayData(i))
                Loop Until lenStart = lenEnd
                ayData(i) = Replace(ayData(i), " ", vbTab)
            End If
            'switch tab deliminator to strdelim (one of the tabs was refusing to be recognized)
            ayData(i) = Replace(Trim(ayData(i)), vbTab, strDelim, 1, , vbBinaryCompare)
            'trim the excess deliminator if one exists
            If Right(ayData(i), 1) = strDelim Then ayData(i) = Left(ayData(i), Len(ayData(i)) - 1)
   
            'look to see if there are 10 fields as expected.  adds fields after field 7 if we are short.
            tmp = 10 - (Len(ayData(i)) - Len(Replace(ayData(i), strDelim, "")))
            ayData(i) = Application.WorksheetFunction.Substitute(ayData(i), strDelim, String(1 + tmp, strDelim), 7)
   
            'write ayData(i) to last ayBody record and then expand ayBody for next record
            ayBody(UBound(ayBody)) = Split(ayData(i), strDelim, , vbBinaryCompare)
            ReDim Preserve ayBody(0 To UBound(ayBody) + 1)
       
        End If
       
    Next i
   
    'trim last enpty record and set function to this value
    ReDim Preserve ayBody(0 To UBound(ayBody) - 1)
    pfStringToArray = ayBody

End Function

Private Function pfChooseDeliminator(strSubject) As Variant
'string of shoices for a deliminator to use; infrequently used prinatable characters
Const strChoices = "•|…—¦‡»×©Ø@–¤¬·§«ø~¥Þ=\/$%+:›&!ƒ°"
Dim i%, strD$
    For i = 1 To Len(strChoices)
        strD = Mid(strChoices, i, 1)
        'if the proposed deliminstor character is not in the string, then use that one as deliminator
        'set first array value to true to signal we fond a deliminator
        If Len(Replace(strSubject, strD, "")) = Len(strSubject) Then pfChooseDeliminator = Array(True, strD): Exit Function
    Next i
    'if ALL characters fail, we say so ... first in array is false ... and use Tab
    pfChooseDeliminator = Array(False, vbTab)
End Function

This is awesome - thanks! I know you have put some time into this so it is really appreciated. A couple of notes: You should be getting a link to a updated text file. I changed the values so we had every possible combo of numbers in columns 2 & 6 plus assigned unique numbers to column 9 to better keep track. With this data, it is putting the 1-3 combo in the same columns as 2-1; the 3-1 combo is under 2-3; and then when it hits a 3-3 it crashes. I changed the ayOutput from 12 to 16 and that fixed the 3-3 crash problem. As I picked through the code I just can't quite understand the logic where it is not putting the 1-3 and 3-1 combos in correctly. Again, I cannot thank you enough for the help with this. Once I get this project put together I will send you the completed details.

 

[mmhill]

As I mentioned in my first post, it appears your description has errors - the description you gave with the potential combinations didn't match the output in the example file and didn't make sense logically or follow a pattern. But, the output file both made logical sense and followed a pattern.

You wrote:
"So for example, if field #2 = 1, and field #6 = 2, then fields #9 & #11 will go in columns A & B. " >>> This makes sense and is the same as in the output file.

"If field #2 = 2 and field #6 = 1, then fields #9 & #11 will go into columns C&D. " >>> This is inconsistent with the example output that has a 2/1 combo mapped to E & F (2 times). Th eoutput file also has a 1/2 combo mapped to columns C & D.

"There would most likely be 6 possible combinations of those variables (1/1, ½, 1/3, 2/1, 2/2, 2/3) so columns A-L could be populated." >>> This looks wrong as it is inconsistant with the output file and doesn't makse sense or folloe a pattern.

You say there are 6 possible combinations that populate coluns A - L. You say: 1-1, 1-2, 1-3, 2-1, 2-2, 2-3. The output file has 5 combos ... 1-1, 1-2, 2-1, 2-2, 3-1. The output file example has a 3-1, that is not on your list and neither of the 1-3 or 2-3.

Here are the actual mappings on your first example data

If we look at the combinations and where they actually land on the output table example here's what you have

So, 5 cases are represented in your data. The 3-2 combo gong to K & L comes from the pattern of the first 5 items.

If you are mapping a 1-3 case or a 2-3 case, where do they go? 1-3 cannot go in between 1-2 and 2-1 as they go to columns C D E F in your output data. Case 2-3 can't go between 2-2 and 3-1 as those map to G H I J in your output data. Further, your output data maps to columns A - J. If you are mapping to A - L, that only leaves 2 columns left (K & L) that are not mapped. But, a 1-3 and 2-3 case would require 4 open columns. This doesn't makse sense.

What does make sense is you transposed a 1-3 case from 3-1 and transposed a 2-3 case from 3-2. If that is true, then everything fits .. it's logical, the output is a function of the inputs and it matches the example output data.

If I'm wrong, I'm wrong and we can take another approach to mapping columns 2 and 6 to columns.

 

[mmhill]

You asked why 1-3 was mapping to same columns as 2-1 and 2-3 was mapping to columns 3-1. It's the pattern algorithm.

The first column is determined by the formula = ( ((Input Column #2 Value -1) * 4) + ((Input Column #6 Value -1) * 2) + 1 )
The second column is Column 1 + 1 to make the A/B, C/D, E/F, etc. pairs

Using that formula, 1-3 inputs gets the same result as a 2 - 1 input and 2-3 gets the same result as 3-1. For that matter, 3-3 gets the same results as 4-1 and so on. Here the math ...

 

[maudedog]

You asked why 1-3 was mapping to same columns as 2-1 and 2-3 was mapping to columns 3-1. It's the pattern algorithm.

The first column is determined by the formula = ( ((Input Column #2 Value -1) * 4) + ((Input Column #6 Value -1) * 2) + 1 )
The second column is Column 1 + 1 to make the A/B, C/D, E/F, etc. pairs

Using that formula, 1-3 inputs gets the same result as a 2 - 1 input and 2-3 gets the same result as 3-1. For that matter, 3-3 gets the same results as 4-1 and so on. Here the math ...

View attachment 80708

Got it, thanks! I do apologize for the incompleteness of my initial specifications and files. The app that generates this text file will never be populating the file with all nine possibilities at the same time, usually 4-5 at most so the first text file I uploaded only had what it had. I manually made up the one I uploaded today.

Let me work with this for a bit and I will let you know how it goes and again, thanks for the tutorial and your patience!