vba extract data from text file

[drop05]

Hello, I am wondering if there is a way to select a text file and loop through the text file line by line.
each line is similar to this structure

"FORM 1","1","0","0","NAME","Diego","O"
"FORM 1","2","0","0","NAME","Pedro","O"
etc. . .

and put it into a dictionary of sorts to store the information collected from the text file and then put it into a table on a sheet in the workbook. There is 8 areas that are separated by the comma, so area 1 = form 1, area 2 is the 1 and 2, etc.

Hopefully I am thinking correctly of the process

 

[igold]

This would be a very basic example of what I think you are trying accomplish. You will have to change the code to place the text where you want it. I took your example text and expanded a couple of lines so that it looks like this and is named "drop05.txt". The path in the code is where is resides on my computer and you will have to change to meet your requirements.

"FORM 1","1","0","0","NAME","Diego","O"
"FORM 1","2","0","0","NAME","Pedro","O"
"FORM 1","3","0","0","NAME","Juan","O"
"FORM 1","4","0","0","NAME","Jose","O"

This is the code:

Sub TextFlie()

    Dim row_number As Single
    Dim LineFromFile As Variant
    Dim col_number As Single
    Dim FilePath As String
    Dim i As Long
    
    FilePath = "G:\Excel VBA\drop05.txt"    'Change path here"
    Close #1
    Open FilePath For Input As #1
    row_number = 0
    col_number = 0
    Range("a1").Select

    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ",")
        For i = 0 To UBound(LineItems)
            ActiveCell.Offset(row_number, col_number + i).Value = LineItems(i)
        Next
        row_number = row_number + 1
    Loop

    Close #1

End Sub

And my worksheet looks like this after the code has run:

Text from test.txt file.xlsm
	A	B	C	D	E	F	G	H
1	"FORM 1"	"1"	"0"	"0"	"NAME"	"Diego"	"O"
2	"FORM 1"	"2"	"0"	"0"	"NAME"	"Pedro"	"O"
3	"FORM 1"	"3"	"0"	"0"	"NAME"	"Juan"	"O"
4	"FORM 1"	"4"	"0"	"0"	"NAME"	"Jose"	"O"
5
Sheet7

 

[drop05]

This would be a very basic example of what I think you are trying accomplish. You will have to change the code to place the text where you want it. I took your example text and expanded a couple of lines so that it looks like this and is named "drop05.txt". The path in the code is where is resides on my computer and you will have to change to meet your requirements.

"FORM 1","1","0","0","NAME","Diego","O"
"FORM 1","2","0","0","NAME","Pedro","O"
"FORM 1","3","0","0","NAME","Juan","O"
"FORM 1","4","0","0","NAME","Jose","O"

This is the code:

Sub TextFlie()

    Dim row_number As Single
    Dim LineFromFile As Variant
    Dim col_number As Single
    Dim FilePath As String
    Dim i As Long
   
    FilePath = "G:\Excel VBA\drop05.txt"    'Change path here"
    Close #1
    Open FilePath For Input As #1
    row_number = 0
    col_number = 0
    Range("a1").Select

    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ",")
        For i = 0 To UBound(LineItems)
            ActiveCell.Offset(row_number, col_number + i).Value = LineItems(i)
        Next
        row_number = row_number + 1
    Loop

    Close #1

End Sub

And my worksheet looks like this after the code has run:

Text from test.txt file.xlsm
	A	B	C	D	E	F	G	H
1	"FORM 1"	"1"	"0"	"0"	"NAME"	"Diego"	"O"
2	"FORM 1"	"2"	"0"	"0"	"NAME"	"Pedro"	"O"
3	"FORM 1"	"3"	"0"	"0"	"NAME"	"Juan"	"O"
4	"FORM 1"	"4"	"0"	"0"	"NAME"	"Jose"	"O"
5
Sheet7

Yes this does help a lot, gives me an idea and the code makes sense
Questions,

