Loading a serialized DataTable in PowerShell - Gives back array of DataRows not a DataTable

Question

I'm trying to save and load a DataTable in PowerShell. I save it like this:

$dt | Export-CliXml -path "c:\exports\data.xml"

and load it like this:

$dt = Import-CliXml -path "c:\exports\data.xml"

But the type I get back is an array of Rows rather than a DataTable! This is causing me major problems as it needs to be passed into a function which requires a DataTable, and it cannot be cast to one.

Any help greatly appreciated, thanks.

Answer 1

This is a known trap: PowerShell processes your DataTable as a collection of DataRow items. That is the very first command

$dt | Export-CliXml -path "c:\exports\data.xml"

already “forgets” the data table. You may take a look at the output file, it starts with DataRow:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Data.DataRow</T>

To avoid this effect, use the , operator (it looks funny but that’s exactly how it works):

, $dt | Export-CliXml -path "c:\exports\data.xml"

As a result, the output file now starts with DataTable:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Data.DataTable</T>

After that you can import the table back:

$dt = Import-CliXml -path "c:\exports\data.xml"

Let’s check it:

$dt | Get-Member
# output:
TypeName: Deserialized.System.Data.DataRow
…

We can see the same effect (DataRow instead of DataTable). Thus, the correct command is with ,:

, $dt | Get-Member
# output:
TypeName: Deserialized.System.Data.DataTable
…

So, we really dehydrate a DataTable in this way.

===

EDIT: This effect is known as unrolling. PowerShell tends to unroll collections. The comma operator creates an array of a single item. PowerShell unrolls this array, too, but it does not unroll its items (our DataTable).

Here is a very similar question:

Strange behavior in PowerShell function returning DataSet/DataTable