Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

The question:

I'm trying to write data to an Excel file using Python, specifically using the ExcelWriter function provided py Pandas as described here in the docs . I think I've onto something here, but I'm only able to achieve one of two outcomes:

1. If the Excel file is open, access permission is denied.

2. If the Excel file is closed, the code seems to be running just fine, but the following error message is provided when trying to open the file Excel file after execution:

Excel cannot open the file excelTest.xlsm because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extenstion matches the format of the file

Does anyone know what's going on here? Or is there perhaps a better way to do this than using pd.ExcelWrite ?

The details:

I've got three files in the directory C:\pythontest :

1. input.txt

2. excelTest.xlsm

1. pythonTest.py

input.txt is a comma separated text file with this content:

A,B,C
1,4,6
2,5,5
3,5,6
excelTest.xlsm is an Excel file that is completely empty with the exception of of one empty sheet named Sheet1.
pythonTest.py is a script where I'm trying to read the txt file using Python, and then write a pandas dataframe to the Excel file:
import os
import pandas as pd
os.getcwd()
os.chdir('C:/pythonTest')
os.listdir(os.getcwd())
df = pd.read_csv('C:\\pythonTest\\input.txt')
writer = pd.ExcelWriter('excelTest.xlsm')
df.to_excel(writer,'Sheet2')
writer.save()
But as I've mentioned, it fails spectacularly. Any suggestions?
System info:
Windows 7, 64 bit
Excel Version 1803
Python 3.6.6 | Anaconda custom (64-bit) |
Pandas 0.23.4
EDIT 1 - print(df) output as requested in the comments:
Pandas requires that a workbook name ends in .xls or .xlsx. It uses the extension to choose which Excel engine to use.
So the problem you've got is the extension, due to "extension hardening" Excel won't open this file since it knows that it doesn't contain a macro and isn't actually an xlsm file. Writing to excelTest.xlsx should work!
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.