Answered by:
Converting csv file from a txt file

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,003100Monday, 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
¯\_(ツ)_/¯
- Marked as answer by PowerShellNovice Monday, June 2, 2014 8:30 PM
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
¯\_(ツ)_/¯
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:34Thanks,
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
¯\_(ツ)_/¯
- Marked as answer by PowerShellNovice Monday, June 2, 2014 8:30 PM
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