Load serialized DataTable into PowerShell - returns an array of DataRows not a DataTable

Question

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

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

and download it as follows:

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

But the type I'm returning is an array of strings, not a DataTable! This causes me serious problems, since it needs to be passed to a function that requires a DataTable, and it cannot be attributed to it.

Any help is much appreciated, thanks.

Answer 1

This is a well-known trap: PowerShell treats your DataTable as a collection of DataRow elements. This is the first team.

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

already "forgets" the data table. You can see the output file, it starts with a 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 it works the same way):

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

As a result, the output file now starts with a 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" 

Allows you to check:

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

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

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

So, we really dehydrate the DataTable in this way.

===

EDIT: This effect is known as deploy. PowerShell tends to expand collections. The comma operator creates an array of one element. PowerShell also expands this array, but it does not expand its elements (our DataTable ).

Here is a very similar question:

Strange PowerShell behavior returning a DataSet / DataTable