Welcome › Forums › General PowerShell Q&A › Use powershell for Excel
This topic contains 0 replies, has 1 voice, and was last updated by Forums Archives 7 years ago.

AuthorPosts

January 1, 2012 at 12:00 am #5745
by leitsu at 20130401 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 128The 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 20130401 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 20130401 22:30:20
I have uploaded the file thanks
by nohandle at 20130402 02:02:20
In the very basic approach you can do it like this:
$data = ImportCsv 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 20130402 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 whereobject:
$data = ImportCsv Path .\book1.csv
$indexedData = $data 
foreach Begin {$row = 2 } Process {
$_  addmember name 'Row' MemberType NoteProperty Value ($row++) PassThru
}$indexedData  where {($_.row ge 2) and ($_.row le 8)}  select subnet, "number of guests"
by leitsu at 20130402 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 20130403 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" 
AuthorPosts
The topic ‘Use powershell for Excel’ is closed to new replies.