how to convert text to csv file which having delimiter '|' in powershell-CodePudding

Code:

$file= Get-Content "C:\Users\phm4\Desktop\source\status.txt" | select -first 18

$new=$file | select -Skip 12
$nospace=($new)-replace '\s' 
$nospace | Out-File new1.txt

import-csv new1.txt -Delimiter "|" | export-csv csvfile.csv

Sample data:

1|10.9.7.73|-A--|0|505k|505k|2.4T/52.7T(5%)|L3:1.5T
2|10.9.7.74|OK|0|608k|608k|2.4T/52.7T(5%)|L3:1.5T
3|10.9.7.75|OK|0|626k|626k|2.4T/52.7T(5%)|L3:1.5T
4|10.9.7.76|OK|0|0|0|2.4T/52.7T(5%)|L3:1.5T
5|10.9.7.77|OK|0|0|0|2.4T/52.7T(5%)|L3:1.5T
6|10.9.7.78|OK|0|398k|398k|2.4T/52.7T(5%)|L3:1.5T

CodePudding user response：

Import the text file using import-csv then Export it using export-csv


$path = "C:\Users\phm4\Desktop\source\status.txt"
$csv = "C:\Users\phm4\Desktop\source\csvfile.csv"
$import = import-csv $path -Delimiter "|" 
$import | export-csv $csv

CodePudding user response：

The reason you canot use Import-Csv on the text file directly is because a proper CSV file has headers which must be unique and the example you show has no headers.
That means PowerShell will use the top line as header, but can't because then there are two columns caled '505k'..

What you can do is:

# get the lines you're interested in and repace whitespaces
$data = Get-Content -Path 'D:\Test\test.txt' -TotalCount 18 | Select-Object -Skip 12 | ForEach-Object { $_ -replace '\s' }

# first find out how many headers you need and create them (as demo, they will be called 'Column_1' etc.)
$n = (($data | ForEach-Object { ($_ -split '\|').Count }) | Measure-Object -Maximum).Maximum
$headers = 1..$n | ForEach-Object { "Column_$_" }

To me it is not really clear if you want the resulting csv file with the pipe symbol | as delimiter, but if that is the case, just simply write the data back to file including those headers:

# join the created $headers array with the pipe symbol and write to file
($headers -join '|') | Set-Content -Path 'D:\Test\csvfile.csv'
$data | Add-Content -Path 'D:\Test\csvfile.csv'

BUT if you don't want the pipe, but rather a different character as delimiter, use:

# for demo output a csv with the default comma as delimiter
$data | ConvertFrom-Csv -Header $headers -Delimiter '|' | Export-Csv -Path 'D:\Test\csvfile2.csv' -NoTypeInformation