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.

 

[jdellasala]

VBA is not the Holy Grail. Neither is Power Query, but it IS a LOT easier! This is what I got doing nothing but specifying TAB as the delimiter (PQ thought Colon was)

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
2	0	1	2	0	0	1	b	1648	1648	9:55:02.61	09:55:03
3	0	2	5	0	0	1	b	1648	1659	9:57:45.70	09:57:46
4	0	2	7	0	0	2	b	1649	1649	9:58:09.84	09:58:10
5	0	3	8	0	0	1	s	9:58:09.87	09:58:10
6	0	1	9	0	0	1	b	1656	1656	9:58:13.80	09:58:15
7	0	1	11	0	0	1	b	1657	1657	9:58:19.88	09:58:20
8	0	3	12	0	0	1	s	9:58:19.93	09:58:20
9	0	1	13	0	0	1	b	1663	1663	9:58:25.77	09:58:26
10	0	1	14	0	0	2	s	9:58:25.78	09:58:26
11	0	1	15	0	0	2	s	9:59:22.40	09:59:23
12	0	2	16	0	0	1	b	1664	1664	9:59:22.41	09:59:23
13	0	2	17	0	0	2	s	1671	1671	9:59:26.33	09:59:27
Sheet1

This is with ONE STEP to Remove other columns:

Book6
	M	N
1	Column9	Column11
2	1648	09:55:03
3	1659	09:57:46
4	1649	09:58:10
5	09:58:10
6	1656	09:58:15
7	1657	09:58:20
8	09:58:20
9	1663	09:58:26
10	09:58:26
11	09:59:23
12	1664	09:59:23
13	1671	09:59:27
Sheet1

I realize that's not what you're looking for, but it's not clear what is determining what goes in what column. BTW, it was relatively easy to replace all Spaces with Tabs for the Text file, but posing screenshots is rarely helpful. Use XL2BB instead.
Also, I'm guessing you're using that #$!@ Merge & Center button. There's not much that screws up the structure of a Worksheet more! Use Center Across Selection under Alignment -> Horizontal. This has been requested to be added to the M&C Drop Down for years. I added it to my QAT.
I suspect the codes in columns 1-7 mean something that might be useful in getting this fleshed out further, but as it is it's hard to understand why the output is what it is and what columns are related.
BTW, if this is a single text file that is updated, the Query can be set up to run automatically on opening the file. When open, Refresh All will update it.

 

[maudedog]

VBA is not the Holy Grail. Neither is Power Query, but it IS a LOT easier! This is what I got doing nothing but specifying TAB as the delimiter (PQ thought Colon was)

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
2	0	1	2	0	0	1	b	1648	1648	9:55:02.61	09:55:03
3	0	2	5	0	0	1	b	1648	1659	9:57:45.70	09:57:46
4	0	2	7	0	0	2	b	1649	1649	9:58:09.84	09:58:10
5	0	3	8	0	0	1	s	9:58:09.87	09:58:10
6	0	1	9	0	0	1	b	1656	1656	9:58:13.80	09:58:15
7	0	1	11	0	0	1	b	1657	1657	9:58:19.88	09:58:20
8	0	3	12	0	0	1	s	9:58:19.93	09:58:20
9	0	1	13	0	0	1	b	1663	1663	9:58:25.77	09:58:26
10	0	1	14	0	0	2	s	9:58:25.78	09:58:26
11	0	1	15	0	0	2	s	9:59:22.40	09:59:23
12	0	2	16	0	0	1	b	1664	1664	9:59:22.41	09:59:23
13	0	2	17	0	0	2	s	1671	1671	9:59:26.33	09:59:27
Sheet1

This is with ONE STEP to Remove other columns:

Book6
	M	N
1	Column9	Column11
2	1648	09:55:03
3	1659	09:57:46
4	1649	09:58:10
5	09:58:10
6	1656	09:58:15
7	1657	09:58:20
8	09:58:20
9	1663	09:58:26
10	09:58:26
11	09:59:23
12	1664	09:59:23
13	1671	09:59:27
Sheet1

