Read text file into multidim array or search text file and return corresponding value

Hello,

I have a list of data contained in a column in a sheet. I want to look at or in a text file for a match, then read a corresponding item from the row of data. My goal is the derive the code to do this with one file then apply it to several values that will be extracted from several different text files. The text files will likely have 300,000 rows and 10-15 columns of data each.

I understand I could load each file into a separate sheet then use vlookup to extract the data. This seems extremely cumbersome given the size of the text files. The original data set (column) comes from a complex search of another text file that I have loaded into the spreadsheet already. I hope it can use the new method and reside outside the spreadsheet.

The column or parent info might be 500 items and thinking about a good method to: 1) read and split the child info into an array, then search the array for the answer. 2) search the text file for the value then read in the corresponding items.

ex:
this would be the parent information.
part
001-25895-00
002-1259-00
ps-25436
abc-125



Child: Note there maybe repeats that have different data. this data is contained in a text file. Info.txt

part desc location qty
5948 name3 g-12 5
001-25895-00 name1 a-1 1
002-1259-00 name2 a-11 1
05948 name3 b-2 5
abc-12356 name6 C-5 2
abc-12356 name6 D-9 1
abc-125 name5 a-2 5
abc-125 name5 q-12 6
ps-25436 name4 a-2 6
xcv-598 name10 a-3 25
001-2596-00 name11 a-3-1 1
002289 name12 a-6 0



MY output would be for part, desc & location - This output would be reported on the sheet.

part desc loc
001-25895-00 name1 a-1
002-1259-00 name2 a-11
abc-125 name5 a-2
abc-125 name5 q-12
ps-25436 name4 a-2


I hope this is a good explanation. I want to thank everyone before hand for your assistance. I am somewhat of a beginner at this so please excuse my knowledge of arrays and collections. Don't seem to be able to wrap my brain around these.

Thank you again.

The best way to do this is to use the power query data functions of Excel. An approach might be as follows:

In the spreadsheet where you want the combined information (this could be where you have the parent column or a link to the original text file).

On the menu choose Data Get Data and choose the appropriate source. This will create a data table.

Do the same for the child text files. On these when it comes time to load it, choose Load To and then create a connection only.

Finally merge the two queries to create the end table you are looking for.

Excel provides a nice interface that helps you do this. I have attached a workbook where I have done this but instead of connecting to other text files I have just put the data in different tabs so I can demonstrate in one file. It works the same with text files. You can see the queries that link it all together by going to the Data tab then choosing Queries & Connections. That will show you the tables I used.