Python Search Corresponding Data In Multiple Excel And Paste To A New Excel Worksheet
i have some excel files in a folder, there's already a worksheet call 'service' in each file Notes_111.xlsx Notes_222.xlsx Notes_888.xlsx Workflow : I want to open each .xlsx file
Solution 1:
Use pandas its much easier to manipulate, I believe it uses openpyxl under the hood anyway.
import glob
import pandas as pd
import os
for f in glob.glob('Notes_*.xlsx'):
dda = re.findall('\d+', f) #matches digits in the filename
df_each = pd.read_excel(f) # have to save the data first, coz ExcelWriter will clear up and create a new excel, so, you paste the saved data back to new sheet
df_1_dda = df_master[df_master['code'] == int(dda[0])] #select only those records with code in the filename
writer = pd.ExcelWriter(f)
df_each.to_excel(writer, 'service', index = False) # paste the saved data back to new sheet
df_1_dda.to_excel(writer, 'code_city', index = False)
writer.close()
Hope that helps!
using python 3.6.4 Anaconda - 32-bit
Solution 2:
from openpyxl import load_workbook
for f in glob.glob("Notes_*.xlsx"):
code = re.findall('\d+', f) #matches digits in the filename
df_1_dda = df_master[df_master['code'] == int(code[0])] #select only those records with code from the master dataframe#create new worksheet using openpyxl
wb = load_workbook(f)
ws = wb.create_sheet('code_city')
wb.save(f)
# reload the file and paste data I need
writer = pd.ExcelWriter(f)
df_1_dda.to_excel(writer, 'code_city')
writer.save()
Post a Comment for "Python Search Corresponding Data In Multiple Excel And Paste To A New Excel Worksheet"