I realize that's not what you're looking for, but it's not clear what is determining what goes in what column. BTW, it was relatively easy to replace all Spaces with Tabs for the Text file, but posing screenshots is rarely helpful. Use XL2BB instead.
Also, I'm guessing you're using that #$!@ Merge & Center button. There's not much that screws up the structure of a Worksheet more! Use Center Across Selection under Alignment -> Horizontal. This has been requested to be added to the M&C Drop Down for years. I added it to my QAT.
I suspect the codes in columns 1-7 mean something that might be useful in getting this fleshed out further, but as it is it's hard to understand why the output is what it is and what columns are related.
BTW, if this is a single text file that is updated, the Query can be set up to run automatically on opening the file. When open, Refresh All will update it.

Nordic Timing Book v1.7.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	0	1	1	0	0	2	s			9:55:02.31	9:55:02.00
2	0	1	2	0	0	1	b	1648	1648	9:55:02.61	9:55:03.00
3	0	1	3	0	0	1	b			9:57:19.97	9:57:21.00
4	0	1	4	0	0	1	b			9:57:21.41	9:57:21.00
5	0	1	5	0	0	1	b	1648	1648	9:57:45.70	9:57:46.00
6	0	1	6	0	0	2	s			9:57:45.71	9:57:46.00
7	0	1	7	0	0	1	b	1649	1649	9:58:09.84	9:58:10.00
8	0	1	8	0	0	2	s			9:58:09.87	9:58:10.00
9	0	1	9	0	0	1	b	1656	1656	9:58:13.80	9:58:15.00
10	0	1	10	0	0	2	s			9:58:13.82	9:58:15.00
11	0	1	11	0	0	1	b	1657	1657	9:58:19.88	9:58:20.00
12	0	1	12	0	0	2	s			9:58:19.93	9:58:20.00
13	0	1	13	0	0	1	b	1663	1663	9:58:25.77	9:58:26.00
14	0	1	14	0	0	2	s			9:58:25.78	9:58:26.00
15	0	1	15	0	0	2	s			9:59:22.40	9:59:23.00
16	0	1	16	0	0	1	b	1664	1664	9:59:22.41	9:59:23.00
17	0	1	17	0	0	2	s			9:59:26.33	9:59:27.00
18	0	1	18	0	0	1	b	1675	1675	9:59:26.34	9:59:27.00
19	0	1	19	0	0	1	b	1677	1677	9:59:30.48	9:59:31.00
20	0	1	20	0	0	2	s			9:59:30.50	9:59:31.00
21	0	1	21	0	0	1	b	1697	1697	9:59:35.20	9:59:36.00
22	0	1	22	0	0	2	s			9:59:35.22	9:59:36.00
23	0	1	23	0	0	2	s			10:00:06.78	10:00:07.00
24	0	1	24	0	0	1	b	1701	1701	10:00:06.78	10:00:07.00
25	0	1	25	0	0	1	b	1712	1712	10:00:10.97	10:00:12.00
26	0	1	26	0	0	2	s			10:00:10.98	10:00:12.00
srt_data (2)

 

[maudedog]

