Combine Multiple Text Files Into One XLSX

We are continuing our new series that answers common questions, or questions that have been asked about PowerShell. In this post we talk about how to combine multiple text files into one xlsx file. What we intend to provide is one of the many, many ways you can accomplish these tasks within PowerShell.

All of the solutions are ours and demonstrate the author’s skill and ability level at the time of writing. That is to say, we might not always write the best PowerShell code, however, if you know of a better way we welcome that input.

The Question:

I’m pretty new to PS and been struggling for a few days. I have multiple text files in a folder with specific data that I would like to extract into an excel spreadsheet. Each file looks like this:

Client n° : xxx Client name : xxx

Computer status

pc group 1 :

no1 OK                no2 Disconnected    no3 Unresponsive

no4 Unreachable host                      no5 Unresponsive

Data read 11/11/20 12:50:07 

Version: x.x.x

I would like to have an output file that looks like this :

Client name /no OK Disconnected Unresponsive Unreachable version
xxx/xxx       1      1            2               1     x.x.x

Let’s get to answering this question.

Combine Multiple Text Files Into One XLSX

I created three of the same files, with different data, and tested this script. As long as they are text files in the directory the script will iterate through each file and pull the data from each as it needs to be.

NOTE: If a stray text file gets added the script does not know nor care and will treat it like the others. If there is data it can find it will, and it will output that data to the excel file. Lastly the file is set to save itself and then immediately close.

It starts by Creating the Excel file, then Workbook. (I commented out the naming of the workbook. If you like you can add it back.) Finds all text files in a directory, then searches the text for the specific content within the format specified above.

During the script I commented as much as I thought might be needed to assist with modification later on.