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 in Python by Prateek
3,669 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 by Omkar
• 67,660 points
+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 by Mian Tanzeel
Hello @Mian. Is this a library? How can I install this using pip?

Related Questions In Python

0 votes
1 answer

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

This code should work. You have to ...READ MORE

answered Aug 1 in Python by Ashish
421 views
0 votes
1 answer
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 ariaholic
• 7,340 points
48 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
• 56,940 points
1,021 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 in Python by Neha
• 330 points

edited Jul 8 by Kalgi 346 views
+4 votes
6 answers
0 votes
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 in Python by Omkar
• 67,660 points
118 views
+1 vote
2 answers

Python convert extracted excel file to csv

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

answered Aug 30 in Python by Mian Tanzeel
1,343 views