I was given a text file that had a format like similar the following...
==============================================================
SHEET: SheetTitle1
==============================================================HeaderTitle1 | HeaderTitle2Row1Value1 | Row1Value2Row2Value1 | Row2Value2...==============================================================SHEET: SheetTitle2==============================================================HeaderTitle1 | HeaderTitle2Row1Value1 | Row1Value2Row2Value1 | Row2Value2...==============================================================SHEET: SheetTitle3==============================================================HeaderTitle1 | HeaderTitle2Row1Value1 | Row1Value2Row2Value1 | Row2Value2......
I found that I could do several different manual processes to put this all into a multi-tabbed Excel workbook doing something similar to the following:
-Open your text file.
-Open Excel.
-For each Sheet: label, make a worksheet in Excel using the SheetTitle as the worksheet name.
-From the text file select all the rows in the current sheet section and paste them into A1.
-Select column A.
-Go to the Data menu > click on Text to Columns > Delimited > Other: as "|" > Next > Finish
This should move all your data in to properly labeled columns
-Make a new sheet and title it as the next section.
-From the text file select all the data rows of the section.
-In cell A1 on the new worksheet, paste.
Please notice that Excel may remember what you did on your first sheet and automatically properly delimit the data and make proper columns for your rows.
if it doesn't, then you can just go select col A > Data menu > Text to Columns again.
The final workbook would look like this...
As I was sent a source with more than 5 Sheets and hundreds of rows, that may have taken a bit to do. And, since data entry will put me to sleep, I decided to do it with code. I created a repo on GitHub at https://github.com/LesleyPhillips/ReportToExcelWorkbook
There you can get the source Python and Powershell. the Input.txt, logs and Excel workbooks from the runs.
Here is the console of the runs, the logs contain the same.
A request to anyone seeing this:
While I appreciate the neatness of this format, I don't know of any program that would generate this input.txt as a standard format. If you know it, please reach out and let me know. : )
