Vfrdtyky

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  |   %{ Write-Output "$($_.Name),$($_.Total),$($_.free),$($_.free)"  |  out-file csvfile.csv -Encoding  ascii  -Append  -Force   }