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
10,289 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)
answered Feb 8, 2019 by Omkar
• 69,030 points
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
2 answers

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

If there is lots of raw data ...READ MORE

answered Sep 21 in Python by Archies singh
25,307 views
0 votes
2 answers
0 votes
1 answer

Python using basicConfig method to log to console and file

I can't reproduce it on Python 3.3. ...READ MORE

answered Aug 14, 2018 in Python by aryya
• 7,380 points
138 views
0 votes
1 answer

Python using basicConfig method to log to console and file

Try this working fine(tested in python 2.7) ...READ MORE

answered Aug 27, 2018 in Python by Priyaj
• 57,660 points
3,554 views
+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 6, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 1,364 views
0 votes
0 answers
+4 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,800 points
1,011 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,030 points
436 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
3,783 views