Convert Excel files to CSV using Python and Pandas

July 3, 2020  /  Leave a response

I had a need to regular convert excel to csv file for processing instead of every time opening them in excel and save as CSV, below Python script using Pandas library converts all excel files in the given directory to CSV files.

import pandas as pd
import os

path = '/Users/shravan/Documents/files/'
csv_dir_path = os.path.join(path, 'csvs')
files = os.listdir(path) 

if not os.path.exists(csv_dir_path): 
    os.makedirs(csv_dir_path) 

for file_name in files: 
     # process only files, ignore child directories
    if os.path.isfile(os.path.join(path, file_name)):

        csv_file_path = os.path.join(csv_dir_path, os.path.splitext(file_name)[0]+'.csv')

        # If the csv file alreday exists, skips the conversation
        if not os.path.isfile(csv_file_path):
            # df = pd.read_excel(path+file, sheet_name='Sheet1')
            df = pd.read_excel(os.path.join(path, file_name))
            # print(df.shape)
            df.to_csv(csv_file_path, index=False)

print('Export Complete')