topic Re: SQL dump to Excel in Python Questions

SQL dump to Excel

CCWeedcontrol — Sat, 11 Dec 2021 22:59:39 GMT

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

Re: SQL dump to Excel

JoeBorgione — Sat, 11 Dec 2021 22:59:41 GMT

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

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

Re: SQL dump to Excel

CCWeedcontrol — Sat, 11 Dec 2021 22:59:44 GMT

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

Re: SQL dump to Excel

JoeBorgione — Tue, 14 Jul 2020 13:16:44 GMT

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

Re: SQL dump to Excel

JoshuaBixby — Tue, 14 Jul 2020 13:51:13 GMT

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

Re: SQL dump to Excel

CCWeedcontrol — Sat, 11 Dec 2021 22:59:46 GMT

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

Re: SQL dump to Excel

JoeBorgione — Tue, 14 Jul 2020 18:58:34 GMT

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