I need to automate a separation and transfer process from text files to an excel sheet. I am familiar with running automation scripts with Powershell ISE which I think would be the best solution because I use powershell frequently and I would use this script frequently. I'm not a programmer. If you respond to this question then do not expect that I could learn programming because I will not be able to do this. The best I could do is simply use the script and add paths for it to work.
I have a lot of text files that contains text strings that are ordered as shown in the zip file example which you can download from the dropbox link at the foot of this message. You will see that the format takes this structure where the <CRLF> is a line break.
I have many text files like this with records all ordered in this way. These files contain the original (Before) text string and then on the next line a new (After) text string, then a space separating the text string pair then another text string pair all the way down the text document.
What I want to do is automate the separation and transfer of the text strings in these text files to an excel sheet such that the "before" path is in column 1 and the "after" path, for the corresponding text string pair, is in column 2 of the excel sheet for all entries in the text file.
The "after" text strings may be completely different from the "before" text strings or they may be the same. In addition the text strings may have different text string before and after and possibly different file extensions for the text strings. Therefore the matching criteria for separation and transfer would be the <CRLR>. The best way to view the <CRLR> characters we have inserted in the attached text file is to open the text file in NotePad++ then go to View/Show Symbol and tick the option called "Show End Of Line" . This will display all the end of line characters. You will see that all strings follow the same ordered pattern.
The text files all have exactly the same order. i.e. a text "before" text string is terminated by a <CRLR> the "after" text string is terminated by a <CRLR> then a single <CRLR> on the next line to separate the text string pair from the next text string pair and then the next text string pair is repeated. This continues all the way down to the end of the text file and is terminated by a single <CRLR> after the last text string.
As an example I have provided a dropbox download link for a zip file containing an example of both the text file and the excel file output that would be generated by the script.
I have only included 3 string pairs (that is 6 strings) only, however our text files could have several thousand pairs for separation.
What we would like is for the script to be able to process all text files in a folder and subfolders. We would specify the path to the path to be processed in the powershell script and it would then process all folders and subfolders. This would save us time having to run individual processes for each text file. It would be good if the output excel sheet could be placed in the same folder as the text file it was created from and name by the same name as the original text file.
I have windows 7 computer with powershell 3 ISE installed. I look forward to seeing what you can come up with to solve this issue.
Download link for sample files:
Thank you for your interest and taking the time to read this.