Write-PsqlDataTable.ps1
|
<#PSScriptInfo .VERSION 1.0 .GUID 5473d07f-0fd6-46f7-b25b-b3ac40e4eeb5 .AUTHOR Francisco Nabas .COMPANYNAME .COPYRIGHT .TAGS PostgreSQL .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES #> <# .DESCRIPTION Script to "Bulk" copy data into PostgreSQL database using the COPY statement and a csv file. Input is a PSCustomObject #> Function Write-PsqlDataTable { [CmdletBinding()] param( [Parameter (Position = 0, Mandatory = $true)] [string] $ServerInstance, [Parameter (Mandatory = $false)] [int] $Port=5432, [Parameter (Position = 1, Mandatory = $true)] [string] $Database, [Parameter (Position = 2, Mandatory = $true)] [string] $TableName, # The PSCustomObject NoteProperties must be ordered! Otherwise PgSQL COPY will fail! [Parameter (Position = 3, Mandatory = $true, HelpMessage = 'The PSCustomObject NoteProperties must be ordered! Otherwise PgSQL COPY will fail!')] [PSCustomObject] $Data, [Parameter (Position = 4, Mandatory = $false)] [string] $Username, [Parameter (Position = 5, Mandatory = $false)] [Security.SecureString] $Password, [Parameter (Mandatory = $false)] [string] $Docker ) $DBConn = New-Object System.Data.Odbc.OdbcConnection $BTSR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password) $PTP = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BTSR) if ($Username) { $DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$ServerInstance;Port=$Port;Database=$Database;Uid=$Username;Pwd=$PTP;" } else { $DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$ServerInstance;Port=$Port;Database=$Database;" } $DBConn.ConnectionString = $DBConnectionString try { $Columns = ($Data | Get-Member | Where-Object {$_.MemberType -eq 'NoteProperty'}).Name $Columns = [System.String]::Join(',',$Columns) $Data | Export-Csv $Env:TEMP\TempPsAd.csv -Delimiter ',' -NoTypeInformation if ($Docker) { docker cp $Env:TEMP\TempPsAd.csv postgres_docker:/media/TempPsAd.csv $DBConn.Open() $DBCmd = $DBConn.CreateCommand() $DBCmd.CommandText = @" COPY $TableName ($Columns) FROM '/media/TempPsAd.csv' DELIMITER ',' CSV HEADER "@ $DBCmd.ExecuteReader() $DBConn.Close() docker exec postgres_docker rm -rf /media/TempPsAd.csv Remove-Item $Env:TEMP\TempPsAd.csv -Force } else { $DBConn.Open() $DBCmd = $DBConn.CreateCommand() $DBCmd.CommandText = @" COPY $TableName ($Columns) FROM '$Env:TEMP\TempPsAd.csv' DELIMITER ',' CSV HEADER "@ $DBCmd.ExecuteReader() $DBConn.Close() Remove-Item $Env:TEMP\TempPsAd.csv -Force } } catch { Write-Error "$($_.Exception.Message)" continue } } |