CODEWITHSUNDEEP
pythoncsv
TigerClaw
TigerClaw

Reputation: 175

Python - Multiple XLSX/XLSM to CSV

I have a folder with multiple *.xlsm-files for example "80-384sec -_november_2017.xlsm", "80-384sec -_december_2017.xlsm", ..... I can read a specific sheet from this file with python like this:

df_xlsmtocsv = pd.read_excel('80-384sec -_november_2017.xlsm', 'sheet3, index_col=None )

And my first solution is something like this:

for file in os.listdir():
    if file.endswith(".xlsm"):
        df_qctocsv = pd.read_excel(file, 'sheet3', index_col=None )
        print(df_qctocsv)
        with open('all.csv', 'a') as f:
            df_qctocsv.to_csv(f, index=True, header=None)

How can I read multiple xlsm-files and append all new messages to a csv-file and order this for example by first column?

After converting I want to copy all this rows from the csv-file to a new sheet in an existing file "messages.xlsx".

Upvotes: 0

Views: 1150

Answers (1)

jjj
jjj

Reputation: 1109

There is a lot of ways in which you can join data frames. One possible way is this:

import pandas as pd

df = pd.DataFrame()
for file in os.listdir():
    if file.endswith(".xlsm"):
        df_tmp = pd.read_excel(file, 'Sheet1', index_col=None)
        df = df.append(df_tmp)

df.to_csv('all.csv')

EDIT: If you want to add your dataframe to an existing xlsx file (adapted from here):

from openpyxl import load_workbook

book = load_workbook('<your-xlsx-file>')
wrt = pd.ExcelWriter('<your-output-file>', engine='openpyxl')
wrt.book = book
wrt.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(wrt, '<name-of-your-sheet>')
wrt.save()

Upvotes: 1

Related Questions