Nordic Timing Book v1.7.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	0	1	1	0	0	2	s			9:55:02.31	9:55:02.00
2	0	1	2	0	0	1	b	1648	1648	9:55:02.61	9:55:03.00
3	0	1	3	0	0	1	b			9:57:19.97	9:57:21.00
4	0	1	4	0	0	1	b			9:57:21.41	9:57:21.00
5	0	1	5	0	0	1	b	1648	1648	9:57:45.70	9:57:46.00
6	0	1	6	0	0	2	s			9:57:45.71	9:57:46.00
7	0	1	7	0	0	1	b	1649	1649	9:58:09.84	9:58:10.00
8	0	1	8	0	0	2	s			9:58:09.87	9:58:10.00
9	0	1	9	0	0	1	b	1656	1656	9:58:13.80	9:58:15.00
10	0	1	10	0	0	2	s			9:58:13.82	9:58:15.00
11	0	1	11	0	0	1	b	1657	1657	9:58:19.88	9:58:20.00
12	0	1	12	0	0	2	s			9:58:19.93	9:58:20.00
13	0	1	13	0	0	1	b	1663	1663	9:58:25.77	9:58:26.00
14	0	1	14	0	0	2	s			9:58:25.78	9:58:26.00
15	0	1	15	0	0	2	s			9:59:22.40	9:59:23.00
16	0	1	16	0	0	1	b	1664	1664	9:59:22.41	9:59:23.00
17	0	1	17	0	0	2	s			9:59:26.33	9:59:27.00
18	0	1	18	0	0	1	b	1675	1675	9:59:26.34	9:59:27.00
19	0	1	19	0	0	1	b	1677	1677	9:59:30.48	9:59:31.00
20	0	1	20	0	0	2	s			9:59:30.50	9:59:31.00
21	0	1	21	0	0	1	b	1697	1697	9:59:35.20	9:59:36.00
22	0	1	22	0	0	2	s			9:59:35.22	9:59:36.00
23	0	1	23	0	0	2	s			10:00:06.78	10:00:07.00
24	0	1	24	0	0	1	b	1701	1701	10:00:06.78	10:00:07.00
25	0	1	25	0	0	1	b	1712	1712	10:00:10.97	10:00:12.00
26	0	1	26	0	0	2	s			10:00:10.98	10:00:12.00
srt_data (2)

Nordic Timing Book v1.7.xlsm
	A	B	C	D
1	number 1 time 2		number 1 time 1
2	number	time	number	time
3		9:57:45.71	1648	9:57:45.70
4		9:58:09.87	1649	9:58:09.84
5		9:58:13.82	1656	9:58:13.80
6		9:58:19.93	1657	9:58:19.88
7		9:58:25.78	1663	9:58:25.77
8		9:59:22.40	1664	9:59:22.41
9		9:59:26.33
Sheet2

 

[maudedog]

VBA is not the Holy Grail. Neither is Power Query, but it IS a LOT easier! This is what I got doing nothing but specifying TAB as the delimiter (PQ thought Colon was)

Book6
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
2	0	1	2	0	0	1	b	1648	1648	9:55:02.61	09:55:03
3	0	2	5	0	0	1	b	1648	1659	9:57:45.70	09:57:46
4	0	2	7	0	0	2	b	1649	1649	9:58:09.84	09:58:10
5	0	3	8	0	0	1	s	9:58:09.87	09:58:10
6	0	1	9	0	0	1	b	1656	1656	9:58:13.80	09:58:15
7	0	1	11	0	0	1	b	1657	1657	9:58:19.88	09:58:20
8	0	3	12	0	0	1	s	9:58:19.93	09:58:20
9	0	1	13	0	0	1	b	1663	1663	9:58:25.77	09:58:26
10	0	1	14	0	0	2	s	9:58:25.78	09:58:26
11	0	1	15	0	0	2	s	9:59:22.40	09:59:23
12	0	2	16	0	0	1	b	1664	1664	9:59:22.41	09:59:23
13	0	2	17	0	0	2	s	1671	1671	9:59:26.33	09:59:27
Sheet1

This is with ONE STEP to Remove other columns:

Book6
	M	N
1	Column9	Column11
2	1648	09:55:03
3	1659	09:57:46
4	1649	09:58:10
5	09:58:10
6	1656	09:58:15
7	1657	09:58:20
8	09:58:20
9	1663	09:58:26
10	09:58:26
11	09:59:23
12	1664	09:59:23
13	1671	09:59:27
Sheet1

I realize that's not what you're looking for, but it's not clear what is determining what goes in what column. BTW, it was relatively easy to replace all Spaces with Tabs for the Text file, but posing screenshots is rarely helpful. Use XL2BB instead.
Also, I'm guessing you're using that #$!@ Merge & Center button. There's not much that screws up the structure of a Worksheet more! Use Center Across Selection under Alignment -> Horizontal. This has been requested to be added to the M&C Drop Down for years. I added it to my QAT.
I suspect the codes in columns 1-7 mean something that might be useful in getting this fleshed out further, but as it is it's hard to understand why the output is what it is and what columns are related.
BTW, if this is a single text file that is updated, the Query can be set up to run automatically on opening the file. When open, Refresh All will update it.

