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]