how to convert column values to str when reading multi-sheet xlsx using pd.read_excel?

user_5937757 0
Viewed 150

I have a muti-sheet xlsx file which I want to process selected pages and finally save them as CSV.

This is a snapshot of a few raws from one page:

enter image description here

I use this code to load all pages and process each one-by-one:

def load_raw_excel_file(file_full_name):

    df = pd.read_excel(file_full_name, sheet_name=None, engine="openpyxl", header=0)
    sheets_name = list(df.keys())

    return df, sheets_name

The output of the code (from the same page) looks like this:

dfs, shs =  load_raw_excel_file("myexelfile.xlsx")
dfs['myselectedsheetname']

enter image description here

As you can see, some values from the Contract column have changed to date, but I don't want any changes. I've tried using convertors and dtype in pd.read_excel, but it didn't work:

df = pd.read_excel(file_full_name, sheet_name=None, engine="openpyxl", header=0, dtype=str)

or

df = pd.read_excel("myexelfile.xlsx", sheet_name='selectedsheetname', header=0, converters={'Contract':str})

any idea?

Update

I found a workaround but not a good solution:

def convert_str_date(x):
    
    try:
        y = x.strftime("%b-%y")
        return y
    except:
        return x


df.Contract.apply(lambda x : convert_str_date(x))

Also, see @Simon answer

openpyxl pandas excel python
1 Answers

the excel set those values to datetime format. maybe you can postprocess with the dataframe,

nKCol = df['Contract']                                            
oKCol = df['Contract'].copy()

# update cell to %b-%y string format; Nan if error                            
nKCol = pd.to_datetime(nKCol, errors='coerce').dt.strftime('%b-%y')

# update the column
df['Contract'] = nKCol   
  
# fill Nan with original column                                       
df['Contract'] = df['Contract'].fillna(oKCol)
user_76152600
Related