Not able to read excel file which I created through open function?

user_12504232 0
Viewed 1141

I created one excel file and wrote something in it. I am trying to read that file through pandas - dataframe, but I am getting error 

XLRDError: Unsupported format, or corrupt file: Expected BOF record

Code -

import pandas as pd
a = open("D:\\Joseph\\abcsaa.xlsx","a")
a.write("Hello all")
p = pd.read_excel("D:\\Joseph\\abcsaa.xlsx")
p

Thanks for the answers. I need to store tick data in a excel and then read it through dataframe.

What is the use of open function in python for excel file if I have to use other modules for this ?

python
4 Answers

Excel file cannot be created with inbuilt python open function. You have to use openpyxl package to read and write excel files. Some besic operations using openpyxl

import openpyxl

# Open Workbook
wb = openpyxl.load_workbook(filename='example.xlsx', data_only=True)

# Get All Sheets
a_sheet_names = wb.get_sheet_names()
print(a_sheet_names)

# Get Sheet Object by names
o_sheet = wb.get_sheet_by_name("Sheet1")
print(o_sheet)

# Get Cell Values
o_cell = o_sheet['A1']
print(o_cell.value)

o_cell = o_sheet.cell(row=2, column=1)
print(o_cell.value)

o_cell = o_sheet['H1']
print(o_cell.value)

# Sheet Maximum filled Rows and columns
print(o_sheet.max_row)
print(o_sheet.max_column)
user_128571740

Install this if you don't already have it.

pip install XlsxWriter

Code:

import xlsxwriter

workbook = xlsxwriter.Workbook("D:\\Joseph\\abcsaa.xlsx")
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello world')

workbook.close()

XLsxWriter can do a lot and has great documentation here.

user_99760380

If the file already exists, open it the first time with 

a = pd.read_excel('path/aabcsaa.xlsx')

Else, create a pandas dataframe with

a = pd.DataFrame(data)

and then save it using

pd.to_excel('path/aabcsaa.xlsx')
user_116491050

You opened your file in append mode ("a"). If you want to read it with read_excel by passing the filename, you need to close the file before:

a.close()

And the content of the file needs to be in valid excel format.

user_129462330
Related