locked
Converting csv from a txt file RRS feed

  • Question

  • Hi,

    There is a txt file, I have updated which I need to convert to a csv file. The txt file looks something like this.

    Count,Test                                                           Number    
    ----------------------------------------------------------------- ----------    
    46,003201                                                                  3    
    14,008131                                                                  2    
    8,003150                                                                   1    

    I am running this piece of script 

    Import-Csv C:\VPS\newfile.txt |
    Where-Object {$_.Count -notmatch '----'} |
    export-csv C:\VPS\newfile3.csv -notype


    However the output does not seem to work. This is what I get.There are spaces as you see below.

    "Count","Test                                                                                          Number    "

    "46","003201                                                                  3    "
    "14","008131                                                                  2    "
    "8","003150                                                                   1    "

    I am trying to create a csv file with the correct format. This is what I am expecting.

    "Count","Test ",  "Number"

    "46","003201" ,"3"
    "14","008131","2"
    "8","003150","1"

    Please assist.

    Thursday, June 5, 2014 3:02 PM

Answers

  • Sorry, forgot for doesn't generate output. We need to put it in a scriptblock and then output it:


    $inputData = get-content "inputdata.txt"
    & {
      for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
        if ( $inputData[$row] ) {
          $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
            new-object PSObject -property @{
              "Count" = $_.Matches[0].Groups[1].Value
              "Test" = $_.Matches[0].Groups[2].Value
              "Number" = $_.Matches[0].Groups[3].Value
            } | select-object Count,Test,Number
          }
        }
      }
    } | export-csv "outputdata.csv" -notypeinformation
    

    This version also skips blank lines in the input, provided the blank lines are past the third line.

    -- Bill Stewart [Bill_Stewart]


    • Edited by Bill_Stewart Thursday, June 5, 2014 5:16 PM Clarification
    • Proposed as answer by Bill_Stewart Monday, June 9, 2014 2:48 PM
    • Marked as answer by PowerShellNovice Wednesday, June 11, 2014 1:51 PM
    Thursday, June 5, 2014 5:14 PM

