Costa Zachariou Costa Zachariou - 3 months ago 32
PowerShell Question

Sort and export-CSV

I have a csv file containing rows of the following extract:


"EmployeeID","FirstName","LastName","Location","Department","TelephoneNo","Email"

"000001 ","abc ","def ","Loc1"," "," ","name1@company.com "

"000023 ","ghi ","jkl ","Loc2"," "," ","name2@company.com "

"000089 ","mno ","pqr ","Loc2"," "," ","name3@company.com "


How do I keep the quotes and sort and save as a csv file?

I have the following powershell source script which works with csv files not having double quotes for the columns:

Get-Content $Source -ReadCount 1000 |
ConvertFrom-Csv -Delimiter $Delimiter |
Sort-Object -Property $NamesOfColumns -Unique |
ForEach-Object {
# Each of the values in $ColumnValueFormat must be executed to get the property from the loop variable ($_).
$values = foreach ($value in $ColumnValueFormat) {
Invoke-Expression $value
}
# Then the values can be passed in as an argument for the format operator.
$ShowColsByNumber -f $values
} |
Add-Content $Destination;


The
$Source
,
$Delimiter
,
$NamesOfColumns
and
$ColumnValueFormat
are given or built dynamically.

$ColumnValueFormat
with a non quoted csv file contains:

$_.EmployeeID.Trim()
$_.FirstName.Trim()
$_.LastName.Trim()
$_.Location.Trim()
$_.Department.Trim()
$_.TelephoneNo.Trim()
$_.Email.Trim()


$ColumnValueFormat
with a quoted csv file contains:

$_."EmployeeID".Trim()
$_."FirstName".Trim()
$_."LastName".Trim()
$_."Location".Trim()
$_."Department".Trim()
$_."TelephoneNo".Trim()
$_."Email".Trim()


The problem seems to be based around the
$ColumnValueFormat
that is placing the column headers with the double quotes. (If I remove them I am not sure the internals of the cmdlet will recognize the column headings when it is processing the rows)

I am having two problems:


  1. The column heading surrounded by the double quotes. The problem seems to be based around the
    $ColumnValueFormat
    that is placing the column headers with the double quotes as it does not process the rows. (If I remove the double quotes then it does not recognize the column headings when it is processing the rows).

  2. Another problem I came across last minute is if the last column is blank it thinks it's a null and when the
    Invoke-Expression $value
    executes (where
    $value
    holds the last column expression of
    $_.Email.Trim()
    - on a non quoted CSV file) it bombs. If I try to place the statement in a try/catch block it simply ignore it the last column is not added to the
    $values
    array and again bombs.


Answer

Quotes around property names are used syntactically to access names with spaces, not to write quotes to the output.

Export-Csv cmdlet doesn't have an option to force quotes so we'll have to export the CSV manually. And we'll have to process empty values that are $Null after ConvertFrom-Csv with an empty string. In case only some fields are needed we'll use Select cmdlet with -index parameter.

Get-Content $Source |
    ConvertFrom-Csv |
    %{ $header = $false } {
        if (!$header) {
            $header = $true
            '"' + (
                ($csv[0].PSObject.Properties.Name.trim() |
                    select -index 1,6
                ) -join '","'
            ) + '"'
        }
        '"' + (
            ($_.PSObject.Properties.Value |
                %{ if ($_) { $_.trim() } else { '' } } |
                select -index 1,6
            ) -join '","'
        ) + '"'
    } | Out-File $Destination

The above code is great for pass-through processing of large CSV files because it doesn't keep the entire file in memory. Otherwise it's possible to simplify the code a bit:

$csv = Get-Content $Source | ConvertFrom-Csv
$csv | %{
    '"' + (
        ($csv[0].PSObject.Properties.Name.trim() |
            select -index 1,6
        ) -join '","'
    ) + '"'
} {
    '"' + (
        ($_.PSObject.Properties.Value |
            %{ if ($_) { $_.trim() } else { '' } } |
            select -index 1,6
        ) -join '","'
    ) + '"'
) | Out-File $Destination