SQL dump to Excel

[CCWeedcontrol]

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]

I just dealt with that same problem see: Unicode Errors   Line14, change 

encoding='utf-8'
#to
encoding='utf-16'

[CCWeedcontrol]

utf-16 didn't work, adding reload(sys) , sys.setdefaultencoding('Cp1252') to my code worked.

Thanks tho.

import pyodbc, sys
import pandas as pd
import os
reload(sys)  
sys.setdefaultencoding('Cp1252')‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

Ah, yes I've used that too.  Glad you got it going none the less; those errors are a pain.

[JoshuaBixby]

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 

[CCWeedcontrol]

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()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

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