How to merge a few excel files into one (Python+Excel method #2, w/xlwings)

  • Installation of free python tools for MacBook/Windows using pycharm program with anaconda
    → a link for installation guide
  • Installation python plug-ins for Excel
    → a link for installation guide
  • Step by Step
  • Writing a source Code (Sample) → a video link for installtion guide (tbd)
import pandas as pd
from pathlib import Path
import numpy as np
import xlwings as xw

input_folder = '/Users/tippang/PycharmProjects/pythonProject2/tmp'
raw_data_dirpath = Path(input_folder)
excel_files = raw_data_dirpath.glob('tst*')

merge_df2 = pd.DataFrame()

for excel_file in excel_files:
wb = xw.Book(excel_file)
sheet = wb.sheets['sheet1']

df = sheet.range('A1').options(pd.DataFrame, expand='table', index=False).value


merge_df2 = merge_df2.append(df, ignore_index=True)

merged_excel_file = input_folder + 'integrated_file2.xlsx'
merge_df2.to_excel(merged_excel_file, sheet_name='1Q_Data', index=False)

print("crated file", merged_excel_file)



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store