Odtnhj

Hi I am trying to export a text file to csv format in Powershell but facing some alignment issue.data not coming properly.

Input text

clexec.py "top -d1|grep Swap"
NVA-0055-Prod-N08:Swap: 48G Total, 1428K Used, 48G Free
NVA-0055-Prod-N01:Swap: 48G Total, 332K Used, 48G Free
NVA-0055-Prod-N04:Swap: 48G Total, 169M Used, 48G Free
NVA-0055-Prod-N05:Swap: 48G Total, 884K Used, 48G Free
NVA-0055-Prod-N02:Swap: 48G Total, 4236K Used, 48G Free
NVA-0055-Prod-N06:Swap: 48G Total, 132K Used, 48G Free
NVA-0055-Prod-N07:Swap: 48G Total, 92K Used, 48G Free
NVA-0055-Prod-N03:Swap: 48G Total, 712K Used, 48G Free

I am using below below command

$a= import-csv 'C:UsersPDC_AVERE_SWAP_OP.txt' -Delimiter 't' -Header 'Name','Total','used','free'
$a | export-csv csvfile.csv -NoTypeInformation

My output coming like below

after correcting th delimiter part I am getting all the data in a single column but I need it to split among the columns Total,used and free

If you want to drop the initial line from the file,

you may read content as text and skip the first line before ConvertFrom-Csv

Get-Content 'C:UsersPDC_AVERE_SWAP_OP.txt' | Select-Object -Skip 1|
 ConvertFrom-Csv -Delimiter "`t" -Header Name,Total,used,free |
 Export-Csv csvfile.csv -NoTypeInformation

Just in case there is no tab but space(s) like above sample, you could use

$Data = Get-Content '.PDC_AVERE_SWAP_OP.txt' |Select-Object -skip 1| ForEach-Object
 $Name,$Total,$used,$free = ($_ -split('s+'))[0,1,3,5]
 [PSCustomObject]@
 Name = $Name
 Total= $Total
 used = $used
 free = $free
 

$Data
$Data | Export-Csv '.PDC_AVERE_SWAP_OP.csv' -NoTypeInformation
$Data | Export-Excel '.PDC_AVERE_SWAP_OP.xlsx' -AutoSize -Show

and thereby strip off the label from each value:

Name Total used free
---- ----- ---- ----
NVA-0055-Prod-N08:Swap: 48G 1428K 48G
NVA-0055-Prod-N01:Swap: 48G 332K 48G
NVA-0055-Prod-N04:Swap: 48G 169M 48G
NVA-0055-Prod-N05:Swap: 48G 884K 48G
NVA-0055-Prod-N02:Swap: 48G 4236K 48G
NVA-0055-Prod-N06:Swap: 48G 132K 48G
NVA-0055-Prod-N07:Swap: 48G 92K 48G
NVA-0055-Prod-N03:Swap: 48G 712K 48G

When using Export-Excel you'll directly get an xlsx file

EDIT: as mentioned in the comment here a variant using a RegEx to separate the elements
See the RegEx live https://regex101.com/r/GFXpXw/1 with a missing used value

## Q:Test20181115SO_53322626_2.ps1

$RE = "^(?<Name>[^ ]+) (?<Total>d+[KMG]) Total(, (?<used>d+[KMG]) Used)?, (?<free>d+[KMG])"

$Data = Get-Content '.PDC_AVERE_SWAP_OP.txt' |Select-Object -skip 1| ForEach-Object
 if ($_ -match $RE)
 [PSCustomObject]@
 Name = $Matches.Name
 Total= $Matches.Total
 used = $Matches.used
 free = $Matches.free
 
 else Write-Host ("0 didn't match RegEx" -f $_)

$Data
$Data | Export-Csv '.PDC_AVERE_SWAP_OP.csv' -NoTypeInformation
#$Data | Export-Excel '.PDC_AVERE_SWAP_OP.xlsx' -AutoSize

The problem is -Delimiter 't'. It literally means that letter t is the delimiter character. Most likely you mean the tab character, which in Powershell is backtick-t: `t.

use "`t" instead of "t" as Delimeter and instead using Export-CSV you can use the following code,

 "Name,Total,used,free" | Set-Content csvfile.csv

 $a | % out-file csvfile.csv -Encoding ascii -Append -Force