Python – Importing Multiple Excel Files using OpenPyXL

excelopenpyxlpython

I am trying to read in multiple excel files and append the data from each file into one master file. Each file will have the same headers (So I can skip the import of the first row after the initial file).

I am pretty new to both Python and the OpenPyXL module. I am able to import the first workbook without problem. My problem comes in when I need to open the subsequent file and copy the data to paste into the original worksheet.

Here is my code so far:

# Creating blank workbook
from openpyxl import Workbook
wb = Workbook()

# grab active worksheet
ws = wb.active

# Read in excel data
from openpyxl import load_workbook
wb = load_workbook('first_file.xlsx') #explicitly loading workbook, will automate later

# grab active worksheet in current workbook
ws = wb.active

#get max columns and rows
sheet = wb.get_sheet_by_name('Sheet1')
print ("Rows: ", sheet.max_row) # for debugging purposes
print ("Columns: ", sheet.max_column) # for debugging purposes
last_data_point = ws.cell(row = sheet.max_row, column = sheet.max_column).coordinate
print ("Last data point in current worksheet:", last_data_point) #for debugging purposes

#import next file and add to master
append_point = ws.cell(row = sheet.max_row + 1, column = 1).coordinate
print ("Start new data at:", append_point)
wb = load_workbook('second_file.xlsx')
sheet2 = wb.get_sheet_by_name('Sheet1')
start = ws.cell(coordinate='A2').coordinate
print("New data start: ", start)
end = ws.cell(row = sheet2.max_row, column = sheet2.max_column).coordinate
print ("New data end: ", end)

# write a value to selected cell
#sheet[append_point] = 311
#print (ws.cell(append_point).value)

#save file
wb.save('master_file.xlsx')

Thanks!

Best Answer

I don't really understand your code. It looks too complicated. When copying between worksheets you probably want to use ws.rows.

wb1 = load_workbook('master.xlsx')
ws2 = wb1.active

for f in files:
    wb2 = load_workbook(f)
    ws2 = wb2['Sheet1']
    for row in ws2.rows[1:]:
          ws1.append((cell.value for cell in row))
Related Topic