what if in the table i have key words that are located within the txt file. So like some lines may not contain it but some will
so say the table i have has the key words in column A2 and down column A.
So for example
"FORM 1"
"FORM 2"
"FORM 3"
are three key words id like to find within the text file and once it finds it in the text file then it grabs the values to the right of it so the
1","0","0","NAME","Diego","O"
2","0","0","NAME","Pedro","O"
3","0","0","NAME","Juan","O"
4","0","0","NAME","Jose,"O"

 

[igold]

Can you show me an example of what the text file might look like and also an example of what the worksheet would look like before and after. If you could use XL2BB that would be great. It is really a matter of inserting code that will look for your keywords and then having the text moved around. If I could see it I could better try to help.

 

[drop05]

Can you show me an example of what the text file might look like and also an example of what the worksheet would look like before and after. If you could use XL2BB that would be great. It is really a matter of inserting code that will look for your keywords and then having the text moved around. If I could see it I could better try to help.

yes give me a moment and i can upload an example, i appreciate you!

 

[igold]

It is getting late here on the East Coast of the US (around midnight), so take your time. I probably will not get to it until tomorrow morning...

 

[drop05]

It is getting late here on the East Coast of the US (around midnight), so take your time. I probably will not get to it until tomorrow morning...

I am not too familiar with using the xl2bb but think i did it right. I am not sure how to include a txt file but at the bottom of the tables i have inlcuded an example of what is inside the txt file and also a screen shot of how it looks since the excel file will have text to column
But over night i thought maybe use a table to have the form and key words looking for and boolval if it is true then those are the ones where id like the values for labeled "Section", "Group", "Subgroup" and "Value" as well as the form and key names from the txt file and fill out the table, and the table has the values from the text file how i am trying to get it to look like so you will see that all the values from the txt file with the boolval being true are in the second table on the right
anything that is false can be ignored. and the txt file can be selected by a user since i will have multiple file paths

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Form2Find	Key2Find	Sheet	BoolVal		Form	Key	Section	Group	Subgroup	Value	Sheet
2	FORM 1	FIRST NAME	Sheet A	TRUE		FORM 1	FIRST NAME	1	1	0	Jose
3	FORM 1	LAST NAME	Sheet A	TRUE		FORM 1	FIRST NAME	1	2	0	Joseph
4	FORM 1	MIDDLE NAME	Sheet A	TRUE		FORM 1	FIRST NAME	1	3	0	Pedro
5	FORM 1	GRADE LEVEL	Sheet A	FALSE		FORM 1	FIRST NAME	2	1	0	Christian
6	FORM B	BEG GRADE 1	Sheet B	TRUE		FORM 1	FIRST NAME	2	2	0	Pablo
7	FORM B	END GRADE 1	Sheet B	FALSE		FORM 1	FIRST NAME	2	3	0	Tristan
8	FORM B	END GRADE NAME	Sheet B	TRUE		FORM 1	FIRST NAME	3	1	0	Chris
9						FORM 1	FIRST NAME	3	2	0	Preslee
10						FORM 1	FIRST NAME	3	3	0	Tyler
11						FORM 1	LAST NAME	1	1	0	Garcia
12						FORM 1	LAST NAME	1	2	0	Guerra
13						FORM 1	LAST NAME	1	3	0	Gonzales
14						FORM 1	LAST NAME	2	1	0	Garza
15						FORM 1	LAST NAME	2	2	0	Houston
16						FORM 1	LAST NAME	2	3	0	Brady
17						FORM 1	LAST NAME	3	1	0	Garcia
18						FORM 1	LAST NAME	3	2	0	Preslee
19						FORM 1	LAST NAME	3	3	0	Hal
20						FORM B	BEG GRADE 1	1	1	0	90
21						FORM B	BEG GRADE 1	1	2	0	100
22						FORM B	BEG GRADE 1	1	3	0	60
23						FORM B	BEG GRADE 1	2	1	0	73
24						FORM B	BEG GRADE 1	2	2	0	88
25						FORM B	BEG GRADE 1	2	3	0	55
26						FORM B	BEG GRADE 1	3	1	0	77
27						FORM B	BEG GRADE 1	3	2	0	75
28						FORM B	BEG GRADE 1	3	3	0	81
29						FORM B	END GRADE NAME	1	1	0	Jose
30						FORM B	END GRADE NAME	1	2	0	Joseph
31						FORM B	END GRADE NAME	1	3	0	Pedro
32						FORM B	END GRADE NAME	2	1	0	Christian
33						FORM B	END GRADE NAME	2	2	0	Pablo
34						FORM B	END GRADE NAME	2	3	0	Tristian
35						FORM B	END GRADE NAME	3	1	0	Chris
36						FORM B	END GRADE NAME	3	2	0	Preslee
37						FORM B	END GRADE NAME	3	3	0	Tyler
38
39
40	FORM 1	1	1	0	FIRST NAME	Jose	O
41	FORM 1	1	2	0	FIRST NAME	Joseph	O
42	FORM 1	1	3	0	FIRST NAME	Pedro	O
43	FORM 1	2	1	0	FIRST NAME	Christian	O
44	FORM 1	2	2	0	FIRST NAME	Pablo	O
45	FORM 1	2	3	0	FIRST NAME	Tristan	O
46	FORM 1	3	1	0	FIRST NAME	Chris	O
47	FORM 1	3	2	0	FIRST NAME	Preslee	O
48	FORM 1	3	3	0	FIRST NAME	Tyler	O
49	FORM 1	1	1	0	LAST NAME	Garcia	O
50	FORM 1	1	2	0	LAST NAME	Guerra	O
51	FORM 1	1	3	0	LAST NAME	Gonzales	O
52	FORM 1	2	1	0	LAST NAME	Garza	O
53	FORM 1	2	2	0	LAST NAME	Houston	O
54	FORM 1	2	3	0	LAST NAME	Brady	O
55	FORM 1	3	1	0	LAST NAME	Garcia	O
56	FORM 1	3	2	0	LAST NAME	Preslee	O
57	FORM 1	3	3	0	LAST NAME	Hal	O
58	FORM 1	1	1	0	GRADE LEVEL	1	O
59	FORM 1	1	2	0	GRADE LEVEL	1	O
60	FORM 1	1	3	0	GRADE LEVEL	2	O
61	FORM 1	2	1	0	GRADE LEVEL	3	O
62	FORM 1	2	2	0	GRADE LEVEL	3	O
63	FORM 1	2	3	0	GRADE LEVEL	1	O
64	FORM 1	3	1	0	GRADE LEVEL	4	O
65	FORM 1	3	2	0	GRADE LEVEL	5	O
66	FORM 1	3	3	0	GRADE LEVEL	3	O
67	FORM B	1	1	0	BEG GRADE 1	90	O
68	FORM B	1	2	0	BEG GRADE 1	100	O
69	FORM B	1	3	0	BEG GRADE 1	60	O
70	FORM B	2	1	0	BEG GRADE 1	73	O
71	FORM B	2	2	0	BEG GRADE 1	88	O
72	FORM B	2	3	0	BEG GRADE 1	55	O
73	FORM B	3	1	0	BEG GRADE 1	77	O
74	FORM B	3	2	0	BEG GRADE 1	75	O
75	FORM B	3	3	0	BEG GRADE 1	81	O
76	FORM B	1	1	0	END GRADE 1	90	O
77	FORM B	1	2	0	END GRADE 1	95	O
78	FORM B	1	3	0	END GRADE 1	91	O
79	FORM B	2	1	0	END GRADE 11	79	O
80	FORM B	2	2	0	END GRADE 1	70	O
81	FORM B	2	3	0	END GRADE 1	90	O
82	FORM B	3	1	0	END GRADE 1	80	O
83	FORM B	3	2	0	END GRADE 1	85	O
84	FORM B	3	3	0	END GRADE 1	75	O
85	FORM B	1	1	0	END GRADE NAME	Jose	O
86	FORM B	1	2	0	END GRADE NAME	Joseph	O
87	FORM B	1	3	0	END GRADE NAME	Pedro	O
88	FORM B	2	1	0	END GRADE NAME	Christian	O
89	FORM B	2	2	0	END GRADE NAME	Pablo	O
90	FORM B	2	3	0	END GRADE NAME	Tristian	O
91	FORM B	3	1	0	END GRADE NAME	Chris	O
92	FORM B	3	2	0	END GRADE NAME	Preslee	O
93	FORM B	3	3	0	END GRADE NAME	Tyler	O
94
Sheet1

