0

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:

enter image description here

vestland
  • 55,229
  • 37
  • 187
  • 305

1 Answers1

2

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!

Nordle
  • 2,915
  • 3
  • 16
  • 34