PowerShell export data to file-John Liu Blog

This example PowerShell script exports data from SQL table into files and then zip them together.
Clear-Host

function ExportDataToFile
{
param(
    [string]$instancename,
    [string]$dbname,
    [string]$flowname,
    [string]$clientname,
    [string]$filepath
    )

$guid = @()
$guid_replace = @()

if ([string]::IsNullOrEmpty($filepath))
{
    throw "filepath is rquired but not specified!"
}

if ($filepath -match "\\$")
{
    $filepath = $filepath.Substring(0,$filepath.Length-1)
}

#connection to SQL
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=" + $instancename + ";Database=" + $dbname + ";Integrated Security=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "DECLARE @XML XML;
SELECT @XML = FileContent
FROM FlowFile F INNER JOIN FlowList L ON F.FlowID = L.ID WHERE L.FlowName = '$flowname' AND FileName = 'solution.xml';
SELECT Guid = REPLACE(REPLACE(a.value('@id','varchar(128)'),'{',''),'}','')
FROM @XML.nodes('/ImportExportXml/SolutionManifest/RootComponents/RootComponent') R(a);

SELECT Placeholdername FROM FlowList WHERE FlowName = '$flowname';

SELECT FileName, FileContent, FolderName FROM FlowFile F INNER JOIN FlowList L ON F.FlowID = L.ID WHERE L.FlowName = '$flowname';
"

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$Dataset = New-Object System.Data.DataSet

$SqlAdapter.Fill($Dataset) | Out-Null

if($Dataset.Tables[1].Rows.Count -eq 0)
{
    throw "Flow $flowname not found!"
}

if($Dataset.Tables[0].Rows.Count -eq 0)
{
    throw "solution.xml not found for flow $flowname!"
}

if($Dataset.Tables[2].Rows.Count -lt 4)
{
    throw "Number of files doesn't look right for flow $flowname!"
}

foreach($row in $Dataset.Tables[0].Rows)
{
    $guid += $row["Guid"]
    $guid_replace += [guid]::NewGuid().ToString()
}

foreach ($row in $Dataset.Tables[1].Rows)
{
    $placeholdername = $row["Placeholdername"]
}

$parentfilepath = $filepath

$filepath = $parentfilepath + "\" + $flowname.Replace($placeholdername.Replace(" ",""),$clientname.Replace(" ","")) + "_1_0_0"

if (Test-Path -PathType Container $filepath) {
    Remove-Item -LiteralPath $filepath -Force -Recurse | Out-Null  #use Out-Null to stop display newly create folder info
}

foreach ($row in $Dataset.Tables[2].Rows)
{
    $filepathtmp = $filepath
    if (-NOT [string]::IsNullOrEmpty($row["FolderName"]))
    {
        $filepathtmp = $filepath + "\" + $row["FolderName"]
    }
    
    if (!(Test-Path -PathType Container $filepathtmp)) {
        New-Item -ItemType Directory -Path $filepathtmp | Out-Null  #use Out-Null to stop display newly create folder info
    }

    if ($row["FileName"] -match "[.*]")
    {
        $filename = $filepathtmp + "\" + $row["FileName"].replace("[","``[").replace("]","``]")
    }
    else {
        $filename = $filepathtmp + "\" + $row["FileName"]
    }
    
    $filename = $filename.replace("ClientNameToReplace",$clientname)
    $filecontent = $row["FileContent"].replace("ClientNameToReplace",$clientname)
    for($i=0; $i -le $guid.Length-1; $i++)
    {
        $filename = $filename.Replace($guid[$i],$guid_replace[$i])
        $filename = $filename.Replace($guid[$i].ToUpper(),$guid_replace[$i].ToUpper())
        $filecontent = $filecontent.Replace($guid[$i],$guid_replace[$i])
        $filecontent = $filecontent.Replace($guid[$i].ToUpper(),$guid_replace[$i].ToUpper())
    }
    
     $filecontent | Out-File -FilePath $filename -Encoding ascii

     if($row["FileName"].Contains("["))
     {
        $renamefilename = $row["FileName"].replace("ClientNameToReplace",$clientname)
        "rename $filename $renamefilename" | cmd | Out-Null
     }
}

$destinationpath = "$parentfilepath\" + $flowname.Replace($placeholdername.Replace(" ",""),$clientname.Replace(" ","")) + "_1_0_0.zip"

Compress-Archive -Path "$filepath\*" -DestinationPath $destinationpath -Force

if (Test-Path -PathType Container $filepath) {
    Remove-Item -LiteralPath $filepath -Force -Recurse | Out-Null  #use Out-Null to stop display newly create folder info
}


}

ExportDataToFile "laptop\sqldev_2019" "_PAFlowMaster" "ClientNameToReplaceImportAutomation" "New company" "c:\test"
FEATURED TAGS
