How to Query a CSV file

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 5 months ago.

  • Author
  • #24944
    Profile photo of Sanjaya Luintel
    Sanjaya Luintel

    I recently wrote a script that archives IIS logs into a remote server. The script runs by importing a module that mainly contains functions to copy, zip and remove files. I am populating the script the using a CSV File. which looks like this

    [attachment file="Servers.csv"]

    In the CSV

    The cutoffdate in the CSV represents the retention period for the IISLogs.
    I do not want to run the delete action if the copy action returns an error. So, I added a column name called ActionID and PreCheckForActionID.
    Later, when the script runs, for every object, I plan to add a new column in the imported CSV called results.. Hence, Before a remove action takes place, I would like to check for the PreCheckForActionID , filter the Action associated with it and check it the action was a success or returned an error.

    This is what I have so far and I am completely lost, since I do not know how to go about getting the ActionID related to the PreCheckForActionID object.

    {$Newlist = Import-Csv C:\powershell\Servers.csv | Add-Member -MemberType noteproperty -Name Results -Value "success" -PassThru | Select-Object ActionID, Action, ServerName, PreCheckForActionID, Results

    $verynew = $Newlist | Select-Object -Property ActionID, Results, PreCheckForActionID | Select-Object -Property ActionID,PreCheckForActionID

    foreach($item in $verynew)



    Can someone help me out with the next step on how to correlate my PreCheckForActionID with an ActionID?

  • #24959
    Profile photo of Rob Simmers
    Rob Simmers

    I think this is what you are trying to do. Read through this and validate this the logic:

    $csv = @()
    $csv += New-Object -TypeName PSObject -Property @{ServerName="Server1";ActionID=1;PreReqActionID=$null}
    $csv += New-Object -TypeName PSObject -Property @{ServerName="Server2";ActionID=2;PreReqActionID=1}
    $csv += New-Object -TypeName PSObject -Property @{ServerName="Server3";ActionID=3;PreReqActionID=$null}
    $csv += New-Object -TypeName PSObject -Property @{ServerName="Server4";ActionID=4;PreReqActionID=2}
    #Another method to add a column is just to use Select-Object.  Add-Member you can assign a default value to the objects
    $csv = $csv | Select ServerName, ActionID, PreReqActionID, Result
    $results = foreach ($item in $csv) {
        #if the PreReqActionID is not null
        if ($item.PreReqActionID) {
            #Cheat a bit and use Write-Host to not return this to the output and result and to illustrate what is happening....
            Write-Host ("Running Pre-Req Action {0} on server {1} before running action {2}" -f $item.PreReqActionID, $item.ServerName, $item.ActionID)
            #Create a new record for the result of the pre-reqID since you are running another action and want to 
            New-Object -TypeName PSObject -Property @{ServerName=$item.ServerName;ActionID=$item.ActionID;PreReqActionID=$item.PreReqActionID;Result="Failed"}
       #Cheat a bit and use Write-Host to not return this to the output and result and to illustrate what is happening....
       Write-Host ("Running Action {0} on server {1}" -f $item.ActionID, $item.ServerName)
       #Assign a success value to Result 
       $item.Result = "Success"
       #We're generating a new object, so enumerate the current row (item) to the $result variable
    $results | Format-Table -AutoSize


    Running Action 1 on server Server1
    Running Pre-Req Action 1 on server Server2 before running action 2
    Running Action 2 on server Server2
    Running Action 3 on server Server3
    Running Pre-Req Action 2 on server Server4 before running action 4
    Running Action 4 on server Server4
    ServerName ActionID PreReqActionID Result 
    ---------- -------- -------------- ------ 
    Server1           1                Success
    Server2           2 1              Failed 
    Server2           2 1              Success
    Server3           3                Success
    Server4           4 2              Failed 
    Server4           4 2              Success

You must be logged in to reply to this topic.