I am working to import and parse multiple text files into a single worksheet, using VBA. I have not found code that can readily be adapted, and I very much appreciate any guidance you can share.
Simplified version:
- Three text files (#101.txt #102.txt, and #103.txt) are to be imported into Sheet1.
- Each file contains three sets of data. Each data set will always have at least three rows, and at most four rows. It is the data in the fourth row of each data set (or absence of this data) that is key. The overall objective is to organize and parse data from the fourth row of each data set (if it exists), so that they can be compared. There is no way to predict when there will or will not be a fourth row – some data sets have three rows and unpredictably, some have four.
- In the example below, for file #101, the second data set (for “ITEM-b”) has only three rows; while the first and third data sets (for “ITEM-a” and “ITEM-c”) contain four rows of data.
File #101
(#101)-ITEM-a
-abc ITEM-a defg hij
(#101) [ignore this row]
text_#101a
(#101)-ITEM-b
-abc ITEM-b defg hij
(#101) [ignore this row]
(#101)-ITEM-c
-abc ITEM-c defg hij
(#101) [ignore this row]
text_#101c
- Highlighted below is the first of three data sets in file #101.txt. The VBA code should:
- Lock in on the “Item-*” in the first row of each data set,
- Look for the corresponding row based on the list of items in column A of Sheet1 (see attached file "FileWrangler.xlsx"),
- And then copy the fourth row of the data set (if it exists), and paste it into the corresponding cell for that file (column) and that Item (row) in Sheet1.
(#101)-ITEM-a
-abc ITEM-a defg hij
(#101) [ignore this row]
text_#101a
- If a data set does not have a fourth row, then the corresponding cell in Sheet1 is left blank.
- As mentioned, the first row of each data set is used to identify the "Item", and the fourth row (if it exists) is what is copied into Sheet1. Rows 2 and 3 always exist, and can always be essentially ignored.
- Below is the desired final state of Sheet1, based on the contents of all three text files. In this example, only file #102 has all four rows for each of the three data sets.
Sheet1 Final State
I appreciate any guidance you can share. Attached are the three text files, and document "FileWrangler.xlsx", showing the desired results on Sheet1.