SQL dump to Excel

Question

I am trying to do a SQL dump of a table to excel but I can't get past the error, what am I doing wrong? Error line 14 'utf8' codec can't decode byte 0xc3 in position 0: invalid continuation byte import pyodbc import pandas as pd import os cnxn = pyodbc.connect("Driver={SQL Server};" "Server=***;" "Database=***;" "uid=***;pwd=***") cursor = cnxn.cursor() script = """ SELECT * FROM ***.*** """ df = pd.read_sql(script, cnxn) writer = pd.ExcelWriter(r'C:\Temp\export.xlsx') df.to_excel(writer, sheet_name ='Sheet1', encoding='utf-8') writer.save() ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoeBorgione · Answer

Throw in a few print() statements along the way or use a try/accept block and see it tosses something for you.

CCWeedcontrol · Answer

The first time I was using 2.y python, which worked yesterday but today nothing happens when I run the script, no error no output .xlsx.and I didn't change anything, I am lost on this one. So i decided to try it on 3.6 so in 3.6 I have to add from importlib import reload and comment out sys.setdefaultencoding('Cp1252') but in doing so I get the same error, on line 17 'utf8' codec can't decode byte 0xc3 in position 0: invalid continuation byte. Thoughts? mport pyodbc import pandas as pd import os from importlib import reload reload(sys) #sys.setdefaultencoding('Cp1252') cnxn = pyodbc.connect("Driver={SQL Server};" "Server=***;" "Database=***;" "uid=***;pwd=***") cursor = cnxn.cursor() script = """ SELECT * FROM ***.*** """ df = pd.read_sql(script, cnxn) writer = pd.ExcelWriter(r'C:\Temp\export.xlsx') df.to_excel(writer, sheet_name ='Sheet1', encoding='utf-8') writer.save()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

bixb0012 · Answer

The SQL Server default collation for many Windows locales is Latin1_General_CI_AS .  For English(United States), the default collation SQL_Latin1_General_CP1_CI_AS. Both of those collations use code page 1252 (CP-1252).

Collation and Unicode support - SQL Server | Microsoft Docs