CODEWITHSUNDEEP
powershellcsv
ledgeJumper
ledgeJumper

Reputation: 3630

Is there a way to force powershell -Export-CSV cmdlet to maintain a specific column order?

I'm new to powershell, so the script is a Frankenstein of various examples of from sites.

My question is how can I make sure that a csv file I am creating for a DataTable keeps the column order I specify?

My script does this to populate the csv headers and values like so:

...snip...
$dataTable | ForEach-Object {
            $csv+=New-Object PSObject -Property @{
                program_code=$_.ProgramCode;
                first_name=$_.FirstName;
                last_name=$_.LastName;
                email=$_.email;
                phone=$_.phone;
                phone2=$_.otherphone;
                address=$_.addr1;
                address2=$_.addr2;
                city=$_.city;
                state=$_.state;
                postal_code=$_.Zip;
                country=$_.Country;
                grad_year=$_.HsGradDate;
                lead_date=$_.LeadDate;
                lead_source=$_.LeadSource;
                school_status=$_.SchoolStatus;
        }
        }
    $csv | Export-CSV C:\scripts\NewLeads$(Get-Date -Format yyyyMMdd).csv -notype -Append
...snip...

I want the file to have to columns in the order that I specify in the script, but when I open it in notepad or excel the columns appear in a seemingly random order. Keyword being seemingly since they may have some method of ordering themselves.

Upvotes: 12

Views: 15406

Answers (2)

Jason Shirk
Jason Shirk

Reputation: 8019

In PowerShell V3, instead of:

        $csv+=New-Object PSObject -Property @{

I would use:

        $csv+=[pscustomobject]@{

The PowerShell V3 parser will preserve the order of the keys when you cast a hash literal to either [ordered] or [pscustomobject]. A small side benefit to this approach - it will also be faster.

If you are using V2, you'll need to skip the -Property parameter to New-Object and instead use multiple calls to Add-Member. It will look something like:

$csv+=New-Object PSObject |
    Add-Member -Name program_code -Value $_.ProgramCode -MemberType NoteProperty -PassThru |
    Add-Member -Name first_name -Value $_.FirstName -MemberType NoteProperty -PassThru |
    ...

Upvotes: 19

alroc
alroc

Reputation: 28174

Select the fields in the order required, then export.

$csv | select-object -property program_code,first_name,last_name,email,phone,phone2,address,address2,city,state,psotal_code,country,grad_year,lead_date,lead_source,school_status |
Export-CSV C:\scripts\NewLeads$(Get-Date -Format yyyyMMdd).csv -notype -Append

However, you may be able to short-circuit this a little. Depending on what $dataTable really is, you may (should, in most cases) be able to select directly from that object and bypass creating the collection of PSObjects. But if you need the custom headers, you'll need to use expressions in select-object (linebreaks for readability).

$dataTable| select-object @{Name="program_code";Expression={$_.ProgramCode}},`
@{Name="first_name";Expression={$_.firstname}},`
@{Name="last_name";Expression={$_.lastname}},email,phone,`
@{Name="phone2";Expression={$_.otherphone}},`
@{Name="addr1";Expression={$_.address}},`
@{Name="addr2";Expression={$_.address2}},city,state,`
@{Name="postal_code";Expression={$_.zip}},country,`
@{Name="grad_year";Expression={$_.hsgraddate}},`
@{Name="lead_date";Expression={$_.leaddate}},`
@{Name="lead_source";Expression={$_.leadsource}},`
@{Name="school_status ";Expression={$_.schoolstatus }}|
 Export-CSV C:\scripts\NewLeads$(Get-Date -Format yyyyMMdd).csv -notype -Append

Upvotes: 11

Related Questions