Question

Python Script:

Question: Please edit my script for it to get to the right directory and/or folder. How to get the right files in the folder to display under files. I don't want anymore errors. Thanks-

#Program to combine multiple excel file into one master file/spreadsheet

#import library

import os
import pandas as pd
cwd = os.path.abspath(")
files = os.listdir(cwd)

#Combine multiple excel files
#This blocks of code will loop through files in directory/folder and append/merge .xlsx files

df = pd.DataFrame()
for file in files:
   if file.endswith('.xlsx'):
       df = df.append(pd.read_excel(file),ignore_index=True

#Display first 5 rows of data/records
df.head()

#Display total columns and rows
df.shape()

df.to_excel('Output.xlsx')

Answer 1

To get your current working directory path

• Navigate to your folder and type pwd.
• Note that all xsls files should have same header which column are to be appended else we end up having a new column for every new column.

Code

import os
import pandas as pd

#change this to your actual path this was mine
path = os.path.abspath("/home/geek/Downloads/SHEETS")
files = os.listdir(path)

dataframe = pd.DataFrame()
for file in files:
    if (file.endswith('.xlsx')):
        dataframe = dataframe.append(pd.read_excel(file),ignore_index=True)
                    
#Display first 5 rows
print(dataframe.head())

#Display total columns and rows
print(dataframe.shape)

dataframe.to_excel('Output1.xlsx',index=False)

Screenshots

Input Sheets

Output