Docs
Switch to Classic Editor docs Switch to Beta Editor docs
You are currently viewing the new Beta Editor Docs.
Switch to the Classic Editor Docs
You are currently viewing the Classic Editor Docs.
Switch to the Beta Editor Docs

Importing data from CSV and Excel

Anvil apps have a full Python instance server-side, which makes it simple to import data from CSVs and Excel files into your Data Tables.

There are two ways to do this:

  1. Using a FileLoader Component in a running Anvil app
  2. Accessing files locally using the Anvil Uplink

We’ll cover both methods, and you can download our sample CSV and Excel files here:

File upload in a running app

First, we’ll import data to Anvil’s Data Tables using a FileLoader component in a running Anvil app.

Create a new app, add the Data Tables service, and create a table.

Ensure that ‘Auto-create missing columns when adding rows’ in the top right hand corner is checked:

Add a Server Module, and select the ‘Full Python 3’ runtime in the dropdown in top right:

Create a new app, navigate to the Data service and create a table.

Navigate to ‘Settings’ in the Sidebar Menu, and ensure that ‘Auto create missing columns’ is checked in the ‘Data Tables’ tab:

Still in the ‘Settings’ menu, select the ‘Full Python 3’ server runtime under ‘Python Versions’:

Next, a Server Module.

Users on the Free Plan will need to use the Uplink rather than Anvil’s Server Modules to run their server-side code. This is because our example use the pandas library which requires the Full Python server instance, available on any of our paid plans.

Add these lines to your Server Module:

# Add these lines to the top of your Server Module
import pandas as pd
import anvil.media

@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.your_table_name_here.add_row(**d)
Make sure you change your_table_name_here to the name of the Data Table you just created!

Go back to ‘Form1’, and drop a FileLoader component An Anvil FileLoader into your UI.

The FileLoader’s change event is raised when a user selects a file. Its file argument is a Media object containing the chosen file.

Double click the FileLoader in your UI to create the file_loader_1_change event, and edit the function to look like this:

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

Run your app, and upload a CSV or excel file into the FileLoader component.

Stop your app, navigate to your Data Tables, and you’ll see your data has been imported.

If you’d rather access your files locally with Python, rather than uploading them in a running Anvil app, you can use the Uplink to do this.

Create a new app, add the Data Tables service, and create a table.

Ensure that ‘Auto-create missing columns when adding rows’ in the top right hand corner is checked:

Create a new app, navigate to the Data service and create a table.

Navigate to ‘Settings’ in the Sidebar Menu, and ensure that ‘Auto create missing columns’ is checked in the ‘Data Tables’ tab.

We’ll use the pandas and xlrd libraries, so install these:

pip install pandas
pip install xlrd

Import from CSV

This Python script will import data from a CSV file to your Data Table:

import pandas as pd
import anvil.tables as tables
from anvil.tables import app_tables

def import_csv_data(file):
  with open(file, "r") as f:
    df = pd.read_csv(f)
    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.your_table_name_here.add_row(**d)
Make sure you change your_table_name_here to the name of the Data Table you just created!

Import from Excel

This Python script will import data from an Excel file to your Data Table:

import pandas as pd
import anvil.tables as tables
from anvil.tables import app_tables

def import_excel_data(file):
  with open(file, "rb") as f:
    df = pd.read_excel(f)
    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.your_table_name_here.add_row(**d)
Make sure you change your_table_name_here to the name of the Data Table you just created!

Next, install the Anvil Uplink library:

pip install anvil-uplink

Enable the Uplink in your app, and then paste the connection code into your script:

# Add these lines underneath your import statements
import anvil.server
anvil.server.connect("YOUR-UPLINK-KEY")  # Make sure you replace this with your own Uplink key

Run your scripts

Run your script, calling your functions and passing in the CSV or Excel files you want to import.

Stop your app, navigate to your Data Tables, and you’ll see your data has been imported.

Example script

Here’s an example script that uploads both colours.csv and colours.xlsx to an Anvil Data Table:

import pandas as pd
import anvil.tables as tables
from anvil.tables import app_tables

import anvil.server
anvil.server.connect("YOUR-UPLINK-KEY")  # Make sure you replace this with your own Uplink key

def import_csv_data(file):
  with open(file, "r") as f:
    df = pd.read_csv(f)
    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.your_table_name_here.add_row(**d)

def import_excel_data(file):
  with open(file, "rb") as f:
    df = pd.read_excel(f)
    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.your_table_name_here.add_row(**d)

import_csv_data("colours.csv")
import_excel_data("colours.xlsx")
SQL Access and Indexing External database

On This Page: