ADO .NET, Export-CSV, required column names (headers)

PowerShell has a very nice cmd-let called Export-CSV. It allows to serialize any .NET object to a csv file. The cmd-let has a heavy usage when you need to export a sql table to a csv file, i.e. an instance of the System.Data.DataTable  to the file.

Such script may look like this one:

$Database = "your_database"
$Server = "your_server"
$AttachmentPath = "your_file.csv"
$SqlQuery = "SELECT * FROM your_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataTable = New-Object System.Data.DataTable
$SqlAdapter.Fill($DataTable) #ref.A (will be used further)
$SqlAdapter.Dispose()
$SqlCmd.Dispose()
$SqlConnection.Close()

$DataTable | Export-CSV $AttachmentPath -notype #ref.B (will be used further)

But there is one big problem which has become crucial for one system I have been working on (I have left refs in the code for this case). If the DataTable is empty, headers (column names) are not rendered to the file. Luckily, at first, ADO .NET still fetches a schema even if no records are returned; at second, PowerShell allows us to create types and adds their members dynamically using Add-Member cmd-let. These two lucks give us a very intelligent solution below:

#...
$nRec = $SqlAdapter.Fill($DataTable) #instead of ref.A
#...
if ($nRec -gt 0) { #instead of ref.B
    $DataTable | Export-CSV $AttachmentPath -notype
}
else {
    $obj = New-Object Object
    $DataTable.Columns | % { Add-Member -InputObject $obj NoteProperty $_.ColumnName $null }
    $obj | convertto-csv -NoTypeInformation | Select-Object -first 1 | Out-File $AttachmentPath 

}

Fairly simple, if some records are fetched, we do the same, otherwise we create an object, then we iterate over columns of the data table and add members to the new object using the ColumnName property as a property name and null as its value (the value is mandatory).

Afterwards we convert this object to csv (ConvertTo-CSV is almost the same as Export-CSV but it does not write a file, it propagates result lines through a pipeline).  We select only the first line, because the second line contains nulls, and write this line down to a file using Out-File. Looks awesome.

Advertisements
This entry was posted in .NET, ADO .NET, Powershell. Bookmark the permalink.

One Response to ADO .NET, Export-CSV, required column names (headers)

  1. vidhya says:

    Thank you so much,This post is really very helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s