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

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

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.

Hi Leitsu,by leitsu at 2013-04-01 22:30:20

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.

I have uploaded the file thanksby nohandle at 2013-04-02 02:02:20

In the very basic approach you can do it like this:by nohandle at 2013-04-02 02:05:32`$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"

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:by leitsu at 2013-04-02 14:51:56`$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"

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.by AlexBrassington at 2013-04-03 00:26:38

Is it possible to include a statement so the loop checks for the first letter “T”?

$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”