Export Data to a CSV and Maintain the Column Order

Posted on by
Reply

This post provides a simple example of how we can export data to a CSV and maintain the column order:

 $csvFile = "c:\temp\alkane.csv"

 $exampledata = @(@("John","37"),@("Peter","14"),@("Michelle","22"),@("Abdul","31"),@("Roger","22"),@("Rachel","50"))
 $csvwrapper = @()

foreach($person in  $exampledata) {
    $name = $person[0]
    $age = $person[1]    
    
    #append to a wrapper which we export to CSV at the end (Name and Age are the column names we want in the CSV)
    $csvwrapper += New-Object PSObject -Property @{ 
        Name = $name;
        Age = $age; 
    }
}

 $csvwrapper | 
 Select Name, Age | #we pipe our wrapper into this so we can define the order we want our columns to display in the CSV
 Export-Csv -Path $csvFile -NoTypeInformation

Invoke-Sqlcmd Returns Columns RowError and HasErrors

Posted on by
Reply

I’ve been performing some SQL queries recently using PowerShell and Invoke-SqlCmd, but I’ve noticed that by default Invoke-Sqlcmd returns columns RowError and HasErrors.  Here is a simple example of returning a list of devices (a single column of data) from a database:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices"

Of course I pipe this into Export-CSV like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"

When we read the exported data in the CSV we expect one column of data called ‘Device’.  However, instead we can also see columns called RowError, HasErrors and others!  It appears that Invoke-Sqlcmd returns columns RowError and HasErrors.  And whilst I’m not sure why these appear, we can omit them like so:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors

and of course if we wanted to pipe this into a CSV we can do so like this:

Invoke-Sqlcmd -ServerInstance "AlkaneSQLInstance" -Database "AlkaneSQLDatabase" -Query "SELECT Device FROM Devices" | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path "C:\Temp\Alkane.csv"

And voila.  We only get the columns of data that we asked for!

Export to CSV using PowerShell and Dynamic Data

Posted on by
Reply

This post explains how to export to CSV using PowerShell and dynamic data.  I find that hashtables are quite useful for various scenarios when scripting in PowerShell.  We can also utilise them when exporting data using Export-CSV.

I provide two approaches to this.  Both approaches work by adding hashtables to an array.  By default if you export an array of hashtables to an array you will lose the column ordering.  With PowerShell 2 we can circumvent this issue by using ‘Select’ and specifying the order of the columns we would like to select:

$csvnamePS2 = "C:\Temp\" + (Get-Date -format "dd-MM-yyyy-HH-mm") + ".csv"
$global:csvarrayPS2 = @()

function Export-To-CSV()
{  
    param (       
        [string]$FirstName,
	[string]$LastName
    )
	$wrapper = New-Object -TypeName PSObject -Property @{ FirstName = $FirstName; LastName = $LastName;} | Select FirstName, LastName
	$global:csvarrayPS2 += $wrapper
}

Export-To-CSV "Captain" "Hook"
Export-To-CSV "Peter" "Pan"

$global:csvarrayPS2 | Export-Csv -NoTypeInformation -Path $csvnamePS2

With PowerShell 3 we can simplify this, by specifying the [ordered] type for our hashtable:

$csvnamePS3 = "C:\Temp\" + (Get-Date -format "dd-MM-yyyy-HH-mm") + ".csv"
$global:csvarrayPS3 = @()

function Export-To-CSV()
{  
    param (       
        [string]$FirstName,
	[string]$LastName
    )
	$wrapper = [ordered]@{ FirstName = $FirstName; LastName = $LastName;}
	$global:csvarrayPS3 += New-Object psobject -property $wrapper
}

Export-To-CSV "Captain" "Hook"
Export-To-CSV "Peter" "Pan"

$global:csvarrayPS3 | Export-Csv -NoTypeInformation -Path $csvnamePS3