CODEWITHSUNDEEP
pythonexcel
Unicorn_tech
Unicorn_tech

Reputation: 251

Python iterating over excel files in a folder

I am interested in getting this script to open an excel file, and save it again as a .csv or .txt file. I'm pretty sure the problem with this is the iteration - I haven't coded it correctly to iterate properly over the contents of the folder. I am new to Python, and I managed to get this code to sucessfully print a copy of the contents of the items in the folder by the commented out part. Can someone please advise what needs to be fixed?

My error is: raise XLRDError('Unsupported format, or corrupt file: ' + msg)

from xlrd import open_workbook
import csv
import glob
import os
import openpyxl

cwd= os.getcwd()
print (cwd)

FileList = glob.glob('*.xlsx')
#print(FileList)

for i in FileList:
    rb = open_workbook(i)
    wb = copy(rb)
    wb.save('new_document.csv')

Upvotes: 1

Views: 1380

Answers (2)

Leo Qi
Leo Qi

Reputation: 567

It appears that your error is related to the excel files, not because of your code.

  • Check that your files aren't also open in Excel at the same time.
  • Check that your files aren't encrypted.
  • Check that your version of xlrd supports the files you are reading

In the above order. Any of the above could have caused your error.

Upvotes: 1

David Duarte
David Duarte

Reputation: 664

I would just use:

import pandas as pd
import glob
import os

file_list = glob.glob('*.xlsx')

for file in file_list:
    filename = os.path.split(file, )[1]
    pd.read_excel(file).to_csv(filename.replace('xlsx', 'csv'), index=False)

Upvotes: 1

Related Questions