Import utf-8 encoded excel

Anvil Q&A
1

Hello everyone !

What I’m trying to do:

I’m trying to import an utf-8 encoded excel file as a dataframe and store it into a data table

What I’ve tried and what’s not working:

I tried different codes I found on the forum but I always have an error. With the code bellow which seems to be the closest to the solution the error is:
“ParserError: Error tokenizing data. C error: Expected 1 fields in line 25, saw 2”
which is the line with StringIO and to be honest I don’t know what the my_bytes, my_string and the StringIO lines are doing.
So if someone can explain it to me it’ll be very useful because it seems very useful to import data.

I hope I was clear and hope you guys can help me.

Code Sample:

@anvil.server.callable
def add_table(media):
  
    my_bytes=media.get_bytes()
    my_string = str(my_bytes, 'utf-8')
    df = pd.read_csv(StringIO(my_string))
    df = df.replace({pd.np.nan: None})
    
    for d in df.to_dict(orient="records"):
      app_tables.my_table.add_row(**d)
2

same issue, cannot find a way to fix directly

I use an excel formular to make list of dic, then copy and paste them into anvil. It works

3

Thank you for your reply
I will try it

If anyone has or founds a way to fix it directly I’m still interested though

4

Hi @Sweezy,

Have you tried something based on the code in this post? Importing from Excel - #2

I’m able to upload a UTF-8 encoded CSV with the following code (adapted from the above post):

import pandas as pd
import random

@anvil.server.callable
def add_table(media):
  tmp_name = "/tmp/%s" % "".join([random.choice("0123456789abcdef") for x in range(32)])
  with open(tmp_name, 'wb') as f:
    f.write(media.get_bytes())

  df = pd.read_csv(tmp_name)
  for d in df.to_dict(orient="records"):
      app_tables.my_table.add_row(**d)
1 Like
5

@Sweezy

From the pandas docs, pd.read_csv takes a str, path object or file-like object as its first argument:

Any valid string path is acceptable. The string could be a URL. Valid URL schemes include http, ftp, s3, gs, and file. For file URLs, a host is expected. A local file could be: file://localhost/path/to>/table.csv.

If you want to pass in a path object, pandas accepts any os.PathLike.

By file-like object, we refer to objects with a read() method, such as a file handle (e.g. via builtin open function) or StringIO.

@brooke gives an example above using a str that’s a path to a temp file.

If you want to avoid using the file system, you could modify your sample code to use a BytesIO object. Something like:

@anvil.server.callable
def add_table(media):
    df = pd.read_csv(io.BytesIO(media.get_bytes())
    df = df.replace({pd.np.nan: None})
    
    for d in df.to_dict(orient="records"):
      app_tables.my_table.add_row(**d)
2 Likes
6

Thanks for your response, I didn’t try it but I found how to do it in fact I was trying to use complex methods (maybe it isn’t but for me it is) while I juste had to use the basic python synthax:

@anvil.server.callable
def store_data(file):
  with anvil.media.TempFile(file) as file_name:
    df = pd.read_csv(file_name, delimiter=';' ,encoding='utf-8-sig', dtype='str')
    df = df.replace({pd.np.nan: None})
    
    for d in df.to_dict(orient="records"):
      app_tables.my_table.add_row(**d)

With this code I’m able to import my utf-8 encoded excel and store it into a data table which is what I was trying to do.

1 Like
7

Thanks for your responses,
I found a method to solve my problem but I’ll remember your method if I have any problem with the method I used.