6

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Chris
  • 183
  • 1
  • 10

1 Answers1

6

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

Community
  • 1
  • 1
Roman Kuzmin
  • 40,627
  • 11
  • 95
  • 117
  • Thanks, but this still leaves me with a problem: $dt2 = ,(Import-CliXml -path "c:\exports\data.xml") $dt2.GetType().Name Object[] $dt2 | Get-Member TypeName: Deserialized.System.Data.DataTable – Chris Nov 05 '10 at 09:56
  • What is the problem in this? (BTW, you do something different than I suggested). – Roman Kuzmin Nov 05 '10 at 10:03
  • If I save the table in the way you say, then import with: $dt = Import-CliXml -path "c:\exports\data.xml" $dt.GetType() gives the error: "Method invocation failed because [Deserialized.System.Data.DataTable] doesn't contain a method named 'GetType'" When I pass $dt into my function which requires a DataTable, I get: Cannot convert the "System.Collections.ArrayList" value of type "System.Collections.ArrayList" to type "System .Data.DataTable". – Chris Nov 05 '10 at 10:08
  • This is another question, @Chris, perhaps not suitable for discussion in these comments. How do you send $dt to your function? How does your function look like? Why do you think it is supposed to work with deserialized by PowerShell DataTable? – Roman Kuzmin Nov 05 '10 at 10:14
  • Perhaps PowerShell Clixml serialization should not be used in this particular case. I would recommend you to use DataTable.WriteXml/ReadXml methods for saving and restoring table data *exactly* as they are. Do not deserialize the table, use a new one and call ReadXml on it. – Roman Kuzmin Nov 05 '10 at 10:23
  • Thanks Roman, I have managed to get it to work using the WriteXml and ReadXml functions as you suggested. However I am still shocked that a deserialized object in PowerShell is not identical to the original object. – Chris Nov 05 '10 at 12:34
  • Yes, this is confusing. Clixml export/import has nothing to do with classic serialization. – Roman Kuzmin Nov 05 '10 at 12:40
  • I think serialization in .NET in general is weak. – JasonMArcher Nov 08 '10 at 06:41