Powershell Script to separate text strings in a text file & transfer to Excel

Dear Experts,
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 is a line break.

TextStringPathForFile1Before.lbi
NewTextStringForFile1After.lbi

TextStringPathForFile2Before.lbi
NewTextStringForFile2Afte.lbir

TextStringPathForFile3Before.lbi
NewTextStringForFile3After.lbi


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 . The best way to view the 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 the "after" text string is terminated by a then a single 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 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.

Download link
https://www.dropbox.com/s/9zmfgnyny9yg9cp/TextStringSepartor.zip?dl=0

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:https://www.dropbox.com/s/9zmfgnyny9yg9cp/TextStringSepartor.zip?dl=0


Thank you for your interest and taking the time to read this.

best regards

John



I've hacked something together. The way it works is to assume based off your example file that the file will always have a before and after combo 1 line after each other and nothing else (no comments, headers, etc). The script imports the contents of the file stripping out empty lines and then iterates through the file checking whether the line is odd or even.

Array item 0 = even (as far as mod is concerned) .. becomes a before
Array item 1 = odd .. becomes an after
Array item 2 = even .. becomes an before
Array item 3 = odd .. becomes an after
And so on.

Results are exported to a CSV which can be opened in Excel.



I've hacked something together. The way it works is to assume based off your example file that the file will always have a before and after combo 1 line after each other and nothing else (no comments, headers, etc). The script imports the contents of the file stripping out empty lines and then iterates through the file checking whether the line is odd or even.

Array item 0 = even (as far as mod is concerned) .. becomes a before
Array item 1 = odd .. becomes an after
Array item 2 = even .. becomes an before
Array item 3 = odd .. becomes an after
And so on.

Results are exported to a CSV which can be opened in Excel.



Hello Learnctx,
thank you for your reply. Yes you are correct there are no comments, no headers and no footers in the text files to be processed. There are just the 2 string pairs (before and after) and a single carriage return between the pairs as shown in the sample.


I hope you can give me some assistance on running your script. You have used some technical terms which I do not properly undestand since I have no experience or knowledge of programming. Normally when I run a powershell script that has been written form me I run it from Powershell ISE. When I open the script from within the ISE there is normally a line for me to enter the path to the parent directory that contains the folders and subfolders to be processed. Then an output path which would mirror the folder structure of the input path and output the processed files. I normally just add those 2 paths and then click the RUN button. Can I run your script in the same way? If not where do I input the paths in your script? Thank you for writing this I think you've come up a ingenious solution by using odd and even values for the logic of the script.

Best regards

John

I don't know how to run a powershell script from a command line.



When you execute the script it will prompt you to enter the path to a file. There was an error above I fixed and I've included an alternative where you can edit the file paths in ISE.



Hello, I tried running the script. It ran however I received these errors which seem to be for every record. When I opened the CSV file the is only 2 words "before" and "after" Any ideas what is happening?


Cannot index into a null array.
At C:WorkSCMDevScriptsPowershellScriptsDicFileToCSV.ps1:13 char:3
+ $ObjValues.Before=$a[$_]
+ ~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray

Cannot index into a null array.
At C:WorkSCMDevScriptsPowershellScriptsDicFileToCSV.ps1:14 char:3
+ $objValues.After=$a[$_+1]
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray



I do indeed. I forgot to replace $a with $contents. Modified below.

Share this

Related Posts

There was an error in this gadget