Writing excel 2003 files from python 3.x

Question

I have a very large CSV dataset (several million records). I have already filtered and massaged and split this list into customer specifications. All this was done in Python3.3

The final requirement is that these splitting lists are saved in Excel format. They have a utility that imports an Excel spreadsheet (in a specific format) into its database after doing some calculations and checking for existing duplicates in the database. My problem is that their utility only works with Excel 2003.xls files ... I did not know this ahead of time.

So, I can already write data in the correct format for Excel 2007 using OpenPyXl, but these files will not work. I can write CSV files, but they do not work either, their importer needs xls files. Perhaps there is a way to batch convert all files from Excel 2007 xlsx format to xls format or from csv format to xls format? There are thousands of files, so this cannot be done manually.

The best thing to do is to output them in the correct format, but I cannot find a python 3 compatible method that will work with Excel 2003 format. Xlwt is only python 2.x.

Does anyone have any suggestions on how I can end this?

EDIT: It was like a solution.

EDIT2: Added workbook as suggested by stenci.

import os import errno import glob import time import win32com.client def xlsx_to_xls(path): xlsx_files = glob.glob(path+'\\*.xlsx') if len(xlsx_files) == 0: raise RuntimeError('No XLSX files to convert.') xlApp = win32com.client.Dispatch('Excel.Application') for file in xlsx_files: xlWb = xlApp.Workbooks.Open(os.path.join(os.getcwd(), file)) xlWb.SaveAs(os.path.join(os.getcwd(), file.split('.xlsx')[0] + '.xls'), FileFormat=1) xlWb.Close() xlApp.Quit() time.sleep(2) # give Excel time to quit, otherwise files may be locked for file in xlsx_files: os.unlink(file) 

Answer 1

Open them with Excel 2007 and save them as Excel 2003. You can do this with a simple VBA macro or from Python without even showing the Excel application to the user. The only problem is you need Excel on your computer.

Here is the VBA code:

 Sub ConvertTo2003(FileName As String) Dim WB As Workbook Set WB = Workbooks.Open(FileName, ReadOnly:=True) WB.SaveAs Replace(FileName, ".xlsx", ".xls"), FileFormat:=xlExcel8 WB.Close End Sub 

Here is the Python code:

 xlApp = Excel.ExcelApp(False) xlApp.convertTo2003('FileName.xlsx') class ExcelApp(object): def __init__(self, visible): self.app = win32com.client.Dispatch('Excel.Application') if visible: self.app.Visible = True def __exit__(self): self.app.Quit() def __del__(self): self.app.Quit() def convertTo2003(self, fileName): if self.app: wb = self.app.WorkBooks.Open(fileName, ReadOnly = True) wb.SaveAs(fileName[:-1], FileFormat = 56) wb.Close() def quit(self): if self.app: self.app.Quit() 

Answer 2

The situation has changed since the question was first asked (and the answer received). Starting with version 1.0.0, xlwt works with Python 3. Thus, this is perhaps the easiest option for outputting Excel 2003 workbooks and, of course, the preferred method if you do not have Excel at hand.