Saving a pandas dataframe in excel with utf-8 encoding

Question

Simply want to set the encoding of an excel workbook for saving data. All data is stored in a pandas dataframe.

Code below:

file_name = "bobs_burgers"

with pd.ExcelWriter(r".\Exported_data\{file_name}.xlsx".format(file_name = file_name),
                        engine="xlsxwriter",
                        options={'encoding':'utf-8'}) as writer:

data_table.to_excel(writer, sheet_name = "characters", index = False)

I receive the error below

TypeError: Workbook.init() got an unexpected keyword argument 'encoding'

Using google to search for this, I have found that the majority of comments about encoding using ExcelWriter always has input of options.

How to set the encode to utf-8 for exported pandas tables?

Answer 1

According to the ExcelWriter documentation from pandas version 2 the **kwargs was removed from this class. So in order to pass options to ExcelWriter, you need to pass it to engine_kwargs as a key like this :

engine_kwargs = {'options':{'encoding':'utf-8'}}

Here is the modified version of your code :

file_name = "bobs_burgers"

with pd.ExcelWriter(r".\Exported_data\{file_name}.xlsx".format(file_name = file_name),
                        engine="xlsxwriter",
                        engine_kwargs = {'options':{'encoding':'utf-8'}}) as writer:

data_table.to_excel(writer, sheet_name = "characters", index = False)