1

Trying to use python to change the format of a file (a .xlsx) to .xls. Creating a copy of the file with a different format would also work.

This is part of a larger script, so manually opening the file in excel and saving as a new format will not work (unfortunately).

I have tried using python shutil.copyfile and os.rename, but the file is still being read as a .xlsx (despite having the .xls extension)

(Opening the file in excel throws a warning that the file format and file extension do not match)

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
BadgerTaco
  • 21
  • 3
  • I think that Excel actually does some real changes when it saves a file as .xls as opposed to .xlsx, and almost certainly the best way to replicate those changes is to just have excel do it itself. I think that means having the script open excel and saving from there. Why can't you do that? Are you concerned about the window opening and being on screen? You can make it so it is not visible if that's the concern. – scotscotmcc Jan 19 '23 at 21:57
  • 3
    What about the tools you have tagged, `openpyxl` and `xlrd`? – mkrieger1 Jan 19 '23 at 21:57
  • 1
    While `.xlsx` is essentially a zip file containing XML files and some other data, `.xls` is a completely different binary format. Just changing the extension does not change the data in the file. You need to use a library that can handle both formats to do the conversion. – dskrypa Jan 19 '23 at 22:33
  • @scotscotmcc, I am not sure how to do that: Apologies if I was unclear, If the script can do that in excel, that would be great! Do you know where I can look to learn how to do that? What I meant is that simply opening the file myself in excel (without python) and using the "save as" function would not work as I will need to do this to a large number of files. – BadgerTaco Jan 20 '23 at 01:38
  • Use Excel or OpenOffice to do this. – Charlie Clark Jan 20 '23 at 10:04
  • 1
    See the answer from @kvdogan in this question https://stackoverflow.com/questions/9918646/how-to-convert-xls-to-xlsx. Note you're wanting to change xlsx to xls so change the file format to 56 and you want to remove the end x so your save would be **wb.SaveAs(fname[:-1], FileFormat=56)** . Be aware you may loose some funtionality after conversion. – moken Jan 20 '23 at 10:15

1 Answers1

0

As moken alluded to in the comments, you can use the pywin32 library to open Excel and get access to lots of its internals, including being able to do a SaveAs.

Note that you'll install the library as pywin32 but you import it as win32com (which is annoying...).

import win32com.client as win32

filename = r"C:\the\full\path\file.xlsx" # you do need the full path, it won't work with relative path
new_filename = filename[:-1] # new filename is the same as the old except for dropping the last character

xl = win32.DispatchEx("Excel.Application") # opens Excel
xl.DisplayAlerts = False # Makes the application invisible
wb = xl.Workbooks.Open(filename)
wb.SaveAs(new_filename, FileFormat=56)
wb.Close()
xl.Quit()

There are lots of other libraries you can use to achieve similar effects, but this one should do it.

Note that you may lose some functionality as you are going from a more-recent to an older version of Excel. This script replicates how it would work if you manually did all the clicks with your mouse in Excel itself, so any loss of functionality isn't related to the script, just to the "downgrade" itself.

scotscotmcc
  • 2,719
  • 1
  • 6
  • 29