none
Export CSV from txt file RRS feed

  • 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)'

    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


    \_(ツ)_/

    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)'

    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

    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


    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


    \_(ツ)_/

    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