i thought id just paste what was in the txt file the header is not included in the txt file just showing what it aligns to for the table, each section has a group and each group can have a subgroup but most for now are 0 but some can be 1,2,3, etc

Form, Section, Group, SubGroup, Key, Value

"FORM 1","1","1","0","FIRST NAME","Jose","O"
"FORM 1","1","2","0","FIRST NAME","Joseph","O"
"FORM 1","1","3","0","FIRST NAME","Pedro","O"
"FORM 1","2","1","0","FIRST NAME","Christian","O"
"FORM 1","2","2","0","FIRST NAME","Pablo","O"
"FORM 1","2","3","0","FIRST NAME","Tristan","O"
"FORM 1","3","1","0","FIRST NAME","Chris","O"
"FORM 1","3","2","0","FIRST NAME","Preslee","O"
"FORM 1","3","3","0","FIRST NAME","Tyler","O"
"FORM 1","1","1","0","LAST NAME","Garcia","O"
"FORM 1","1","2","0","LAST NAME","Guerra","O"
"FORM 1","1","3","0","LAST NAME","Gonzales","O"
"FORM 1","2","1","0","LAST NAME","Garza","O"
"FORM 1","2","2","0","LAST NAME","Houston","O"
"FORM 1","2","3","0","LAST NAME","Brady","O"
"FORM 1","3","1","0","LAST NAME","Garcia","O"
"FORM 1","3","2","0","LAST NAME","Preslee","O"
"FORM 1","3","3","0","LAST NAME","Hal","O"
"FORM 1","1","1","0","GRADE LEVEL","1","O"
"FORM 1","1","2","0","GRADE LEVEL","1","O"
"FORM 1","1","3","0","GRADE LEVEL","2","O"
"FORM 1","2","1","0","GRADE LEVEL","3","O"
"FORM 1","2","2","0","GRADE LEVEL","3","O"
"FORM 1","2","3","0","GRADE LEVEL","1","O"
"FORM 1","3","1","0","GRADE LEVEL","4","O"
"FORM 1","3","2","0","GRADE LEVEL","5","O"
"FORM 1","3","3","0","GRADE LEVEL","3","O"
"FORM B","1","1","0","BEG GRADE 1","90","O"
"FORM B","1","2","0","BEG GRADE 1","100","O"
"FORM B","1","3","0","BEG GRADE 1","60","O"
"FORM B","2","1","0","BEG GRADE 1","73","O"
"FORM B","2","2","0","BEG GRADE 1","88","O"
"FORM B","2","3","0","BEG GRADE 1","55","O"
"FORM B","3","1","0","BEG GRADE 1","77","O"
"FORM B","3","2","0","BEG GRADE 1","75","O"
"FORM B","3","3","0","BEG GRADE 1","81","O"
"FORM B","1","1","0","END GRADE 1","90","O"
"FORM B","1","2","0","END GRADE 1","95","O"
"FORM B","1","3","0","END GRADE 1","91","O"
"FORM B","2","1","0","END GRADE 11","79","O"
"FORM B","2","2","0","END GRADE 1","70","O"
"FORM B","2","3","0","END GRADE 1","90","O"
"FORM B","3","1","0","END GRADE 1","80","O"
"FORM B","3","2","0","END GRADE 1","85","O"
"FORM B","3","3","0","END GRADE 1","75","O"
"FORM B","1","1","0","END GRADE NAME","Jose","O"
"FORM B","1","2","0","END GRADE NAME","Joseph","O"
"FORM B","1","3","0","END GRADE NAME","Pedro","O"
"FORM B","2","1","0","END GRADE NAME","Christian","O"
"FORM B","2","2","0","END GRADE NAME","Pablo","O"
"FORM B","2","3","0","END GRADE NAME","Tristian","O"
"FORM B","3","1","0","END GRADE NAME","Chris","O"
"FORM B","3","2","0","END GRADE NAME","Preslee","O"
"FORM B","3","3","0","END GRADE NAME","Tyler","O"

