Ad Space here

Direct Link: 2/4/2019PowerShell Export and Import of Datagrids Preserving Schema


I was recently trying to save off data from Datagrids in PowerShell to merge data from one SQL Server Express database that I was working on into another.  The first thing that came to mind was to use Export-Clixml.  For example:

Code:
 $dt | Export-Clixml .\OutFile.xml

The problem with Export-Clixml is the output object is deserialized.  It makes it extremely hard to load the data into another database when it is imported using Import-Clixml.

I was able to come up with these functions that preserved the data types by writing out the schema using System.Io.StreamWriter.   The Datatable has to be named to export it.

Code:
function Write-DtXml
{
    <#
    .SYNOPSIS
    Export a Datagrid to XML
    .DESCRIPTION
    Export a Datagrid to XML
    #>
    param (
        [Parameter(Mandatory=$true)]
        [System.Data.DataTable]$Dt,
        [Parameter(Mandatory=$true)]
        [string]$Name,
        [Parameter(Mandatory=$true)]
        [string]$OutputDir
    )
    $Dt.TableName = $Name
    $writer =  [System.IO.StreamWriter]($OutputDir + "\DataTable_" + $Name + ".xml")
    $Dt.WriteXml($writer, [Data.XmlWriteMode]::WriteSchema)
    $writer.Close()
    $writer.Dispose()
}

Code:
function Import-DtXml
{
    <#
    .SYNOPSIS
    Imports a Datatable
    .DESCRIPTION
    Imports a Datatable
    #>
    param (
        [string]$Name,
        [Parameter(Mandatory=$true)]
        [string]$InputDir
    )
    $DtFile = ($InputDir + "\DataTable_" + $Name + ".xml")
    $dataset = New-Object Data.DataSet
    [void]$dataset.ReadXml($DtFile, [Data.XmlReadMode]::ReadSchema)
    $Dt = $dataset.Tables[0]
    return @(,$Dt)
}

Notice the return command "return @(,$Dt)" actually returns the Datatable instead of just the DataRows. Using this method writes the data and schema to a file which can easily be read back in.  The result is as if the Datatable was filled by actually querying the database.

See this for a nice way to fill your datagrids from a SQL Server database: Object Oriented Approach to PowerShell and Sql Server

Feel free to use/critique/comment.

Dustin


First two comments:



Available Blog posts:






If you like this site, help us out.
Spread the word and share it with others!