Keep apostrophes when reading Excel file into pandas dataframe

Question

Reading Excel file into Pandas dataframe like this:

import pandas as pd

df = pd.read_excel(open('C:/somedir/somefile.xlsx','rb'), sheet_name=0)
print(df)

If a value in a cell starts with ' (single-quote, apostrophe) - pandas omits it. For example, it converts '123 into 123. But I need to get it as is, '123.

I realize that Excel considers single quote as text qualifier. But, maybe there's some workaround to make pandas preserve single-quotes at the start of a cell value?

Tried fiddling with dtype - but still no luck.

Answer 1

You can try using xlwings :

#pip install xlwings
import xlwings as xw
    
with xw.App(visible=False) as app:
    wb = xw.Book("file.xlsx")
    
    df = (
        wb
         .sheets["Sheet1"].used_range
         .options(pd.DataFrame, index=False, header=True)
         .value
    )

Output :

>>> print(df)

    col1
0    123
1 123.00

>>> df.values.ravel().tolist()

# ['123', 123.0]

Input used (file.xlsx) :