Problem creating csv

PowerShell Help
1

Hello,

The text file below is generated from a storage device. I would like to clean it up a bit and export as .csv. Basically, I’d like to remove the lines with hyphens or dashes and the last total line, while keeping the headers. I also want to replace all spaces with commas.

Name Nodes Requested Protection HDD Total % SSD Total %
––––––––––––––––––––––––––––––––––––––––––––
n410_108tb_48gb 1-3 +2d:1n 57.9355T 286.4162T 20.23% 0 0 0.00%
––––––––––––––––––––––––––––––––––––––––––––
Total: 1 57.9355T 286.4162T 20.23% 0 0 0.00%

Here is my code so far:

Get-Content -Path c:\temp\test.txt |
Select-String -Pattern ‘–’, ‘Total:’ -notmatch | # remove unwanted lines
Out-File c:\temp\test2.txt` # output to new file

$values = Get-Content -Path c:\temp\test2.txt # read file just created
$values2 = $values -replace ’ {1,}', “,” # replace spaces with commas

 

This works, but I’m having trouble exporting it as a csv. Somehow it all ends up in one column.

Any help or suggestions greatly appreciated.

2

Look at Export-CSV

3

Thanks for the suggestion, I added

$values2 | Export-Csv .\test3.csv -notypeinformation

But when I open test3.csv in Excel, I just get:

 

Length
0
56
62
0
0
 
4

You save the string into a file and then you read the file as text file.
You have to read it as a csv file…
… and correct some double names headers before saving

$values = Get-Content -Path c:\temp\test.txt | Select-String -Pattern '--', 'Total:' -notmatch 

$values2 = $values -replace ' {1,}', ","    

## Correct some stuff 
## Total and % are twice
##
$values2 = $values2 -replace "HDD,Total,%","HDD,HDDTotal,HDD%"
$values2 = $values2 -replace "SSD,Total,%","SDD,SDDTotal,SDD%"

$values2 | Out-File c:\temp\test2.txt
                    
# saved as TEXTFile and now import as CSV!

$valueResult = import-csv c:\temp\test2.txt
$valueResult
5

Try $values2 | ConvertFrom-Csv.