locked
Converting csv file from a txt file RRS feed

  • Question

  • Hi,

    I need to create a csv file from a txt file. However it is not working. 

    "Count,TestID" | Set-Content C:\VPS\TRH_Report.csv
    foreach ($s1 in Get-Content C:\VPS\TRH_Report.txt)
    {
            $s1 | Add-Content C:\VPS\temp1.txt
            (gc C:\VPS\temp1.txt) | sc C:\VPS\temp1.txt
            (get-content C:\VPS\temp1.txt) | add-content C:\VPS\TRH_Report_Final.csv
            remove-item C:\VPS\temp1.txt
    }
    
    TRH_Report.txt The txt file looks something like this. 

    Count,TestID                                                                   

    -----------------------------------------------                                 
    178,007501                                                                      
    84,007350                                                                       
    24,004140                                                                       
    81,007601                                                                       
    21,004120                                                                       
    84,003100    

    Monday, June 2, 2014 4:45 PM

Answers

  • Close:

    -

    Import-Csv 'C:\VPS\newfile.csv' | 
         Where-Object {[int]($_.Count) -gt 1000} | 
         Export-Csv -Path C:\VPS\newfile_1.csv –NoTypeInformation


    ¯\_(ツ)_/¯

    Monday, June 2, 2014 8:22 PM

All replies

  • Assuming you are telling us the true contents of the text file do this:

    Get-Content C:\VPS\TRH_Report.txt |
        ?{$_ -notmatch '--------'} |
        Out-File newfile.csv
    


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Monday, June 2, 2014 5:09 PM
    Monday, June 2, 2014 4:58 PM
  • This might be better because it also removes any extra spaces:

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


    ¯\_(ツ)_/¯


    • Proposed as answer by jrv Monday, June 2, 2014 5:09 PM
    • Edited by jrv Monday, June 2, 2014 7:08 PM
    Monday, June 2, 2014 5:06 PM
  • Hi Jrv,

    I am seeing the following message, when I try to run it.

    PS C:\VPS> Import-Csv C:\VPS\TRH_Report.txt |
         {$_.Count -notmatch '----'} |
         export-csv newfile.csv -notype
    Expressions are only allowed as the first element of a pipeline.
    At line:2 char:34

    Thanks,


    Monday, June 2, 2014 6:01 PM
  • That's missing the Where-Object alias ?. Put that back in at the beginning of the second line.


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Monday, June 2, 2014 6:04 PM
  • That is an issue with the editor.  I copied and pasted it from and to the screen.  Characters disappear mostly at the beginning of lines.  Try putting a $ at the beginning and copy and past.  Half of the time the $ disappears.

    I fixed the missing ? - Thanks Mike.


    ¯\_(ツ)_/¯

    Monday, June 2, 2014 7:09 PM
  • Hi Jrv,

    That seems to have worked. Now I need to export only those TestID's where Count greater than 1000. I am running the following command. However the new file does not have any data. I have checked the newfile.csf and it has all the data.

    Import-Csv 'C:\VPS\newfile.csv' | Where-Object {[int]$_.Count -gt 1000} | Export-Csv -Path C:\VPS\newfile_1.csv –NoTypeInformation
    
    Thanks

    Monday, June 2, 2014 8:06 PM
  • Close:

    -

    Import-Csv 'C:\VPS\newfile.csv' | 
         Where-Object {[int]($_.Count) -gt 1000} | 
         Export-Csv -Path C:\VPS\newfile_1.csv –NoTypeInformation


    ¯\_(ツ)_/¯

    Monday, June 2, 2014 8:22 PM
  • JRV, Thank you for your time and help. I really appreciate it. That worked like a charm :-)
    Monday, June 2, 2014 8:31 PM
  • No problem.  You were 90% there from the beginning.

    ¯\_(ツ)_/¯

    Monday, June 2, 2014 9:06 PM