Hopefully i provided the information correctly and I do appreciate the help! Thank you soooo much

 

[igold]

This is a first try. I am not sure if wanted your sheet to look exactly as you posted or if that was just more info for your requirements. This code will start writing to Cell A2. I am assuming you have the Header row already written. If not we can add a line of code to write it. Also, I was not sure what you wanted in the "Sheet" Column, right now it is showing "O" taken from the text file- Did you want that to show either an "A" or "B" according to your key. Sorry about the confusion.

Sub TextFlie()

    Dim row_number As Long, col_number As Long, i As Long
    Dim LineFromFile As Variant
    Dim FilePath As String
    
    FilePath = "G:\Excel VBA\drop05.txt"    'Change path here"
    Close #1
    Open FilePath For Input As #1
    row_number = 1
    col_number = 0
    Range("a1").Select

    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ",")
        For i = 0 To UBound(LineItems)
            If Replace(LineItems(i), """", "") = "GRADE LEVEL" Or Replace(LineItems(i), """", "") = "END GRADE 1" Then
                ActiveCell.Offset(row_number, col_number + i).Value = ""
            Else
                ActiveCell.Offset(row_number, col_number + i).Value = Replace(LineItems(i), """", "")
            End If
        Next
        row_number = row_number + 1
    Loop

    Close #1
    
    Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

 

[drop05]

This is a first try. I am not sure if wanted your sheet to look exactly as you posted or if that was just more info for your requirements. This code will start writing to Cell A2. I am assuming you have the Header row already written. If not we can add a line of code to write it. Also, I was not sure what you wanted in the "Sheet" Column, right now it is showing "O" taken from the text file- Did you want that to show either an "A" or "B" according to your key. Sorry about the confusion.

Sub TextFlie()

    Dim row_number As Long, col_number As Long, i As Long
    Dim LineFromFile As Variant
    Dim FilePath As String
   
    FilePath = "G:\Excel VBA\drop05.txt"    'Change path here"
    Close #1
    Open FilePath For Input As #1
    row_number = 1
    col_number = 0
    Range("a1").Select

    Do Until EOF(1)
        Line Input #1, LineFromFile
        Dim LineItems As Variant: LineItems = Split(LineFromFile, ",")
        For i = 0 To UBound(LineItems)
            If Replace(LineItems(i), """", "") = "GRADE LEVEL" Or Replace(LineItems(i), """", "") = "END GRADE 1" Then
                ActiveCell.Offset(row_number, col_number + i).Value = ""
            Else
                ActiveCell.Offset(row_number, col_number + i).Value = Replace(LineItems(i), """", "")
            End If
        Next
        row_number = row_number + 1
    Loop

    Close #1
   
    Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Sorry i have not tried it had something come up emergancy, but id like it to look like the right table and using the table on the left to use column A and B for helping to find those values in the text file rather than just doing "" in the code because I would add more, but only those that if column D the boolean is true
Then once finding them in the text file yeah to look like the table on the right with grabbing the form, section, group, subgroup, key, value; but the order is how the headers are in the table: form, key, group, subgroup, value, not sheet that i have a formula. The tables are name tables with the table on the left being table1 and the one on the right is table2

this is how it is in the text file a little different positioning wise from what id like to try and get into the table

Form, Section, Group, SubGroup, Key, Value

"FORM 1","1","1","0","FIRST NAME","Jose","O"

 

[igold]

Are you saying that want to keep the table on the left and then the user would change the various boolean values and the extracted text would change accordingly. If that is the case, where do you want the text extracted to. The same sheet and position that it is now or on a new sheet. Also do you want the extracted text as a range of data or in a table. If you want a table, do you have the name of the table.

rather than just doing "" in the code

I am not sure what the above means. Did you try the code, all blanks are removed.