CODEWITHSUNDEEP
powershell
Immortal
Immortal

Reputation: 1180

Looping through csv file in powershell

I have a csv file that has been exported from a sql database. The file has 2 columns with 100 records: FunctionName and DateCreated

So I want to see each and every object that is referenced by every function in the database.

Here is my code I have written so far:

$Query=  "SELECT  space(iteration * 4) + TheFullEntityName + ' (' + rtrim(TheType) + ')', *
               FROM    dbo.fn_DependantObjects('$FunctionName',    1,     0)    
               ORDER BY ThePath"

please note: dbo.fn_DependantObjects, is a function I have already created.

$fileName = 'c:\CurrentDate.csv'
foreach ($pattern in (Import-Csv $filename | % {$_.FunctionName})) {
    Invoke-Sqlcmd -ServerInstance "ServerName" -Database "DatabaseName" -Query $Query

I am still new to powershell and would really appreciate if anyone can help me in any other way. I am stuck and this project is behind schedule.

Let me know.

Thanks Immortal

Upvotes: 1

Views: 571

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

Look at this line from your query:

FROM    dbo.fn_DependantObjects('$FunctionName',    1,     0)

It's trying to use string interpolation set the $FunctionName variable in the string. However, at the point where you run that code, $FunctionName doesn't have a value yet. It's trying to complete the string interpolation when you first assign it to the $Query variable, rather than saving the placeholder for each iteration through your loop.

To fix this, you want to move that string variable assignment code down into the loop:

$fileName = 'c:\CurrentDate.csv'
foreach ($pattern in (Import-Csv $filename | % {$_.FunctionName})) {
    $Query=  "SELECT  space(iteration * 4) + TheFullEntityName + ' (' + rtrim(TheType) + ')', *
               FROM    dbo.fn_DependantObjects('$FunctionName',    1,     0)    
               ORDER BY ThePath" 
    Invoke-Sqlcmd -ServerInstance "ServerName" -Database "DatabaseName" -Query $Query
}

Upvotes: 0

Esperento57
Esperento57

Reputation: 17462

with -format (-f) like it :

Import-Csv $filename | %{
    $Query=  "SELECT  space(iteration * 4) + TheFullEntityName + ' (' + rtrim(TheType) + ')', *
               FROM    dbo.fn_DependantObjects('{0}',    1,     0)    
               ORDER BY ThePath" -f $_.FunctionName
    Invoke-Sqlcmd -ServerInstance "ServerName" -Database "DatabaseName" -Query $Query

}

Upvotes: 0

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174445

In the $Query string, you use $FunctionName to refer to the function name, but in the foreach() loop you use $pattern - fix this first.

Then, make sure you assign the $Query string after $FunctionName has been assigned:

$fileName = 'c:\CurrentDate.csv'
foreach ($FunctionName in Import-Csv $filename | % {$_.FunctionName}) {
    $Query=  "SELECT  space(iteration * 4) + TheFullEntityName + ' (' + rtrim(TheType) + ')', *
               FROM    dbo.fn_DependantObjects('$FunctionName',    1,     0)    
               ORDER BY ThePath" 
    Invoke-Sqlcmd -ServerInstance "ServerName" -Database "DatabaseName" -Query $Query
}

Upvotes: 2

Related Questions