Search code examples
pythonconcatenationworksheet

Concatenate excel files with all their sheets Python

I have two excel files with the same name saved under different directories, the first file has two sheets Rapport détaillé and Délai de reconnection and the second file has one sheet named Rapport détaillé.

I need to concatenate these two files:

  1. Concatenate the sheets with the same name
  2. Keep the second sheet from the first file

so the output excel file needs to have two sheets, the first is Rapport détaillé and the second is Délai de reconnection.

My code only concatenates the two first sheets, t does not take under consideration the name of the sheet.

import glob
import pandas as pd
import os,sys,inspect

d2 = 'C:/Users/khouloud/Desktop/cleanVersion/Reports'
d1 = 'C:/Users/khouloud/Desktop/Reports'

output_dir =FinalResult

file_d1 = glob.glob(d1 + "/*.xlsx")
file_d2 = glob.glob(d2 + "/*.xlsx")
i=0
#  
for file_d1 in glob.glob(d1 + "/*.xlsx"):
    fileName_d1 = os.path.splitext(os.path.splitext(os.path.basename(file_d1))[0])[0]       
    for file_d2 in glob.glob(d2 + "/*.xlsx"):
        fileName_d2 = os.path.splitext(os.path.splitext(os.path.basename(file_d2))[0])[0]
        if fileName_d1 == fileName_d2:
            i+=1
            fileName_1=d1+'/'+fileName_d1+'.xlsx'
            df1 = pd.read_excel(fileName_1, header=0, index= False)
            fileName_2=d2+'/'+fileName_d2+'.xlsx'
            df2 = pd.read_excel(fileName_2, header=0, index= False)
            df = pd.DataFrame(np.concatenate([df1.values, df2.values]), columns=df2.columns)
            print('**********************************************')
            print(df)
            print('**********************************************')
            df.to_excel(FinalResult+'/'+fileName+'/'+fileName_d1+'.xlsx', index = False, header=True)
Solution

  • Have you tried something this?

    rd1 = pd.read_excel(filename1, sheet_name = 'Rapport détaillé')
ddr = pd.read_excel(filename1, sheet_name = 'Délai de reconnection')
rd2 = pd.read_excel(filename2, sheet_name = 'Rapport détaillé')
rd_comb = pd.concat([rd1,rd2])

writer = pd.ExcelWriter(target_file_name, 
engine='xlsxwriter')
rd_comb .to_excel(writer, sheet_name = 'Rapport détaillé')
ddr.to_excel(writer, sheet_name = 'Délai de reconnection')
writer.save()

    In the past I've had issues with sheetname vs sheet_name depending on versions.