Just uploaded the text file (in Excel format) and the sample worksheet

 

[mmhill]

I think there was a couple errors in your original post. If so, I have a workign answer, although it doesn't do an incremental read - it reads the whole file. And, so far, it's limited to about 675 lines in the text file. Given how the picture of the text file looks, I assumed it is tab deliminated.

I think these statements are errors based on looking at the images of your text file and desired output file.

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.

In your example table of output, the #2 = 2 and # 6 =1 maps to columns E and F and 1/3 and 2/3 combinations don't make sense (but 3/1 and 3/2 combinations do make sense).

Looking at the output table, number in column 2 gives you the group of 4 columns to write to (1st group of 4 columns, 2nd group of 4 columns, etc.). The number in Column 6 tells you it's the first half or second half of the 4 column group ... e.g., cols A & B or C & D. Combos 1/3 and 2/3 don't fit but combos 3/1 and 3/2 do fit (and 3/1 is in your example text and table.) So, the function is

COL1 = ( (ValueIn#2 - 1) * 4 ) + ( ( ValueIn#6 - 1 ) * 2 ) + 1
COL2 = COL1 + 1

If this is correct. the code below will largely do what you want. The file location and where you write the data are hard coded at the top.

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

'------------------------------------------------------
'name of the text file
strTextFile = "C:\Users\voudoujoe\Downloads\@TEST\TEST.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

'Split by lines, put into an array
ayLines = Split(strData, vbCrLf)

'Split each line into its own array
ReDim ayBody(UBound(ayLines) - 1)
For i = 0 To UBound(ayBody)
    ayBody(i) = Split(ayLines(i), vbTab)
Next i

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

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

    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

 

[mmhill]

I think these statements are errors based on looking at the images of your text file and desired output file.

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.

Sorry - Meant to say that line 2 (not line 1) had an error.

 

[maudedog]

I think there was a couple errors in your original post. If so, I have a workign answer, although it doesn't do an incremental read - it reads the whole file. And, so far, it's limited to about 675 lines in the text file. Given how the picture of the text file looks, I assumed it is tab deliminated.

I think these statements are errors based on looking at the images of your text file and desired output file.



In your example table of output, the #2 = 2 and # 6 =1 maps to columns E and F and 1/3 and 2/3 combinations don't make sense (but 3/1 and 3/2 combinations do make sense).

Looking at the output table, number in column 2 gives you the group of 4 columns to write to (1st group of 4 columns, 2nd group of 4 columns, etc.). The number in Column 6 tells you it's the first half or second half of the 4 column group ... e.g., cols A & B or C & D. Combos 1/3 and 2/3 don't fit but combos 3/1 and 3/2 do fit (and 3/1 is in your example text and table.) So, the function is

COL1 = ( (ValueIn#2 - 1) * 4 ) + ( ( ValueIn#6 - 1 ) * 2 ) + 1
COL2 = COL1 + 1

View attachment 80553

If this is correct. the code below will largely do what you want. The file location and where you write the data are hard coded at the top.

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

'------------------------------------------------------
'name of the text file
strTextFile = "C:\Users\voudoujoe\Downloads\@TEST\TEST.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

'Split by lines, put into an array
ayLines = Split(strData, vbCrLf)

'Split each line into its own array
ReDim ayBody(UBound(ayLines) - 1)
For i = 0 To UBound(ayBody)
    ayBody(i) = Split(ayLines(i), vbTab)
Next i

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

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

    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

mmhill - Awesome - thanks for posting this so quickly. I just tried to run this but I am getting a "subscript out of range error" at:

Any ideas?

 

[jdellasala]

Just uploaded the text file (in Excel format) and the sample worksheet

Can you please add headers to the text file table? Can't tell what column relates to the final output.

 

[jdellasala]

Can you please add headers to the text file table? Can't tell what column relates to the final output.

Give me a URL. What you get varies immensely, but a sample URL is a start.