Python convert XLS and XLSX file to csv

+1 vote

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?

Feb 8, 2019 in Python by Prateek
18,184 views

2 answers to this question.

+1 vote

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.

answered Feb 9, 2019 by Omkar
• 69,220 points

edited Oct 7, 2021 by Sarfaraz
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

+1 vote

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.

answered Aug 30, 2019 by Mian Tanzeel
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

Related Questions In Python

+1 vote
4 answers

Python: convert txt file to csv format with rows and columns

Python will read data from a text ...READ MORE

answered Dec 14, 2020 in Python by Gitika
• 65,770 points
57,517 views
–1 vote
2 answers
0 votes
2 answers

How do I convert text file to CSV file with only plain python. Meaning no panda or any other special module?

Steps to Convert Text File to CSV ...READ MORE

answered Oct 15, 2020 in Python by Abdul Shekh
9,266 views
0 votes
1 answer

How to find the value of a row in a csv file in python and print column and row of that value ?

Hello @Khanhh , Use panda to find the value of ...READ MORE

answered Oct 15, 2020 in Python by Niroj
• 82,840 points
6,792 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,454 views
0 votes
1 answer
+5 votes
6 answers

Lowercase in Python

You can simply the built-in function in ...READ MORE

answered Apr 11, 2018 in Python by hemant
• 5,790 points
4,106 views
–1 vote
1 answer

Python convert excel file to csv

Here you go: import glob path_to_excel_files = glob.glob('path/to/excel/files/*.xlsx') for ...READ MORE

answered Feb 8, 2019 in Python by Omkar
• 69,220 points
1,292 views
+1 vote
2 answers

Python convert extracted excel file to csv

Some services require table data in CSV ...READ MORE

answered Aug 30, 2019 in Python by Mian Tanzeel
7,001 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP