Use powershell for Excel

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #5745

    by leitsu at 2013-04-01 16:33:35

    Hi
    Does anyone know how to write a loop to go through first column cell 2 – 8 and output second column. See attached excel spreadsheet screenshot.

    The output I want:
    Tier1 Front 27
    Tier1 Back 27
    Tier2 Front 80
    Tier1A Front 10
    Tier1b Front 10
    Tier2A 128
    Tier2B 128

    The problem is the number of cells in first column might expand. It can be cell 2 – 9 or cell 2 – 15.

    by ps_gregg at 2013-04-01 20:09:06

    Hi Leitsu,

    Is this an Excel spreadsheet (XLS) or a CSV file? Can you remove anything that is company specific or confidential from the source file and attach it to the post, like you did on your previous CSV question? I'm sure that would help us to better help you. Thanks.

    by leitsu at 2013-04-01 22:30:20

    I have uploaded the file thanks

    by nohandle at 2013-04-02 02:02:20

    In the very basic approach you can do it like this:
    $data = Import-Csv -Path C:\temp\book1.csv
    #data are indexed from 0 here so i need to lower the index by 2,
    #so row 2 to row 8 becomes 0 to 6
    $data[0..6] | select subnet, "number of guests"

    by nohandle at 2013-04-02 02:05:32

    If you play with more data you may consider "indexing" the rows appropriately. Here I added 'row' property that reflects the row number in Excel. I could reorder the data do some statistics, and then reorder again. Or I can use it to do the task you requested using the where-object:
    $data = Import-Csv -Path .\book1.csv
    $indexedData = $data |
    foreach -Begin {$row = 2 } -Process {
    $_ | add-member -name 'Row' -MemberType NoteProperty -Value ($row++) -PassThru
    }

    $indexedData | where {($_.row -ge 2) -and ($_.row -le 8)} | select subnet, "number of guests"

    by leitsu at 2013-04-02 14:51:56

    Unfortunately thats not exactly what I want. the number of cells in the first column might expand. It can be cell 2 – 9 or cell 2 – 15.
    Is it possible to include a statement so the loop checks for the first letter "T"?

    by AlexBrassington at 2013-04-03 00:26:38


    $data | where { $_.subnet -like "T*"} | select subnet, "number of guests"

    You can indeed, a where clause will allow you to select only items that begin with T. Or to extend nohandle's example:

    $indexedData | where {($_.row -ge 2) -and ($_.row -le 8) -AND ($_.subnet -Like "T*")} | select subnet, "number of guests"

You must be logged in to reply to this topic.