DEV Community 👩‍💻👨‍💻

Cover image for How to preserve the data types when writing pandas DataFrame to Excel?
Retiago Drago
Retiago Drago

Posted on • Updated on

How to preserve the data types when writing pandas DataFrame to Excel?

I'm having an issue with how pandas could not preserve the data types of my table after writing process.

This is my table in which I read the data from SQL Server with my query.

Initial

And I simply code this line to write a new one hoping that it will yield the same result when I read it later.

df.to_excel('new2.xlsx', index=False, engine='xlsxwriter')
df2 = pd.read_excel('new2.xlsx')
Enter fullscreen mode Exit fullscreen mode

And I ended up with this.

After writing process

I'm having a hard time finding the right keywords and solutions on Stack Overflow. I've already read the pandas documentation but nothing covers my case.

Let's be friend 👋

Latest comments (1)

Collapse
 
ranggakd profile image
Retiago Drago Author

Some people on Telegram said the right approach is to pass either dtype or converters on pandas.read_excel(). It worked but in the end, it cannot 100% preserve the values.

Like this example:

Reading the data from query (initial)

RKAT ITS

Reading the same data from excel file (saved version)

RKAT _x0013_ ITS

Update Your DEV Experience Level:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. 🛠