Import error:excel to Data table

Anvil Q&A
1

Hi,

While importing the excel file I want also to import a column with users (users are different names) avoiding to do it manually. In the excel file the “users” are strings and get the error that User is linked from another table of users in Anvil.

Is there any way to do it with some sort of format or anything else based on the script below?

Thank you

Code Sample:

@anvil.server.callable
def store_data(file):
  with open(file.name, "rb") as f:
    df = pd.read_excel(f)
    for d in df.to_dict(orient="records"):
      app_tables.input.add_row(**d)
2

It sounds like you have a conflict between the columns as defined in app_tables.input, and the columns as defined in the dataframe.

Folks will probably need to see the actual column definitions, or at least the ones that are in conflict.

3

Perhaps you should replace the user name with a pointer to the row in the user table. Something like this:

    for d in df.to_dict(orient="records"):
      d['user_sh'] = app_tables.users.search(username=d['user_sh'])
      app_tables.input.add_row(**d)
2 Likes
4

The column in app_table is defined as User row(as below)

image

In the excel file these names (users) are strings and the error message:

TableError: Column ‘user_sh’ is e Row from ‘Users’ table - cannot set it to a string

5

Agree. Just replace username with whatever column name @besarberdica is actually using in his Users table.

6

Using this line, nowis giving the following error:

TableError: Column ‘user_sh’ is e Row from ‘Users’ table - cannot set it to a anvil.tables.SearchIterator object

@anvil.server.callable
def store_data(file):
  with open(file.name, "rb") as f:
    df = pd.read_excel(f)
    for d in df.to_dict(orient="records"):
       d['user_sh'] = app_tables.users.search(email=d['user_sh'])
       app_tables.input.add_row(**d)
7

Sorry, that shouldn’t be search. That should probably be get.

See Links Between Tables.

1 Like