Loading xlsx files into dataframes using openpyxl

Asked Feb 28 '20 at 15:48

Active Feb 28 '20 at 15:48

Viewed 30 times

So I have made this code, it goes smoothly through all the files but doesn't write them into the database. Someone maybe know the fix for this?

import pandas as pd
import os
import openpyxl



files= [file for file in os.listdir(r'path')]

a = pd.DataFrame()

for file in files:
    wb= openpyxl.load_workbook(r'path'+file)
    print (wb)
    for ws in wb.sheetnames:
        ws = wb[ws]
        data = ws.values
        columns= next(data)[0:]
        df = pd.DataFrame(data, columns=columns)
     d= pd.concat([df,a])

print (a)

asked Feb 28 '20 at 15:48

Michał

What do you mean write them into the database? Are you basically trying to create one massive DF from all sheets in all workbooks in the path? – Jon Clements Feb 28 '20 at 15:50
Yes, that is what I've meant. – Michał Feb 28 '20 at 15:51
You only concat the last sheet of each file to a new unused df.... Change the last line to `a = pd.concat([df,a])` and put it inside the **inner** loop – Tomerikoo Feb 28 '20 at 15:54
@Tomerikoo except this one step further of *all* sheets from *multiple* files – Jon Clements Feb 28 '20 at 16:01
@JonClements well it's a pretty small step that OP should be able to handle – Tomerikoo Feb 28 '20 at 16:08
That helped, thanks a lot. – Michał Feb 28 '20 at 17:00

Loading xlsx files into dataframes using openpyxl

0 Answers0