Looping through csv file in powershell

Question

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

Immortal · Accepted Answer · 2017-04-24 17:13:52Z

2

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
}

edited Apr 24, 2017 at 17:13

Immortal

1,2161 gold badge21 silver badges39 bronze badges

answered Apr 22, 2017 at 15:58

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

Comments

Joel Coehoorn · Accepted Answer · 2017-05-11 13:43:13Z

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
}

Immortal · Accepted Answer · 2017-04-26 17:48:40Z

0

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

}

edited Apr 26, 2017 at 17:48

Immortal

1,2161 gold badge21 silver badges39 bronze badges

answered Apr 24, 2017 at 9:16

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Collectives™ on Stack Overflow

Looping through csv file in powershell

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related