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 can't convert the files with both the formats to csv with a single command. You will have to separately convert them. Refer to this code:

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)

This should work.

To know more, It's recommended to join our Python Training in Chennai today.

Comments

I have some Special character in xlsx and hence i am getting below error

UnicodeEncodeError: 'ascii' codec can't encode character u'\xd1' in position 13: ordinal not in range(128)

Ñ - Something like this

---

Hi, @Vinzz,

Try this

export PYTHONIOENCODING=utf-8

---

Sorry this didn't help.

---

Hi, @There,

Could you please post here the code snippet you have executed? It will be helpful to analyze the bug.

---

Hi Tina,
 

Please find my code below

import pandas as pd
read_file = pd.read_excel (r'C:\Users\XYZ\Documents\ABC.xlsx',ENCODING='utf-8')
read_file.to_csv (r'C:\Users\XYZ\Documents\ABC.csv', index = None, header=True)

---

Hello @Vinzz,

Simply pass index=False as a keyword argument to not write row names.

read_file.to_csv (r'C:\Users\XYZ\Documents\ABC.csv', index = False, header=True)

---

Hi Niroj,

Thanks for replying but,

The actual problem is that the Code is failing because there are some characters in the columns that have

Ñ - Something like this

Error:- UnicodeEncodeError: 'ascii' codec can't encode character u'\xd1' in position 13: ordinal not in range(128)

---

Hello,

 If you are getting this error you need to read the Python Unicode HOWTO

Answer 2

XLSX tables are usually created in MS Excel 2007/2010 and while offering handy features of editing and processing data cells, have problems with support by the majority of software. CSV is a far more common format for storing data. It also arranges data into table, where each row is a simple line of text, where cell values are separated by delimiter. It is very size efficient format and is supported by a lot of programs. You can view and edit information in CSV even in a Notepad. That is why sometimes it is reasonable to convert XLSX to CSV in order to make information more usable.

For this purpose Total Excel Converter will ideally suit to any kind of user. t is easy and fast-working converter for Excel files that can turn them into the following formats: CSV, XML, XLS, DOC, TXT, DBF.

Comments

Hello @Mian. Is this a library? How can I install this using pip?

---

Hi

Thanks for sharing the code. I tried using it to convert xls to xlsx. However the converted file does not show up in the destination folder. Can you help please.

import pandas as pd
import glob

source="C:/my source folder/"
dest="C:/my dest folder"

os.chdir(source)

for file in glob.glob("*.xls"):
    
    df = pd.read_html(file)
    df1 = df[0]
     
    df1.to_excel(dest+file+'.xlsx', index = False)

---

Hey, @There, 

Could you please confirm me quickly that you are getting this error message mentioned below?

"Error! Please specify the source path and the destination."

---

No I am not getting any error. My converted files are not saving t=in the destination folder