Answered by:
Export CSV from txt file

Question
-
Hi,
I am working on a script which will read a txt file and convert into a csv file. The csv file is not being generated and the script is not throwing an error either. Can someone help pinpoint the issue. I have also attached the text file, I am reading. I believe the issue is with the last two columns POSLSTTMMIN and POSLSTTMMAX which is in date time format. Please advise.
$inputData = get-content "C:\List1.txt" & { for ( $row = 2; $row -lt $inputData.Count; $row++ ) { if ( $inputData[$row] ) { $inputData[$row] | select-string '^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)' | foreach-object { new-object PSObject -property @{ "PLAZAI" = $_.Matches[0].Groups[1].Value "LANEID" = $_.Matches[0].Groups[2].Value "LANE" = $_.Matches[0].Groups[3].Value "PLLAGMN" = $_.Matches[0].Groups[4].Value "PLLAGMX" = $_.Matches[0].Groups[5].Value "POSLSTTMMIN" = $_.Matches[0].Groups[6].Value "POSLSTTMMAX" = $_.Matches[0].Groups[7].Value } | select-object PLAZAI,LANEID,LANE,PLLAGMN,PLLAGMX,POSLSTTMMIN,POSLSTTMMAX } } } } | export-csv C:\List2.csv -notypeinformation
TXT FILE
Thursday, August 6, 2015 7:53 PM
Answers
-
Try this regex:
'^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d{4}_\d\d:\d\d:\d\d)\s+(\d{4}_\d\d:\d\d:\d\d)'
- Marked as answer by PowerShellNovice Monday, August 10, 2015 10:20 PM
Thursday, August 6, 2015 8:09 PM -
Windows 10:
$pattern='^(?<PLAZAI>\d+)\s+(?<LANEID>\d+)\s+(?<LANE>\d+)\s+(?<PLLAGMN>\d+)\s+(?<PLLAGMX>\d+)\s+(?<POSLSTTMMIN>\d{4}_\d\d:\d\d:\d\d)\s+(?<POSLSTTMMAX>\d{4}_\d\d:\d\d:\d\d)' Get-Content list1.txt |
ConvertFrom-String -ParseExpression $pattern |
Export-Csv list2.csv -NoType
\_(ツ)_/
- Marked as answer by PowerShellNovice Monday, August 10, 2015 10:20 PM
Saturday, August 8, 2015 6:19 AM
All replies
-
Try this regex:
'^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d{4}_\d\d:\d\d:\d\d)\s+(\d{4}_\d\d:\d\d:\d\d)'
- Marked as answer by PowerShellNovice Monday, August 10, 2015 10:20 PM
Thursday, August 6, 2015 8:09 PM -
If you have PowerShell 3.0 or 4.0 you can do this:
Select-String -Path C:\List1.txt '^(?:(\d+)\s+)+(?:(\d{4}_\d\d:\d\d:\d\d)\s*)+' | Foreach { [PSCustomObject]@{ PLAZAI = $_.Matches.Groups[1].Captures[0].Value LANEID = $_.Matches.Groups[1].Captures[1].Value LANE = $_.Matches.Groups[1].Captures[2].Value PLLAGMN = $_.Matches.Groups[1].Captures[3].Value PLLAGMX = $_.Matches.Groups[1].Captures[4].Value POSLSTTMMIN = $_.Matches.Groups[2].Captures[0].Value POSLSTTMMAX = $_.Matches.Groups[2].Captures[1].Value } } | Export-Csv C:\List2.csv -NoTypeInformation
- Edited by Leif-Arne Helland Saturday, August 8, 2015 5:03 AM
Thursday, August 6, 2015 8:52 PM -
And this one works in PowerShell 2.0:
Select-String -Path C:\List1.txt '^(?:(\d+)\s+)+(?:(\d{4}_\d\d:\d\d:\d\d)\s*)+' | Foreach { New-Object PSObject -Property @{ PLAZAI = $_.Matches[0].Groups[1].Captures[0].Value LANEID = $_.Matches[0].Groups[1].Captures[1].Value LANE = $_.Matches[0].Groups[1].Captures[2].Value PLLAGMN = $_.Matches[0].Groups[1].Captures[3].Value PLLAGMX = $_.Matches[0].Groups[1].Captures[4].Value POSLSTTMMIN = $_.Matches[0].Groups[2].Captures[0].Value POSLSTTMMAX = $_.Matches[0].Groups[2].Captures[1].Value } | Select PLAZAI, LANEID, LANE, PLLAGMN, PLLAGMX, POSLSTTMMIN, POSLSTTMMAX } | Export-Csv C:\List2.csv -NoTypeInformation
- Edited by Leif-Arne Helland Saturday, August 8, 2015 7:15 AM
Saturday, August 8, 2015 5:27 AM -
Windows 10:
$pattern='^(?<PLAZAI>\d+)\s+(?<LANEID>\d+)\s+(?<LANE>\d+)\s+(?<PLLAGMN>\d+)\s+(?<PLLAGMX>\d+)\s+(?<POSLSTTMMIN>\d{4}_\d\d:\d\d:\d\d)\s+(?<POSLSTTMMAX>\d{4}_\d\d:\d\d:\d\d)' Get-Content list1.txt |
ConvertFrom-String -ParseExpression $pattern |
Export-Csv list2.csv -NoType
\_(ツ)_/
- Marked as answer by PowerShellNovice Monday, August 10, 2015 10:20 PM
Saturday, August 8, 2015 6:19 AM -
Windows 10:
$pattern='^(?<PLAZAI>\d+)\s+(?<LANEID>\d+)\s+(?<LANE>\d+)\s+(?<PLLAGMN>\d+)\s+(?<PLLAGMX>\d+)\s+(?<POSLSTTMMIN>\d{4}_\d\d:\d\d:\d\d)\s+(?<POSLSTTMMAX>\d{4}_\d\d:\d\d:\d\d)' Get-Content list1.txt |
ConvertFrom-String -ParseExpression $pattern |
Export-Csv list2.csv -NoType
Have you tried it?
I get this:
ConvertFrom-String : A parameter cannot be found that matches parameter name 'ParseExpression'.
Saturday, August 8, 2015 8:46 AM -
We are awaiting the first patch to fix that.
Temporarily you can use this:
https://www.sepago.com/blog/2015/01/16/powershell-convertfrom-string-the-new-way-of-extracting-data
\_(ツ)_/
Saturday, August 8, 2015 8:54 AM -
Here is the help for that:
Example 3: Split a string by using a parse expression
PS C:\>"Hello World" | ConvertFrom-String -ParseExpression "He(ll.*o)r(?<Captured>ld)"
The following example generates an object with "P1=llo Wo" and "Captured=ld". There is no property name specified for the first property, so Windows PowerShell applies the
default property name, P1; but the name Captured is specified in the expression for the second property.\_(ツ)_/
Saturday, August 8, 2015 9:01 AM -
Thank you this worked great!Monday, August 10, 2015 10:21 PM