All replies

  • Hi PowerShellNovice,

    this won't work because your Import-Csv breaks due to bad data (the input is no csv after all). You need to sequence this, first edit strings, then make it a csv.

    Get-Content C:\VPS\newfile.txt | Where {$_ -notlike "----*"} | %{$_.Replace("              ",",") | Set-Content C:\VPS\newfile3.csv

    Once you have a valid Csv, you can import it with Import-Csv and easily manipulate it by property. (Just to be sure: The file extension isn't what makes it a csv - it's the content formatting).

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Thursday, June 5, 2014 3:12 PM
  • Something like this, perhaps:


    $inputData = get-content "inputdata.txt"
    for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
      $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
        new-object PSObject -property @{
          "Count" = $_.Matches[0].Groups[1].Value
          "Test" = $_.Matches[0].Groups[2].Value
          "Number" = $_.Matches[0].Groups[3].Value
        } | select-object Count,Test,Number
      }
    }
    


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 5, 2014 3:16 PM
  • Why are you editing the file as text when you can edit it as a CSV object.  What you are doing is counter productive.

    In the end. You are asking the wrong question.  THe question you want to ask is "How do I manage a CSV file?"


    ¯\_(ツ)_/¯

    Thursday, June 5, 2014 3:24 PM
  • Hi Bill,

    Thanks for your reply. I updated my script to export the results to a csv. However for some reason, I see only the last record in the csv file. When I ran your piece of script, it was displaying all the records on screen. Can you tell what's missing.

    Thanks

    $inputData = get-content "C:\VPS\TRH_Report_Violations1.txt"
    for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
      $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
        new-object PSObject -property @{
          "Count" = $_.Matches[0].Groups[1].Value
          "Test" = $_.Matches[0].Groups[2].Value
          "Number" = $_.Matches[0].Groups[3].Value
      } | Export-csv C:\VPS\newfile_10.csv -notypeinformation
      }
    }

    Thursday, June 5, 2014 3:57 PM
  • That's right, sorry my bad :-)
    Thursday, June 5, 2014 3:59 PM
  • You need to put your export-csv command after the last } on the last line.


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 5, 2014 4:12 PM
  • Hi Bill,

    I updated the script as shown below. However after the script runs, I get a dialog box 

    cmdlet Export-Csv at command pipeline position 1 - Supply values for the

    InputObject

    $inputData = get-content "C:\VPS\TRH_Report_Violations1.txt"
    for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
      $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
        new-object PSObject -property @{
          "Count" = $_.Matches[0].Groups[1].Value
          "Test" = $_.Matches[0].Groups[2].Value
          "Number" = $_.Matches[0].Groups[3].Value
         }
       }
    } 
    Export-csv C:\VPS\newfile_10.csv -notypeinformation

    Thursday, June 5, 2014 4:28 PM
  • This is what I meant:


    $inputData = get-content "inputdata.txt"
    for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
      $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
        new-object PSObject -property @{
          "Count" = $_.Matches[0].Groups[1].Value
          "Test" = $_.Matches[0].Groups[2].Value
          "Number" = $_.Matches[0].Groups[3].Value
        } | select-object Count,Test,Number
      }
    } | export-csv "newcsvfile.csv" -notypeinformation
    


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 5, 2014 4:34 PM
  • Hi Bill,

    I am seeing this error

         } | select-object Count,Test,Number
       }
    } | export-csv C:\VPS\newfile_10.csv -notypeinformation

    An empty pipe element is not allowed.
    At line:10 char:3

    It is referring to the export-csv which is line 10.

    Thursday, June 5, 2014 4:44 PM
  • Remove this: "| select-object Count,Test,Number"

    ¯\_(ツ)_/¯

    Thursday, June 5, 2014 4:59 PM
  • I ran the script with the sample data you posted and it worked fine, so there is something in your input file that is different than what you posted.


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 5, 2014 5:02 PM
  • I found a blank line in the original.

    ¯\_(ツ)_/¯

    Thursday, June 5, 2014 5:05 PM
  • Sorry, forgot for doesn't generate output. We need to put it in a scriptblock and then output it:


    $inputData = get-content "inputdata.txt"
    & {
      for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
        if ( $inputData[$row] ) {
          $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
            new-object PSObject -property @{
              "Count" = $_.Matches[0].Groups[1].Value
              "Test" = $_.Matches[0].Groups[2].Value
              "Number" = $_.Matches[0].Groups[3].Value
            } | select-object Count,Test,Number
          }
        }
      }
    } | export-csv "outputdata.csv" -notypeinformation
    

    This version also skips blank lines in the input, provided the blank lines are past the third line.

    -- Bill Stewart [Bill_Stewart]


    • Edited by Bill_Stewart Thursday, June 5, 2014 5:16 PM Clarification
    • Proposed as answer by Bill_Stewart Monday, June 9, 2014 2:48 PM
    • Marked as answer by PowerShellNovice Wednesday, June 11, 2014 1:51 PM
    Thursday, June 5, 2014 5:14 PM
  • No that does not work, it only outputs on the screen. I need that same data exported to a csv file.

    $inputData = get-content "C:\VPS\TRH_Report_Violations1.txt"
    for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
      $inputData[$row] | select-string '^(\d+),(\d+)\s+(\d+)' | foreach-object {
        new-object PSObject -property @{
          "Count" = $_.Matches[0].Groups[1].Value
          "Test" = $_.Matches[0].Groups[2].Value
          "Number" = $_.Matches[0].Groups[3].Value
         } 
       }
    }

    Thursday, June 5, 2014 5:27 PM
  • See my most recent reply.


    -- Bill Stewart [Bill_Stewart]

    Thursday, June 5, 2014 5:30 PM
  • I wrote a function for this kind of thing:

    http://gallery.technet.microsoft.com/scriptcenter/New-PSObjectFromMatches-87d8ce87

    You use it like this:

    $Pattern = '(\d+),(\d+)\s+(\d+)'
    
    Get-Content C:\VPS\newfile.txt |
     new-psobjectfrommatches -pattern $pattern -Property $null,Count,Test,Number |
     export-csv C:\VPS\newfile3.csv -notype
    


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Thursday, June 5, 2014 5:42 PM
  • Hi Bill,

    I appreciate your time and help with this code. It worked great.

    Regards

    Wednesday, June 11, 2014 1:51 PM