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

Question

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    }

get-content "$ScriptsDirectory\$file" -> get-content "$ScriptsDirectory\$file" -Raw — user4003407
– user4003407, Commented Aug 1, 2016 at 17:06

briantist · Accepted Answer · 2016-08-01 17:08:06Z

2

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

answered Aug 1, 2016 at 17:08

briantist

48.3k6 gold badges94 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related