Need a PowerShell/VBS script to Count No# of rows in Mutiple Excel spreadsheets

Mike
Mike used Ask the Experts™
on
Hello Experts,

I am looking for a script  that can count the number of  rows being used in folder full of  excel documents ( 40-60 spreadsheets). I need the script to produce a output.csv file with the name of the file and the no# of rows being used. Does anybody have Powershell or VB script that can handle this task.......

example

Name of File       Number of Rows Used
File name               No# of Rows used
File name               No# of Rows used
File name               No# of Rows used
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale HarrisProfessional Services Engineer

Commented:
Here's what I found from the Scripting Guy which I then modified to create your script request.

http://blogs.technet.com/b/heyscriptingguy/archive/2010/04/05/hey-scripting-guy-april-5-2010.aspx

$path = "C:\fso"
$excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse
$excel = New-Object -comobject Excel.Application
$excel.visible = $false
"Name;Rows" >> "RowTotal.txt"
foreach($excelSheet in $excelSheets)
{
 $workbook = $excel.Workbooks.Open($excelSheet)
 #"There are $($workbook.Sheets.count) sheets in $excelSheet"
$RowTotal = 0
 For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
 {
  $worksheet = $workbook.sheets.item($i)
  $rowMax = ($worksheet.usedRange.rows).count
  $columnMax = ($worksheet.usedRange.columns).count
$RowTotal += $rowMax

  $worksheet = $rowmax = $columnMax = $row = $column = $formula = $null
 } #end for
"$($excelsheet.fullname);$RowTotal" >> "RowTotal.txt"
 $workbook.saved = $true
 $workbook.close()
} #end foreach
$excel.quit()
 $excel = $null
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()

Open in new window


Note: it doesn't create a CSV but you can easily export into CSV in the script, or like I prefer: importing into an excel, separated by semicolon.  Just open the excel, click on the Data Tab, click on From Text, and just follow the wizard.

Regards,

DH
IT Infrastructure Architect
Commented:
Check this..
input - $path = "C:\Test"
outout - $Report = "C:\Test\report.csv"

Clear-Host
$Report = "C:\Test\report.csv"
$path = "C:\Test"
[Array]$Results = $null
$excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse
$excel = New-Object -comobject Excel.Application
$excel.visible = $false

foreach($excelSheet in $excelSheets)
{
 $workbook = $excel.Workbooks.Open($excelSheet)
 $rowCount = $null
For ($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
 {
  $worksheet = $workbook.sheets.item($i)
  $rowMax = ($worksheet.usedRange.rows).count
  $rowCount += $rowMax
  }
$Results += New-Object Psobject -Property @{
    "File Name"=$excelSheet.Name
    "Sheet Count"=$workbook.Sheets.count
    "Row Count"=$rowCount}
$excelSheet.Name
$workbook.Sheets.count
$rowCount
}
$excel.quit()
$Results | select "File Name","Sheet Count","Row Count" | Export-Csv $Report -NoTypeInformation

Open in new window

MikeSecurity

Author

Commented:
Subsun, your powershell script perfect ... The output is what I am looking for thank you for your help.....
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Dale HarrisProfessional Services Engineer

Commented:
Amstoots,

Did you happen to see my response as well?

DH
MikeSecurity

Author

Commented:
yes I did DaleHarris , sorry I didn't respond to yours.... Subsun Powershell script was more streamlined and and was able to use Export-CSV to output the Resolute. I did attempt to use your but ran in few problems... thank you for your time you have put in this.......
Dale HarrisProfessional Services Engineer

Commented:
Okay no problem.  Glad you got it solved quickly.
MikeSecurity

Author

Commented:
I have one more question on this script guys...... How can you tell the script to look for a pacific name in a list of spreadsheets.... For example, I need to look for number of spreadsheet that have the following file name "Local Administrator Access by All Servers MM-DD-YYYY.xls". I would like to add to the script to only search for those files in the folder with the file name "Local Administrator Access by All Servers MM-DD-YYYY.xls" and use a wild card "*" for the date (i.e. Local Administrator Access by All Servers *.xls.) Would this be possible.....?
Subash SundharanIT Infrastructure Architect

Commented:
Yes you can do that..
Get-Childitem -Include "Local *.xls","Local *.xlsx" -Recurse

Open in new window

MikeSecurity

Author

Commented:
Perfect, thanks for the additional Help.... Subsun  .....  :)
Subash SundharanIT Infrastructure Architect

Commented:
You are welcome!!

Commented:
Hi,

Thank you subsun for posting this.. It worked accurately on most files but its is displaying the wrong number for a couple of them. Any idea as to why?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial