How to merge a few excel files into one (Python+Excel method #2, w/xlwings)
In a min, you can data manipulation as Excel & Python Expert
Step 1
- Installation of free python tools for MacBook/Windows using pycharm program with anaconda
→ a link for installation guide
Step 2
- Installation python plug-ins for Excel
→ a link for installation guide - Step by Step
Step 2–1. Move to terminal in PyCharm
Step 2–2. Install plug-in using a comment : pip install package-name (xlwings-xxxx, pandas)
Example : pip install xlwings
Step 3
- Writing a source Code (Sample) → a video link for installtion guide (tbd)
Step 3–1. Creation three excel files to merge as one file, Open those files then run python code to merge
tmp1.xlsx, tmp2.xlsx, tmp3.xlsx → merged_one.xlsx
→ a sample code
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
wb.close()
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)