none
How do I iterate over each Row of a Table Storage using Powershell? RRS feed

  • Question

  • Hi

    This should be easy but I have spent hours on it today. All I want to do is iterate (like in a foreach or something) through each row of an Azure Table Storage in Powershell. I have this much code:

    $resourceGroup = "xxxxxxxxxxx"
    $storageAccount = "scadametrics"
    $tableName = "CompanyID"
    $SaContext = (Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
    $table = (Get-AzStorageTable -Name $tableName -Context $saContext).CloudTable

    Get-AzTableRow -table $table | ft

    This will print out each row, but I need to be able to read a value in each row so that I can assign it to $KeyName or something similar and then move on to the next row. I have googled for hours and can't find how to do this in PowerShell for Azure. 

    Thanks!
    Mike Kiser


    Mike Kiser

    Wednesday, September 18, 2019 9:50 PM

Answers

  • Hi

    Please find the below code helps to meet your requirement

    for($i=0;$i -lt $ds.Tables[1].Rows.Count;$i++)
    { 
      write-host "value is : $i $($ds.Tables[1].Rows[$i][0])"
    }

    Or You can even use Foreach Loop as it no need Count kind of calculations.

    foreach ($Row in $ds.Tables[1].Rows)
    { 
      write-host "value is : $($Row[0])"
    }

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, September 20, 2019 4:14 AM

All replies

  • Hi

    Please find the below code helps to meet your requirement

    for($i=0;$i -lt $ds.Tables[1].Rows.Count;$i++)
    { 
      write-host "value is : $i $($ds.Tables[1].Rows[$i][0])"
    }

    Or You can even use Foreach Loop as it no need Count kind of calculations.

    foreach ($Row in $ds.Tables[1].Rows)
    { 
      write-host "value is : $($Row[0])"
    }

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, September 20, 2019 4:14 AM
  • Thanks so much, Naveen,

    I tried it with my code as follows but I am getting the error:Cannot index into a null array.

    $resourceGroup = "xxxxxxxxxxx"
    $storageAccount = "xxxxxx"
    $tableName = "CompanyID"
    $SaContext = (Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
    $table = (Get-AzStorageTable -Name $tableName -Context $saContext).CloudTable

    Get-AzTableRow -table $table | ft


    foreach ($Row in $ds.table[1].Rows)

      write-host "value is : $($Row[0])"
    }

    Please help;

    Thanks

    Mike Kiser


    Mike Kiser

    Friday, September 20, 2019 2:35 PM
  • Hi

    You could use something to check if there are any matches at all before trying to access the index.

    Try something like below:

    if ($Search.Matches.Count -gt 0) {
    
    }

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by EMKISER Friday, September 20, 2019 3:46 PM
    • Unmarked as answer by EMKISER Saturday, September 21, 2019 12:21 AM
    Friday, September 20, 2019 2:39 PM
  • Hi and Thanks!

    I know there are 149 Rows and print them out right before I try to use 

    foreach ($Row in $ds.table[1].Rows)

      write-host "value is : $($Row[0])"
    }

    Do I have to define the ds (dataset) somewhere?

    CompanyCode CompanyID CompanyName                                FieldVisorConnecti
                                                                     onString
    ----------- --------- -----------                                ------------------
    fdl               114 FDL                                        Server=tcp:sfzwns…
    SUM               220 Summit Natural Resources                   Server=tcp:sfzwns…
    TRO               239 Trivista Operating LLC                     Server=tcp:sfzwns…
    STG               174 Stonegate                                  Server=tcp:sfzwns…
    TCO               245 Tall City III Exploration LLC              Server=tcp:sfzwns…
    SAGE              182 Sage Natural Resources                     Server=tcp:sfzwns…
    JNP               184 Juniper Resources                          Server=tcp:sfzwns…
    QBSQ              180 Quorum Quality Assurance (QA) Test Company Server=tcp:qbstes…
    PSC               252 Production Support Client                  Server=tcp:qbspro…
    KIM               243 Kimmeridge Operations, LLC
    wod               147 Wagner Oil DEVELOPMENT                     Server=tcp:qbstes…
    CATU              251 Catena Resources Test                      Server=tcp:qbstes…
    RAMT              229 Ram Energy TEST                            Server=tcp:qbstes…
    CCI               134 Castleton Commodities International LLC    Server=tcp:sfzwns…
    PDR               171 Paradox Resources                          Server=tcp:sfzwns…
    CAT               250 Catena Resources                           Server=tcp:sfzwns…
    NPC               185 New Prospect Company                       Server=tcp:sfzwns…
    ATR               183 Alta Resources                             Server=tcp:sfzwns…
    tay               113 Taylor Operating
    REV               232 Revolution Resources                       Server=tcp:sfzwns…
    cle               125 CleanIAN                                   Server=tcp:sfzwns…
    VAL               198 Valor Energy                               Server=tcp:sfzwns…
    PLYD              253 SK Plymouth DEV                            Server=tcp:qbstes…
    arr                88 Arrington
    abc                 8 ABC Oil                                    Server=tcp:sfzwns…
    QOE               244 Quorum Origin Environment                  Server=tcp:sfzwns…
    lin                83 DEF Oil                                    Server=tcp:srk8s1…
    SBTU              256 Sandbox Training - Test                    Server=tcp:qbstes…
    DLT               224 Daylight Petroleum TEST                    Server=tcp:qbstes…
    PIE               218 Pie Operating                              Server=tcp:sfzwns…
    ALQ               163 Alta Test Site                             Server=tcp:qbstes…
    PIET              219 Pie Operating Test                         Server=tcp:qbstes…
    toc               109 Travelers Oil                              Server=tcp:sfzwns…
    MDC               205 MDC Texas Energy, LLC                      Server=tcp:sfzwns…
    ero               151 Eagle Road Oil, LLC                        Server=tcp:sfzwns…
    TROU              240 Trivista Operating LLC UAT                 Server=tcp:qbstes…
    MDT               206 MDC Texas Energy, LLC TEST                 Server=tcp:qbstes…
    How               201 Howell Oil & Gas                           Server=tcp:sfzwns…
    mks                45 MKS Natural Gas
    ene                49 Enervest Operating                         Server=tcp:sfzwns…
    FDLU              179 FDL UAT                                    Server=tcp:qbstes…
    WRI               175 Warren Resources                           Server=tcp:sfzwns…
    ECA               160 FVTravelers Dev Site                       Server=tcp:qbstes…
    poll              133 Poller Visor, LLC
    GDS               112 GDSTB                                      Server=tcp:qbstes…
    RELT              227 Reliance Energy TEST                       Server=tcp:qbstes…
    CNX               241 CNX Resources                              Server=tcp:sfzwns…
    SCE               173 Scala Energy                               Server=tcp:sfzwns…
    TREU              237 Titan Rock E&P UAT                         Server=tcp:qbstes…
    kor                13 Kornye Tillman
    bhp               154 BHP Billiton                               Server=tcp:sfzwns…
    mrj                65 Marjac Oil Company, Inc.
    jma                26 JMA Energy
    QBSPS 

    Any suggestions? Thanks for your help; I need to get this working today.

    Thanks
    Mike


    Mike Kiser


    • Edited by EMKISER Friday, September 20, 2019 2:53 PM
    Friday, September 20, 2019 2:46 PM
  • Hi

    Could you share the the expectation, As per my understanding I think you are on the path of what you need rt.

    Please confirm.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by EMKISER Friday, September 20, 2019 3:46 PM
    • Unmarked as answer by EMKISER Saturday, September 21, 2019 12:22 AM
    Friday, September 20, 2019 3:38 PM
  • THANKS so much!
    I changed the code around and now there is no exception HOWEVER it is not printing out the value of $Row[1] . There are no errors or exceptions but it just goes to the Powershell cursor after the code and does not show the value of $Row[1]. That value is what I need.

    $resourceGroup = "EnhancedPoller"
    $storageAccount = "scadametrics"
    $tableName = "CompanyID"
    $SaContext = (Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
    $table = (Get-AzStorageTable -Name $tableName -Context $saContext).CloudTable

    Get-AzTableRow -table $table | ft

    $ds = new-object System.Data.DataSet
    $ds.Tables.Add("table")

    foreach ($Row in $ds.Tables[1].Rows)

      write-host "value is : $($Row[1])"
    }

    Thanks again!


    MIke


    Mike Kiser

    Friday, September 20, 2019 3:42 PM
  • Great !!

    please click the "Mark as Answer" initial reply post  click Answered and "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    • Edited by nkumar230 Friday, September 20, 2019 3:44 PM
    • Marked as answer by EMKISER Friday, September 20, 2019 3:48 PM
    • Unmarked as answer by EMKISER Friday, September 20, 2019 6:58 PM
    Friday, September 20, 2019 3:43 PM
  • Hi Naveen,

    Please read my last post on this thread. It seems to be working but not "printing" out the 

    write-host "value is : $($Row[1])"

    and this is the value that I need....how can I see the value? My entire code is listed above.

    Really appreciate your help!
    Mike Kiser


    Mike Kiser

    Friday, September 20, 2019 3:48 PM
  • Hi 

    Could you replacing the below expression and let me know how it goes

      write-host "value is : $($Row[1])"

    value is : $($Row.ColumnName)
    

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, September 21, 2019 4:31 AM