Pandas - reading from SQL server, and outputting to csv - encoding problems

Question

I am reading data into pandas from an SQL Server 2014 12.0.4100 SP1 database. The data is stored in the Windows-1252 encoding.

I am using python 2.7.

I want to output the resulting dataframe to Excel or csv. Specifically:

import pyodbc
cnxn = pyodbc.connect(r'Driver={SQL Server};Server=.\my_server;Database=my_db;Trusted_Connection=yes;')
sql = "select * from my_table"
df = pd.read_sql(sql, cnxn)
df.to_csv("my_csv.csv", encoding="utf-8")

However, this fails with the error message:

UnicodeDecodeError: 'utf8' codec can't decode byte 0x92 in position 13966: invalid start byte

What do I need to do to successfully export to a utf-8 csv?

Jonathan Leffler · Accepted Answer · 2017-04-24 23:38:31Z

2

The solution is to convert any columns with non-ASCII characters to UTF-8 explicitly.

You can do this using the following code:

def convert(my_str):
    return my_str.decode('Windows-1252').encode('utf-8')
df["Name"] = df["Name"].apply(convert)

Once converted, you will be able to write to .csv and Excel format without problems.

edited Apr 24, 2017 at 23:38

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

answered May 29, 2015 at 17:18

RobinL

11.7k10 gold badges58 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Govind Parmar · Accepted Answer · 2018-12-03 01:54:09Z

0

Did you try to use 'ISO-8859-2' as the encoding ?

df.to_csv("my_csv.csv", encoding="ISO-8859-2")

edited Dec 3, 2018 at 1:54

Govind Parmar

21.7k7 gold badges57 silver badges91 bronze badges

answered Jul 18, 2018 at 12:15

Victor Criclivii

413 bronze badges

Collectives™ on Stack Overflow

Pandas - reading from SQL server, and outputting to csv - encoding problems

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related