0

I have a Python script that pulls data from a MS Access database, concatenates the data to the end of my Google Sheets dataframe, and then pushes the updated dataframe back to the Google Sheet. The process for my 6 other worksheets works fine but this worksheet keeps giving me the Unicode Error when I try to push the dataframe to Google Sheets yet I am able to print the dataframe without issues. If anybody has any ideas on how to fix this I would appreciate it! (I have replaced the sheet and worksheet name with placeholders) I believe the issue might have something to do with the fullAddress data from the sql query that concatenates the address together but I'm not sure.

The line that is throwing the error is gd.set_with_dataframe(whsl_sheet, whsl_updated, include_column_header=True)

The Traceback message is "Traceback < module > set_with_dataframe _cellrepr 'UnicodeEncodeError: 'ascii' codec can't encode character u'\u2026' in position 48: ordinal not in range(128)"

whsl_sheet = client.open('Google Sheet Name').worksheet('Worksheet Name')
whsl_query = "SELECT Orders.Company, Orders.OrderDate, CONCAT(Address, ' ', Address2, ', ', City, ', ', State, ' ', Zip) AS fullAddress, Orders.RefName, Orders.SourceOrderID, Orders.ProductTotal, Orders.GrandTotal \
FROM Orders WHERE CAST(OrderDate as Date) = CAST(getdate() AS Date) AND CartID = 11"
df_whsl = pd.read_sql(whsl_query, cnxn)
df_whsl.rename(columns={'Company': 'ACCOUNT NAME', 'OrderDate': 'ORDER DATE', 'fullAddress': 'SHIPPING ADDRESS', 'RefName': 'REP NAME', 'SourceOrderID': 'Order #', 'ProductTotal': 'TOTAL ORDER WHSL$ (MERCH ONLY)', \
'GrandTotal': 'NOT USED FOR COMMISSION PAY'}, inplace=True)
whsl_existing = gd.get_as_dataframe(whsl_sheet)
whsl_existing2 = whsl_existing.dropna(how='all', axis=1)
whsl_existing3 = whsl_existing2.dropna(how='all')
whsl_updated = pd.concat([whsl_existing3, df_whsl], ignore_index=True, sort=False)
gd.set_with_dataframe(whsl_sheet, whsl_updated, include_column_header=True)
cn_code
  • 3
  • 3
  • 1
    Welcome to Stack Overflow. "keeps giving me the Unicode Error when I try to push the dataframe to Google Sheets" Which part of the code does that? Exactly what does the error message say? Please read [ask] and https://meta.stackoverflow.com/questions/359146, and show a *complete* error message by copying and pasting, starting from the line that says `Traceback (most recent call last):`, and formatting like code. Please also read https://stackoverflow.com/help/minimal-reproducible-example and make sure your error corresponds to the code you show, and that others can run the code as is. – Karl Knechtel Feb 18 '22 at 21:10
  • 1
    Finally, read https://ericlippert.com/2014/03/05/how-to-debug-small-programs/ and try to diagnose the problem. Can you reproduce the problem using saved local data, without accessing a database or doing the processing? For example, can you create a local CSV file out of `whsl_updated` using `pandas.to_csv`, and then reproduce the error by loading and pushing that data?) If you can make a local CSV file that causes the problem, the next step is to eliminate data from that file until you find the part that causes the problem. – Karl Knechtel Feb 18 '22 at 21:13
  • 1
    Lastly: try [reading the documentation](https://pythonhosted.org/gspread-dataframe/). What does it say about handling non-ASCII data? (For that matter, did you *read and understand* the error message?) – Karl Knechtel Feb 18 '22 at 21:14
  • I'd like to add, that in many cases just searching online for the error message will lead you to similar problems, some even with solutiosn. – Ulrich Eckhardt Feb 18 '22 at 21:42

0 Answers0