0

Does anyone know why the below code works if the file is csv or xls but not for xlsx?

with open("file", 'w') as ft:
    for row in auto_rep[answer][:-1]:
        if row != "":
            ft.write(row.replace(";",",")+"\n")

    ft.write(auto_rep[answer][-1].replace(";",","))
    ft.close()

I would like the file to be xlsx. I’m not sure if this is supported though.

Ex: file is either

  • filename.csv
  • filename.xls
  • filename.xlsx

I am also using the following code first to copy an xlsx file so I don’t mess with the original file.

import shutil

orig = r'C:\filepath\original.xlsx‘
targ = r'C:\filepath\file(.csv, .xls, or .xlsx)'

shutil.copyfile(orig, targ)

If the file is csv, it creates the file and the information populated to the first sheet is correct. I know csv doesn’t use multi sheets so I understand it wouldn’t load the others anyways.

If the file is xls, it creates the file but I still receive the following message:

“The file format and extension of ‘filename.xls’ don’t match. The file could be corrupted or unsafe. Unless you trust it’s source, don’t open it. Do you want to open it anyway?” … and I can click yes and it loads but only the first sheet populates.

If the file is xlsx, It creates the file but I get an error that states:

“Excel cannot open the file ‘filename.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”

hackerboi
  • 31
  • 5
  • 2
    CSV is a text-format, just plain text and separator. xls and xlsx are NOT at all plain-text, they are complex stuctured, with possible archive structure, they should be manipulated (read/write) like plain text "line by line" – azro May 27 '22 at 19:23
  • The simple answer is, you are creating a CSV file regardless of what extension you use for the output file. It's when the *reader* makes assumptions about the file format based on the extension that *it* runs into a problem. – chepner May 27 '22 at 19:26
  • I guess the more simple question is… If I have a csv file (name.csv) and I have an xlsx file (name.xlsx)… is there a way for Python to input everything in the csv file to the 1st sheet in the xlsx file? I know I can open the xlsx file and go to the data tab and then import the csv file and that works. But I want to automate it. I also can’t install any packages because it’s a work computer. – hackerboi May 27 '22 at 20:21

1 Answers1

1

I'm researching a simliar question and I found this question with an answer that might help. How to check the uploaded file is csv or xls in python?

Since this question is only a few months old I am hoping this will still help. It uses a standard library package so that should be ok for you.

The way I would apply this answer to your specific question would be to read either filetype using PanDAS and then save it to Excel format. You would need to import PanDAS though. Admittedly you are "...messing with the original file..." but perhaps you could leave the xls's alone and only do this for csv.

M T
  • 863
  • 1
  • 7
  • 15