Please post the most simple example loading a .csv into a table

Anvil Q&A
1

form file-upload button, send that file object to a server module, read the .csv, load contents into a table. I don’t see any other way to get my data (small files) into my tables. I’ve tried making my file(s) assets, tried everything from a form, etc. I cannot get any examples, cloned app code, nothing to work. I’m on a Mac, paid version, using python 3 on the server.

Thanks in advance.

Thinking, Qlik Sense makes this SO easy. Drag and drop a .csv or .xlsx, it creates the table, loads the data, bang - ready to use. I’m writing code to use my data, trying this forum, documentation, you tube, everything - cannot get anything to actually work.

1 Like
2

To get a CSV file into a datatable (where each row in the CSV becomes a row in the datatable), I use Pandas. This is one way to do it (server-side only).

Please see this post which reads a CSV media file into a Pandas dataframe:

Please see this post that shows how to add rows to the datatable based on a Pandas dataframe:

So, if your CSV is stored as a media object in a datatable, you should be able to use the first post to put it into a Pandas dataframe, The second post shows how to convert the dataframe to a list of dicts and how to use that list to populate a datatable.

I think this is what you are asking for but please clarify if I’ve got it wrong.

3

Thanks folks - I’ll try these. Question - when I go to assets and upload a .csv file utf-8 endcoded, that is entirely different than storing that .csv file as a media object in a field in a table - correct?

4

Yes that is a different way of storing a file. Personally, I have not ever stored CSVs as an asset. I’m not saying that’s wrong, just that I have not thought to do it. My thinking for dealing with tabular data is to make use of server-side code and datatables (including media object storage).

5

so by media object storage, you mean load that .csv into a cell in a table?

like this:image

6

Absolutely.

7

ok, thanks, testing now. I searched the docs. but I suspect I don’t know where to look. I’m curious, in teh command below, my_table.get()… where do I find the syntax for this? is column= the name of the column that contains the media object? if so, what is the ‘media_column’ after that?

my_media=app_tables.my_table.get(column=‘value’)[‘media_column’]

my table has two columns - filetype, and file
I have one row, filetype is ‘marsys’ and file is the uploaded .csv file

8

Please read the docs on datatables:
https://anvil.works/docs/data-tables/data-tables-in-code

I found the Anvil tutorials very good as well if you have not gone through them.

9

Thanks, yes very helpful. That led to AttributeError: module ‘pandas.compat’ has no attribute ‘StringIO’

Other than typos or version differences, I wonder why things like this work in people’s examples but not for me. In your example it obviously worked…

10

There is almost always a very logical reason. Perhaps a typo, perhaps a Pandas version issue.

If you read the solution to the post above, you will see that it does not use pd.compat. Give Meredydd’s solution a try and see if that works.

12

Got it to work. Many thanks campopianoa for not throwing in the towel in disdain

13

My pleasure. Good luck with your development.

14

Here’s some code that might be useful:

import pandas as pd

@anvil.server.callable
def load_csv_with_pandas(file_name):
  file_row = app_tables.files.get(name=file_name)
  file = file_row['file']
  
  col_names = [f'column{i}' for i in range(18)]  
  # these are the column headers for your datatable
  # change as appropriate
  
  with anvil.media.TempFile(file) as csv_file:
    df = pd.read_csv(csv_file, header=None, names=col_names)

  for row in df.to_dict('records'):
    row = {k:v for k,v in row.items() if not pd.isna(v)}  #drop nan values
    app_tables.data.add_row(**row)

  file_row.delete() #we've uploaded the file so delete it

I think using Pandas Dataframe is best because the column types are automatically detected.

The problem with the CSV string method that you were using in the other post is that all the datatypes are strings so you have to do some extra work to convert the datatypes appropriately…

NB: This was specific(ish) to your table - though you’ll want to change list of names to be correct
If your csv had a header row then you would need to adapt the approach slightly

You may also have unecessary columns in your csv so you can drop these columns like

df = df.drop(['column5','column8'], axis=1)

I might also create a client_module for this and make it the startup form whenever I needed to load some csv to the database:

import anvil.server
import anvil.tables as tables
import anvil.tables.query as q
from anvil.tables import app_tables

def load_csv(file_name):
  anvil.server.call('load_csv_with_pandas', file_name)
  
if __name__ == '__main__':
  load_csv('MARSYS')

here’s a clone link (I took your csv from before and just used the first 5 rows for brevity):
https://anvil.works/build#clone:AMXLEFG7BNPZKAWF=JVVGS4RWW2VJUDAJIHTWZMCC

2 Likes
15

Is there anything special I need to do to get pandas in anvil? I’m getting module not found when I try to import it!

Thanks,
Heidi

16

@heidi thanks for your message. Pandas is only available on the server side, so you would need to call a server function to use it.

You can see about server functions here: https://anvil.works/docs/server

17

And if your function is already on the server make sure you’ve switched from Basic Python to Full Python 3 (individual plan or higher)

4 Likes
18

D’oh!

Full Python 3 - works great! Thanks @stucork!

1 Like