How to concatenate three excels files xlsx using python?

Question

Hello I would like to concatenate three excels files xlsx using python.

I have tried using openpyxl, but I don't know which function could help me to append three worksheet into one.

Do you have any ideas how to do that ?

Thanks a lot

Answer 1

I'd use xlrd and xlwt. Assuming you literally just need to append these files (rather than doing any real work on them), I'd do something like: Open up a file to write to with xlwt, and then for each of your other three files, loop over the data and add each row to the output file. To get you started:

import xlwt
import xlrd

wkbk = xlwt.Workbook()
outsheet = wkbk.add_sheet('Sheet1')

xlsfiles = [r'C:\foo.xlsx', r'C:\bar.xlsx', r'C:\baz.xlsx']

outrow_idx = 0
for f in xlsfiles:
    # This is all untested; essentially just pseudocode for concept!
    insheet = xlrd.open_workbook(f).sheets()[0]
    for row_idx in xrange(insheet.nrows):
        for col_idx in xrange(insheet.ncols):
            outsheet.write(outrow_idx, col_idx, 
                           insheet.cell_value(row_idx, col_idx))
        outrow_idx += 1
wkbk.save(r'C:\combined.xls')

If your files all have a header line, you probably don't want to repeat that, so you could modify the code above to look more like this:

firstfile = True # Is this the first sheet?
for f in xlsfiles:
    insheet = xlrd.open_workbook(f).sheets()[0]
    for row_idx in xrange(0 if firstfile else 1, insheet.nrows):
        pass # processing; etc
    firstfile = False # We're done with the first sheet.

Answer 2

You can simply use pandas and os library to do this.

import pandas as pd
import os
#create an empty dataframe which will have all the combined data
mergedData = pd.DataFrame()
for files in os.listdir():
    #make sure you are only reading excel files
    if files.endswith('.xlsx'):
        data = pd.read_excel(files, index_col=None)
        mergedData = mergedData.append(data)
        #move the files to other folder so that it does not process multiple times
        os.rename(files, 'path to some other folder')

mergedData DF will have all the combined data which you can export in a separate excel or csv file. Same code will work with csv files as well. just replace it in the IF condition