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

Importing data from CSV and Excel

Using Data Files

The Data Files service makes it straightforward to import data from CSVs and Excel files into your Data Tables. The Data Files documentation shows you how to upload files to your Anvil app and interact with those files from Python code. Learn more here: Anvil Docs | Data Files

Screenshot of the Data Files upload panel.
Data Files are the simplest way to import CSV and Excel data into a Data Table, but the feature is not available in the Classic Editor. Keep reading to learn how to import CSV and Excel data in the Classic Editor, but instead, consider switching to the New Editor.

In the Classic Editor, there are two methods for importing CSV and Excel data into a Data Table:

  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:

1. File upload in a running app

Instead of using Data Files in the New Editor to upload files to your app, you can use a FileLoader to access your files locally. You can download our sample files here:

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:

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.

Instead of using Data Files to upload files to your app, you can use the Uplink to access your files locally. You can download our sample files here:

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")

Do you still have questions?

Our Community Forum is full of helpful information and Anvil experts.

SQL Access and Indexing External database

On This Page: