locked
Export CSV from txt file RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you this worked great!
    Monday, August 10, 2015 10:21 PM