six demon bag

Wind, fire, all that kind of thing!

2015-12-30

Save and Restore a DataTable

If you have a DataTable object in PowerShell and you want to persist that object as a file (and restore it back to a DataTable object sometime later) the naïve approach would be to export the (tabular) data to a (tabular) CSV:

$dt | Export-Csv -Path 'C:\path\to\table.csv' -NoType

However, the problem with this approach is that you lose the type information for the columns of the datatable (the only type information saved by the Export-Csv cmdlet is about the type of the objects representing the rows). Also, there's no simple way to restore the CSV back to a datatable.


A better approach would be to serialize the object via Export-Clixml, so it can be restored via Import-Clixml:

$dt | Export-Clixml -Path 'C:\path\to\table.xml'

This does preserve the data type of the columns, but importing the XML creates just a list of custom objects instead of an actual DataTable object:

PS C:\> $tbl = Import-Clixml -Path 'C:\path\to\table.xml'
PS C:\> Get-Member -InputObject $tbl

   TypeName: System.Object[]

Name           MemberType            Definition
----           ----------            ----------
Count          AliasProperty         Count = Length
...

The reason for this behavior is that the pipeline unrolls the rows of the table to a list of DataRow objects, so Export-Clixml never learns anything about the original DataTable object. The usual workaround to prevent this is to prefix the object variable with the unary comma operator:

,$dt | Export-Clixml -Path 'C:\path\to\table.xml'

This wraps the datatable in an array with a single element (the datatable), so that the pipeline unrolls just the array wrapper, thus preserving the original DataTable object.

So far, so good, but unfortunately that still doesn't re-create the original DataTable object on re-import. Instead the import produces a deserialized datatable:

PS C:\> $tbl = Import-Clixml -Path 'C:\path\to\table.xml'
PS C:\> Get-Member -InputObject $tbl

   TypeName: Deserialized.System.Data.DataTable

Name                   MemberType Definition
----                   ---------- ----------
GetType                Method     type GetType()
...

which has the same properties, but none of the methods of a regular datatable.

The best way I found for saving and restoring datatables was to write table data and schema information to an XML file (the schema is where the type information is stored):

$writer = New-Object New-Object IO.StreamWriter 'C:\path\to\table.xml'
$dt.WriteXml($writer, [Data.XmlWriteMode]::WriteSchema)
$writer.Close()
$writer.Dispose()

and restore the XML into a DataSet:

$ds = New-Object Data.DataSet
$ds.ReadXml('C:\path\to\table.xml', [Data.XmlReadMode]::ReadSchema)
$tbl = $ds.Tables[0]
PS C:\> $ds = New-Object Data.DataSet
PS C:\> $ds.ReadXml('C:\path\to\table.xml', [Data.XmlReadMode]::ReadSchema)
ReadSchema
PS C:\> $tbl = $ds.Tables[0]
PS C:\> Get-Member -InputObject $tbl

   TypeName: System.Data.DataTable

Name                   MemberType Definition
----                   ---------- ----------
ColumnChanged          Event      System.Data.DataColumnCha...
...

Posted 17:01 [permalink]