0

I am trying to save a pandas dataframe as an excel sheet. The dataframe as names with Ö, Ä, Å, Ø, Æ due to which i get the error: 

'ascii' codec can't decode byte 0xc3 in position 2: ordinal not in range(128)

I am using the following code of lines:

import sys
reload(sys)
sys.setdefaultencoding('utf8')
writer = pd.ExcelWriter('/filepath/filename.xls')
df.to_excel(writer,'Sheet1',index=False)
writer.save()

I tried several solutions as specified by the following but no success:

  1. Python pandas to_excel 'utf8' codec can't decode byte

  2. How to fix: "UnicodeDecodeError: 'ascii' codec can't decode byte"

  3. How to fix: "UnicodeDecodeError: 'ascii' codec can't decode byte"

Example code:

I have the following code as an example dataframe:

d = {'col1': ['Äse', 'SÖA'], 'col2': [3, 4]}
df = pd.DataFrame(data=d)

Now if try to save it using the following lines of code, I get the ascii, unicode error.

import sys
reload(sys)
sys.setdefaultencoding('utf8')
writer = pd.ExcelWriter('/filepath/filename.xls')
df.to_excel(writer,'Sheet1',index=False)
writer.save()

I tried adding the "encoding='utf8'" parameter to to_excel as well as removing the first three lines but it did not work.

Your help is highly appreciated, thanks!

Can anybody help in this matter?

Hanif
  • 377
  • 4
  • 19
  • Usually adding encoding to your `to_excel` will do the trick: `result.to_excel(writer,'Sheet1',index=False,encoding='utf8')` – O.Suleiman Feb 19 '18 at 12:28
  • did you try setting utf-8 as encoding parameter in to_excel – Arpit Solanki Feb 19 '18 at 12:28
  • Yes, I tried with encoding='utf8' in to_excel but no success. Following are the first lines of the error: --------------------------------------------------------------------------- UnicodeDecodeError Traceback (most recent call last) in () result.to_excel(writer,'Sheet1',index=False,encoding='utf8') ----> writer.save() – Hanif Feb 19 '18 at 12:36
  • If you can put a working code, we can help you more because we can't recreate your problem. – O.Suleiman Feb 19 '18 at 12:40
  • `You should not use non-Unicode Python version.` – dsgdfg Feb 19 '18 at 12:43
  • @O.Suleiman I have now edited my question and added an example code. – Hanif Feb 19 '18 at 12:52
  • I tried your example code without the `sys` lines and without adding any encoding to `to_excel` and it worked, my environment: Python 3.6.2 / Pandas 0.20.3. Output: https://imgur.com/a/4f2SC – O.Suleiman Feb 19 '18 at 13:01
  • @O.Suleiman Strange it did not work for me. But the solution by Roee Shenberg worked in my case. – Hanif Feb 19 '18 at 13:42
  • According to pandas documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html encoding: string, default None encoding of the resulting excel file. Only necessary for xlwt, other writers support unicode natively. – O.Suleiman Feb 19 '18 at 13:46
  • Out of curiosity, what are your Python and Pandas versions? – O.Suleiman Feb 19 '18 at 13:52
  • @O.Suleiman I am using Python 2.7 and pandas 0.22.0 versions. I have saved a dozen of files without any encoding issues even though those files had special characters as now i have in the current file, but this is the first time i got this error. – Hanif Feb 20 '18 at 10:43

1 Answers1

0

Provide the encoding parameter to the ExcelWriter constructor, not in the to_excel() call:

>>> writer = pd.ExcelWriter('/path/to/file.xls', encoding='utf-8')
>>> df.to_excel(writer,'Sheet1',index=False)
>>> writer.save()
Roee Shenberg
  • 1,457
  • 1
  • 13
  • 22
  • This solution worked for me. Earlier, I used encoding='utf8' but it did not work, now I changed to encoding='utf-8' and adding this '-' helped I guess. – Hanif Feb 19 '18 at 13:44
  • 1
    The difference is encoding on the constructor, not on the `to_excel()` call – Roee Shenberg Feb 19 '18 at 13:55