# 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)
PS C:\> $tbl =$ds.Tables[0]
...