Get the row count of multiple excel spreadsheet files

Here I have many excel spreadsheet files within a folder as seen below...

The task is to return the number of rows in each of the excel files. I can go manually, open each file, scroll to the bottom and note down the row number. That will be cumbersome and time consuming given that number of files I have to cover.

So, I have to write a simple script in python that will handle this boring task accordingly as follow:-

Step 1: First things first, lets find a way to read all the .xlsx files. Here I used the glob module to handle this.

import glob

folder_xlsx = r"C:\Users\Yusuf_08039508010\Desktop\my-xlsx-folder"

# read all the individual order xlsx files
xlsx_files = glob.glob(folder_xlsx + '/*.xlsx')

what I have above is a list that contains path to all the excel files in the folder. Lets move on...


Step 2: Next step is to read each excel file into a pandas dataframe and use a function to count the number of rows in the dataframes. There are many functions to count the number of rows as seen below, but I will use this function 'len(df.index)'.

Here is the solution for the fisrt dataframe.

df = pd.read_excel(xlsx_files[0])

row_count = len(df.index)

To do for the whole excel files, we just write a for loop and save the into a list as seen below. Noticed that I used rsplit() function to get the file names to print it along its corresponding row count.

import pandas as pd

row_count_list = []
for xls_file in xlsx_files:
    df = pd.read_excel(xls_file)
    row_count = len(df.index)
    
    file_name = xls_file.rsplit('\\', 1)[1]
    
    file_details = file_name, row_count
    
    row_count_list.append(file_details)
    
print (row_count_list)

That is it!


P.S: You could easily extend the script above to do many other thing with the files. An example will be to merge all the files into one file using the pandas concat() method. So, instead of appending the file names and the row counts, we will simply append the dataframe as seen below.

df_list = []
for xls_file in xlsx_files:
    df = pd.read_excel(xls_file)
    
    df_list.append(df)
    
merge_df = pd.concat(df_list)