Convert text file to csv

Hello,

I’m trying to break the text file shown below into columns and output to .csv.

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%

I’m trying to generate csv with the following columns

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%

Here’s the code I have so far:

Get-Content -Path c:\temp\test.txt | Select-String -Pattern ‘–’, ‘Total:’ -notmatch | Out-File c:\temp\test2.txt
$values = Get-Content -Path c:\temp\test2.txt
$values2 = $values -replace ’ {1,}', “,”

In the above, I’m taking out the lines with dashes and the final line with ‘Total:’ in the substring. This gives me the desired output:

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%

However, when I write that out as a .csv, it does not break it down into columns as expected.

Thanks.

Unfortunately you have a few problems you need to address first.

  • There are two Total and two % headers, they need to be unique.
  • There are 10 column headers/properties but only 9 corresponding values.

If you can explain how you would handle these then we could most likely come up with a good solution.

I removed the ‘Protection’ column, but this will output to csv.

$header = 'Name','Nodes','Requested','HDD','HDD Total','HDD%','SSD','SSD Total','SSD%'
$content = Get-Content .\input.txt | Where-Object {$_ -notmatch '^-|^Total'} | 
Select-Object -Skip 1
$content -replace '\s',',' | ConvertFrom-Csv -Header $header |
Export-Csv -NoTypeInformation -Path .\output.csv