We help IT Professionals succeed at work.

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

2,718 Views
Last Modified: 2016-10-20
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

Dale HarrisProfessional Services Engineer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
MikeSecurity

Author

Commented:
Subsun, your powershell script perfect ... The output is what I am looking for thank you for your help.....
Dale HarrisProfessional Services Engineer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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?