Convert Excel files to CSV

Question

I wrote the following script to run through several excel files and format them before saving as a CSV for upload to a Quickbase app I'm creating. Each excel file is roughly 100k lines and the code takes about 3-5 minutes per file. I'm currently using pandas to make my edits. If there is a more efficient way please let me know.

import pandas as pd
import os

# set working directories for files
starting_folder=('Purchasing/unformatted/')
save_folder = ('Purchasing/')

#create list of excel file names from folder  
files = []
for file in os.listdir(starting_folder):
    filename = os.fsdecode(file)
    files.append(filename)

# create list for file names to be saved as csv
save_files = [w.replace('xlsx','csv') for w in files]

# create data frame of fiscal calendar
calendar = pd.read_excel('Purchasing/Fiscal Calendar 15-18.xlsx')
fiscal_calendar = pd.DataFrame([])
#create new data frame with only two columns
fiscal_calendar['InvoiceDate'] = calendar['Date']
fiscal_calendar['Week'] = calendar['Week.1']


#loop through csv files to format and save to new location
for i in range(len(files)):
    #reads unformatted excel file into dataframe
    df = pd.read_excel(starting_folder+files[i])
    #change dtype of dates in report to date time
    df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
    #drop unwanted rows
    df = df.drop(df[df.Brand == 'NONPROD'].index)
    #add in fiscal week based on invoice date
    Sygma = pd.merge(df,fiscal_calendar, how = 'left', on = 'InvoiceDate')
    #save to csv for db loading
    Sygma.to_csv(save_folder+save_files[i],index = False)

Answer 1

There are a few small fixes, but just to point out, pandas.read_excel is notoriously slow..

Code.py

import pandas as pd
import os

# # NOTE: If there are multiple worksheets this will take awhile,
# # and you can speed it up by specifying just the worksheet you want
# create data frame of fiscal calendar
fiscal_calendar = pd.read_excel('Purchasing/Fiscal Calendar 15-18.xlsx')
fiscal_calendar = fiscal_calendar[["Date", "Week.1"]].rename(columns={"Date": "InvoiceDate", "Week": "Week.1"})

# set working directories for files
starting_folder = ('Purchasing/unformatted/')
save_folder = ('Purchasing/')

#create list of excel file names from folder  
for file in os.listdir(starting_folder):
    input_name = starting_folder + os.fsdecode(file)
    df = pd.read_excel(input_name)

    # cleanup
    df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
    df = df.drop(df[df.Brand == 'NONPROD'].index)

    # create the output file and save it to .csv
    output_name = save_folder + filename + ".csv"        
    Sygma = pd.merge(df, fiscal_calendar, how='left', on='InvoiceDate')
    Sygma.to_csv(output_name, index=False)