Ragav Ragav - 1 year ago 131
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{
$cmd = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
$dt = New-Object System.Data.DataTable
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 Source

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")