Membership Membership:
Latest New User Latest: vpros
Past 24 Hours Past 24 Hours: 0
Prev. 24 Hours Prev. 24 Hours: 0
User Count Overall: 137

People Online People Online:
Visitors Visitors: 102
Members Members: 0
Total Total: 102

NakedMCSE Computer Tech Support Forums

So you have passed all of the exams and you are now out in the real world of windows servers, linux servers and freebsd servers faced with a tech support problem you have never seen before...

Find free online tech support in the forums below, respecting that advice is free, but work beyond advice is not...

The General forum is provided for off topic discussion.

You need a user account to post - please Register or Login.

Online Tech Support Forums
 
  Forum  Microsoft Tech ...  Windows Server ...  Practical powershell to iterate over a folder of spreadsheets
Previous Previous
 
Next Next
New Post 5/5/2011 4:10 PM
Informative
  Walker
119 posts
www.lairdscomputer.com
10th Level Poster


Practical powershell to iterate over a folder of spreadsheets 

I was given a task of taking a folder full of spreadsheets recently and amalgamating the information contained in them into a summary sheet.  Now there were a few thousand spreadsheets, so doing it by hand would have been somewhat time consuming.

There were only a couple of variations on the sheets as to where the data we needed was held.  It was either in cell F2 or cell F3, split across four sheets.

I came up with the following powershell script to open the sheets, extract the data and push it out into a CSV file:

$xl = new-object -com Excel.Application                   #open excel
write 'CompanyName, Apps, Tech, NatApps, NatTech' > _coupons.csv      #write headers
foreach ( $wbk in ( ls *.xls | where {$_.LastWriteTime -gt '12/31/2007'} ) )  #just do files from 2008 onwards
{
$xl.Workbooks.open($wbk,2,$true) | out-null               #drop the object info output
$sht1f2 = $xl.Worksheets.Item(1).Cells.Item(2,6).Value()  #Sheet 1 F2 - Apps
$sht1f3 = $xl.Worksheets.Item(1).Cells.Item(3,6).Value()  #Sheet 1 F3
$sht2f2 = $xl.Worksheets.Item(2).Cells.Item(2,6).Value()  #Sheet 2 F2 - Tech
$sht2f3 = $xl.Worksheets.Item(2).Cells.Item(3,6).Value()  #Sheet 2 F3
$sht3f2 = $xl.Worksheets.Item(3).Cells.Item(2,6).Value()  #Sheet 3 F2 - Nat Apps
$sht3f3 = $xl.Worksheets.Item(3).Cells.Item(3,6).Value()  #Sheet 3 F3
$sht4f2 = $xl.Worksheets.Item(4).Cells.Item(2,6).Value()  #Sheet 4 F2 - Nat tech
$sht4f3 = $xl.Worksheets.Item(4).Cells.Item(3,6).Value()  #Sheet 4 F3
$sht1totl = $sht1f2 + $sht1f3                             #Total Sheet 1
if ($sht1totl -eq $null) {$sht1totl = 0}
$sht2totl = $sht2f2 + $sht2f3                  #Total Sheet 2
if ($sht2totl -eq $null) {$sht2totl = 0}
$sht3totl = $sht3f2 + $sht3f3                  #Total Sheet 3
if ($sht3totl -eq $null) {$sht3totl = 0}
$sht4totl = $sht4f2 + $sht4f3                  #Total Sheet 4
if ($sht4totl -eq $null) {$sht4totl = 0}
$out = ''                                                 #start with blank output
$out = $wbk.Name.TrimEnd('.xls')                          #output company name,
$out = $out -Replace(",","_")                  #Remove commas
$out = $out + ',' + $sht1totl + ',' + $sht2totl + ','     #output apps and tech
$out = $out + $sht3totl + ',' + $sht4totl                 #output natapps and nattech
write $out >> _coupons.csv                                #write line
$xl.Workbooks.Close()                                    #close sheet without prompt for save
}

Turned out to be a surprisingly short piece of code for what it did.

Walker
www.lairdscomputer.com

 
Previous Previous
 
Next Next
  Forum  Microsoft Tech ...  Windows Server ...  Practical powershell to iterate over a folder of spreadsheets
Send me One Million FREE Guaranteed Visitors