CODEWITHSUNDEEP
powershellcsv
E. Kelly
E. Kelly

Reputation: 117

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

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.

Upvotes: 0

Views: 246

Answers (3)

Vivek Athalye
Vivek Athalye

Reputation: 2979

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.

Upvotes: 0

jrider
jrider

Reputation: 1640

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... 
}

Upvotes: 1

Sprawl63
Sprawl63

Reputation: 123

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.

Upvotes: 1

Related Questions