Upload CSV file into data tables

Anvil Q&A
1

I want to make use of Anvils in built user functions and so for everything to work as it should, I do need to upload my data tables to Anvil rather than access them externally. I’ve followed the tutorial here Anvil Docs | Files, Media and Binary Data

I put this in the server side code:


@anvil.server.callable
def store_data(file):
  with anvil.media.TempFile(file) as file_name:
    if file.content_type == 'text/csv':
      df = pd.read_csv(file_name)
    else:
      df = pd.read_excel(file_name)
    for d in df.to_dict(orient="records"):
      # d is now a dict of {columnname -> value} for this row
      # We use Python's **kwargs syntax to pass the whole dict as
      # keyword arguments
      app_tables.age.add_row(**d)

And this in the client side code

  def file_loader_1_change(self, file, **event_args):
  # Call your server function
    anvil.server.call('store_data',file)

I am getting an error

ValueError: Excel file format cannot be determined, you must specify an engine manually.

Can anyone help? What am I missing?

2

Don’t know if this is your problem, but there are a few things that you should be mindful, like it’s best to use only one sheet in a an excel file, all cells must be non-empty, and the excel file must be xls, not xlsx.

Are your files compatible with this requirements?

3

my files are single sheet csv. Also I just realised before I go too far, all my sql databases have primary keys that are an ID that Auto Increments.

These tables dont have this, so if I want to pull in data from multiple tables using keys it seems I cant do this?

For example,

on my quotes table, it will really mainly look like a list of ID’s which I would then convert back to the representative field I want to access. On looking at this it will publish the value of the field specifically that I have accessed, rather than the ID?
If so that seems pretty limiting. Or am I just not seeing this as it actually works? So if I was calling that data I’d join the tables in an sql join and say where ID = ID.
I suppose if the value is in there then I could always do were Value = value but just want to clarify, before I go too far down this route.

Here is my current sql table quotes

quotestable
quotestable1658×377 95.9 KB

4

If it were me, I wouldn’t worry about trying to upload CSVs you downloaded from your SQL tables. I’d write data conversion server functions that did the SQL queries, and looped through the results adding rows to the data tables. Those would be server functions you’d only need to run once (once you had them working), then you could comment them out.

Linking fields (foreign keys in the SQL tables) are a particular problem area that won’t work well with CSV import. You really need to be in Python code so you can do a separate look up of the foreign key in the other data table to get the row, and use that row for the linking field.

5

UPDATE: I tried, re-doing the tile as an xls file, I am still getting the error. I have followed Anvils exact example. :frowning:

6

The easiest way is to use excel to make a new column and then make a dictionary contain data of all cells.

Then copy all rows of dics into a list in server module.

Then insert each item to table.

7

@Tony.Nguyen :firecracker:<<brain melt! That doesnt sound too easy for me, especially when I am using Anvils tutorial on how to do it
@jshaffstall :firecracker::firecracker:<<double brain melt! I think I may have to go back to the drawing board on that! I really only want to use the user functions in anvil, as its easy, but then I need to refer all the user data via the server. I’m sure there are other ways, so I will have to look at those and go back to plan A - external user tables.

8

It is my excel formular

="{'id':  """&A5400&""", 'name': """&C5400&"""},"

And here is the server code

my_list = [
{'id':  "real_value1", 'name': "real_value2"},
{'id':  "real_value2", 'name': "real_value2"}
]

for item in my_list:
  app_tables.my_table.add_row(id=item["id"], name=item["name"])
9

I think it may be a non starter for consideration anyway, but thanks for the help, I will look into it

10

It is actually easier to make it works than your code taken in the tutorials. I cannot make the tutorial code work as well.

Good luck.

1 Like
11

I am not sure that loading a bunch of data from one database and or service, to the other, one time, statically is the greatest approach, unless you are migrating completely to anvil and never looking back to any of your other services and technology like MySQL etc.

I keep getting stuck on the reason why we are uploading from the excel file, thinking there might be a better way to go from one step back?

I am unsure what you mean or want to accomplish by this:

I think a better approach might be to pull one user at a time on the fly to an anvil data table, from your MySQL server, with only the information/ columns required for the user to use anvil.

A function could be created in the anvil server module to check if the user exists in anvil, and instead of prompting the person to create a new user themselves, one would be pulled from your MySQL DB, creating the user in anvil the first time.

All of your relational joins could be handled through writing MySQL querys that you may be more familiar with , returning a single resulting row and inserting the relevant information directly into the users table.

1 Like
12

Thanks Ian, I have one thing I’m going to try and then if that fails will look at next steps. Its a school project and the aim was for me to just learn Anvil so I can teach my daughter how to use it, just to help save a bit of time. She has exams coming up and so to spend weeks learning something is just going to take up too much time, the plan was really that I learn Anvil, then instruct her on how to set up her project.

Where as I do know the UI side and MySql, she doesn’t and its just too much of an ask at this stage for her to learn all that in time to get anything done. So I’m not too worried about the longer term side of things, more that as much as poss of the UI is done by Anvils drag and drop features and she can focus on the python which is what she’s learned.

Hope that explains my thinking a bit better. Many thanks

13

Ahh ok, how about you build an sql query in PHPMyAdmin that does all the most relevant table joins and then export the results to a CSV as you like it, with all of the connected relational information. The export function in PHPMyAdmin is actually pretty easy to use and you can set your own delimiters, etc.

Once you have a csv (the harder part) you can upload it using some simpler methods like this one:

This post was really old, and I probably would not write it the same way again, but it should still work none the less.

1 Like
14

Oh also I just had a thought to answer your original question at the top, are you trying to use the free plan with pandas?

image
image846×418 24.7 KB

If not, and you are using anvil uplink, you may need to install both pandas AND either xlrd or openpyxl for pandas to open an excel file.

Also, heres a stackoverflow thread where someone was having the same issue:

15

Hi

Im not sure what plan we’ve been given the equivalent of, because the team at Anvil very kindly created a free education plan for the duration of my daughters A levels, so she can produce her project, but it does include the full python packages so I’m pretty sure pandas is working. But… I dont have xlrd or openpyxl, would I need that if I’m not using uplink?

16

I’m not sure if this is helpful (as I’m not following your question about database IDs), but I was able to get the Uplink option of the Anvil tutorial to work when creating this:

17

Hi

Thank you for the information, I did manage to import the data using a function as I had sql connection to my external database, though I am struggling with some of the queries as its not the same as calling SQL so I may have to revert.

Many thanks anyway.

1 Like
18

A post was merged into an existing topic: Colab notebook for uploading CSV or Excel