Use powershell for Excel

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"