1st column = String that starts after T= and ends before “"
2nd column = Date that is starting after "” in the first string
3rd column = String that starts after R=.
Rest everything has to be ignored and the output should be written to a new file. Output will be like
CEMTDR1864393 10/09/2017 TDRALRT01
000284447 10/09/2017 ITNEALRT01
Please let me know how this can be acheived using powershell. I am new to it and do not have much information
Hi, this forum is not about writing scripts for you. It’s more about to answer questions about Powershell scripting. Do you have any specific question about a particular issue you have with Powershell scripting?
In PowerShell v5 there is a command ConvertFrom-String which can parse text files based off a template you create. A very good reason to upgrade to v5.
Thank you John. I see you are hard-coding the values. I just provided a sample and number of records vary from 500 - 1000 or even more. I just want to extract 3 columns as mentioned in my initial post
1st column = String that starts after T= and ends before “"
2nd column = Date that is starting after "” in the first string
3rd column = String that starts after R=
It should work for 1 or more lines. What I have hard coded was just the template to apply to your text file. Please see the results I posted in a separate post.
Hi John,
probably you replied before reading my post.your script worked and it is what i exactly wanted. Thank you so much again. however, i have 2 more questions
gc .\x.txt : how can i specify absolute file path? though it worked in my case.
is it possible to supress the headers? i redirected the output and there were trailing spaces along with headers which i want to get rid off
Just specify the path you want after the GC (get-content) command Get-Content C:\somedir\someotherdir\somefile.txt
use format table with hide headers option.
gc .\x.TXT | cfs -TemplateContent $template | ft First, Date, Third -HideTableHeaders
But you probably don’t want to use Format-Table if you wanted to do something with these items. Probably want to pipe to some sort of output file (out-file, export-csv, or do something with the individual items)
@'# this hides the headers '@
gc .\x.TXT | cfs -TemplateContent $template | ft First, Date, Third -HideTableHeaders
@'# do something with a value -- replace Write-Output with your code/command '@
gc .\x.TXT | cfs -TemplateContent $template | % {$_.First | Write-Output }
@'# Create a CSV file to import somewhere else '@
gc .\x.TXT | cfs -TemplateContent $template | export-csv -Path .\x.csv
Thank you again John. however when i redirect the output using “> c:\results.txt” first line is empty and at the end of the file 3 lines are empty. is it possible to get rid of those blank lines? I mean can we trim those lines and have only required output? I willbe using this output to insert into an oracle table and it will be easy when there are no trailing lines and spaces
Not sure about the whitespace issues…while you can redirect with > PowerShell has a bunch of different ways to write to a file. Maybe use Out-File ? But my first recommendation is to use Export-CSV as shown above.
Thanks John. when i use “.csv” prefixed zeroes will be truncated and only non numeric values are retained. for ex,000052520 will be 52520. However I am good with the output. Thank you so much for your time and helping me out. I spent a lot of time on this but in vain. you were my saviour.