Using a Normal For Loop with CSV (Import-CSV)

Question

I am working on a PowerShell script that will import a CSV file containing server information (including server name, instance, status, and database names) using the Import_Csv cmdlet. After importing the file, the script will loop through the file, connect to each server/instance, and add a user to each database.

What I am trying to figure out is how to be able to use a normal FOR loop within the script instead of a FOREACH loop. I want to limit the number of times I have to connect to each server by looping through the list of servers/databases. What I want to check is if the current server/instance is the same as the next server/instance in the file, keep the connection open. For example, if I have the following data in the file:

server1,instance1,online,database1
server1,instance1,online,database2
server2,instance1,online,database1
server2,instance1,online,database2

When I am looking at the data on the first line, before closing the connection to server1\instance1, I want to check if the same data is on the second line so that I do not have to close the current connection and then reconnect to the same server a moment later.

My question is, is there a way to access the next element of a CSV file in PowerShell within the FOREACH loop, or should I use a FOR loop? If I use a FOR loop instead, what is the best way to access each field?

Here is what I currently have with comments in the foreach loop of what I would like to happen:

Param(
    [Parameter(Mandatory=$true,position=0)]
    [string]$serverListFilePath = $( Read-Host "Server List File Path: " )
)

Write-Host "File path: $serverListFilePath"
$serverListArray = Import-Csv -Path $serverListFilePath # Import the server list CSV file and store it into a CSV object
#$serverListArray

foreach ($item in $serverListArray) # Basically all the substance/work of this script is going to be done in this loop since it needs to go through each server in the file one-by-one
{
    Try
    {
        $serverNameCSV = $item.server_Name
        $serverInstanceCSV = $item.Server_Instance
        $serverStatusCSV = $item.Server_Status
        $databaseNameCSV = $item.Database_Name


        # If no connection to server
             # Create connection to server/instance
        # Run SQL queries
        # If (<current server\instance> -ne <next server\instance>)
             # Close connection to server/instance
             # continue
        # else
             # continue


    } # End Try
# Catch

Any ideas on how to go about this will be much appreciated.

have you thot about using Group-Object to group the connections? that will let you process all accesses to the same system with one call. — Lee_Dailey
– Lee_Dailey, Commented Jun 18, 2019 at 16:53

jrider · Accepted Answer · 2019-06-18 15:29:05Z

1

Example of how to loop through a CSV using a For loop:

$serverListArray = Import-Csv -Path $serverListFilePath
#will loop until $x is no longer less than 10
for ($x=0;$x -lt 10; $x++)
{
    #call property by index
    $serverListArray[$x].Server_name
    $serverListArray[$x].Server_instance
    #etc... 
}

answered Jun 18, 2019 at 15:29

jrider

1,6601 gold badge11 silver badges27 bronze badges

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

2 Comments

E. Kelly Over a year ago

Thanks for that. I am going to try both solutions presented here, but I am glad I can finally see the syntax for using a normal For loop with the CSV. After a bit of searching, I was only getting results with the standard Foreach method.

jrider Over a year ago

@E.Kelly Good deal. If you need additional clarification. Or, further help, let me know.

Sprawl63 · Accepted Answer · 2019-06-18 15:23:37Z

1

Instantiate the server name variable outside of the for/foreach loop. Every time your program loops, compare the new value against the old value and if the values are different, reassign and connect to the new server.

I would also advise sorting the incoming data by server so you can limit the amount of times you need to repeatedly connect to the same data source.

answered Jun 18, 2019 at 15:23

Sprawl63

1231 silver badge8 bronze badges

Comments

Vivek Athalye · Accepted Answer · 2019-06-18 16:12:38Z

0

You can keep a reference to previous server name and previous connection. Then check the previous server name with current server name. If its different then close the previous connection (if anything is open).

Also, after the foreach loop check if the previous connection is open, if yes, close it. This will be the connection to the last server.

answered Jun 18, 2019 at 16:12

Vivek Athalye

2,9792 gold badges24 silver badges34 bronze badges

Collectives™ on Stack Overflow

Using a Normal For Loop with CSV (Import-CSV)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related