Ragav Ragav - 4 months ago 64
SQL Question

Powershell: How to Parse the Multi line String as a String parameter?

As a newbie in powershell, im trying to read thru a folder which has multiple sql files and iterate them through poweshell scripts read the data from oracle and export to CSV.

If my sqlfile has a single line statement no issues with the code, its working fine, If my sql file has multiple line statement - as always it has,

the powershell errors out saying

"Get-DataTable : Cannot process argument transformation on parameter 'sql' Cannot convert value to type System.String."


could you please help me how to resolve this issue? Below my code snapshot.

function Get-DataTable{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[Oracle.DataAccess.Client.OracleConnection]$conn,
[Parameter(Mandatory=$true)]
[string]$sql
)
$cmd = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
$dt = New-Object System.Data.DataTable
[void]$da.Fill($dt)
return ,$dt
}

foreach ($file in Get-ChildItem -path $ScriptsDirectory -Filter *.sql | sort-object -desc )
{
$SQLquery = get-content "$ScriptsDirectory\$file"
echo $SQLquery
$fileName = $file.name.split(".")[0]
$dt = Get-DataTable $conn $SQLquery
Write-Host "Retrieved records:" $dt.Rows.Count -ForegroundColor Green
$dt | Export-Csv -NoTypeInformation -LiteralPath $WorkingDirectory\$fileName.csv
Write-Host "Output Written to :" $WorkingDirectory\$fileName.csv -ForegroundColor Green }

Answer

Get-Content returns an array of lines. If you're using PowerShell v3 or higher you can use the -Raw parameter to read the file as one big string:

$SQLquery = get-content "$ScriptsDirectory\$file" -Raw

Alternatively you could re-join the array with line endings:

$SQLquery = $SQLquery -join "`r`n"

Or you can read the file all at once with .net classes:

$SQLquery = [System.IO.File]::ReadAllText("$ScriptsDirectory\$file")