Python convert XLS and XLSX file to csv

Question

I am trying to write a python code to convert excel file to csv. I am written the following code:

import glob 

path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx')

for excel in path_to_excel_files:

 out = excel.split('.')[0]+'.csv'

 df = pd.read_excel(excel)

 df.to_csv(out) 

Now, the problem is that, this works only of the file is in XLSX format. In my case, the folder contains both: XLSX or XLS file and I have to convert it to csv. How can I do it?

Answer 1

You are facing this problem because you are trying to do it in command and that does not work in pandas because it does not have a command to do it. 

So you need to do it multiple steps.

You can do it like this:

import pandas as pd

import os

import glob

source="D:\\source\\"

dest='D:\\dest\\'

os.chdir(source)

for file in glob.glob("*.xls"):

         df = pd.read_excel(file)

         df.to_csv(dest+file+'.csv', index=False)

         os.remove(file)

for file in glob.glob("*.xlsx"):

       df = pd.read_excel(file)

       df.to_csv(dest+file+'.csv', index=False)

       os.remove(file)

for file in glob.glob("*.XLS"):

         df = pd.read_excel(file)

         df.to_csv(dest+file+'.csv', index=False)

         os.remove(file)

for file in glob.glob("*.XLSX"):

       df = pd.read_excel(file)

       df.to_csv(dest+file+'.csv', index=False)

       os.remove(file)

You can use this video learn more about pandas: