Importing & Exporting Data

Learn how to bring existing data into your Knack tables and export your data when needed. This guide covers file formats, mapping fields, and best practices for moving data in and out of your app.

Prepare the Data

Import to Create a New Table

Import to Update an Existing Table

Advanced Import Options

Importing into Connection Fields

Watch and Learn: Import Demos

What you'll learn:

• How to import data from spreadsheets
• Ways to export your data
• Tips for preparing your data
• How to handle different file formats

Importing Data

Knack supports importing data in multiple formats, including CSV, XLS/XLSX (Excel), and Google Sheets.

Before You Start: Preparing Your Data

1. Add Column Headers

Headers are used to match your spreadsheet columns with Knack table fields.

• For new tables: Each header becomes the name of a new field.
• For updates: Headers match spreadsheet columns to existing fields in the table.
• 💡 Use clear and descriptive headers to avoid confusion during the import.

2. Format Your Data

Ensure data is consistent to avoid errors.

• Example: For a "Size" field, avoid mixing formats like "large," "Large," and "L." Instead, standardize to one format.
• Learn more about formatting data here.

3. Multi-Part Fields

For fields like Name or Address, split data into separate columns (e.g., First Name, Last Name, City, State).

• Columns don’t need to be adjacent in your file. Select the relevant ones during the import process.
• Here's an example of how to format multi-part fields in a spreadsheet

Starting Your Import

Adding a New Table

1. Go to the Data section in the Builder.
2. Click the Add Table button in the left panel
3. Choose from:

• Upload a File: Drag and drop or browse to upload.
• Google Sheets: Follow the connection prompts.
• Blank Table: Create without importing data.
  

  Select the Blank Table option to create a new table without importing data into it. You will be directed back to the Data section of the Builder with your new table highlighted on the left.

• Pre-Made Table: Use a Knack template.
  

  Select the Pre-Made Table option to create one or more tables, each with some fields to start you off with. 

Importing a Data File

1. Upload your file. Knack predicts field types based on your data.
2. Adjust field names and types as needed.
3. Map Columns:
   • Skip unwanted columns by unchecking them.
   • Map valid fields only (e.g., avoid auto-increment fields).
   • 💡 Date/Time Fields: Select how the date and time formats are set in your file during mapping. Example:
     • Date: MM/DD/YYYY, DD/MM/YYYY
     • Time: HH:MM AM/PM, HH:MM (24hr)

Importing into an Existing Table

To import data into an existing table:

1. Navigate to the table’s Records section.
2. Select the Import button by clicking on the ellipses next to the table name.
3. Upload the .csv, .xls, .xlxs, or Google sheet
5. Map fields - Match file headers to existing table fields, or create new fields as needed.
6. Review advanced options:
   • Update or Insert: Choose rules for matching records.
   • Handle Errors: Decide whether to skip or insert rows with issues.
   • Default Values: Set default values for empty fields

Handling Special Field Types

1. Name and Address Fields: Ensure data is split into separate columns (e.g., First Name, Last Name, City).
2. Date/Time Fields: Select how the date and time formats are set in your file during mapping. Examples:
   • Date: MM/DD/YYYY, DD/MM/YYYY
   • Time: HH:MM AM/PM, HH:MM (24hr)

Batch Importing Images

• Upload: Use image files or publicly accessible URLs.
• Ensure URLs are formatted correctly (e.g., Dropbox links must be public).
• 💡 Set the image field to the correct source type (upload vs. URL).

Importing Connections

One-to-Many Connections

You can utilize the import feature to establish connections with other records. By importing records into an existing table, you have the flexibility to select which field from that table should match the corresponding column in the file you are importing.

It is also possible to match a column in your file with a field in a connecting table. Knack will then use that match to find a record to connect to.

The connection fields will have the symbol shown below to the left of "Client":

In the example above, we’re importing a file containing Contact records. We’re matching the data in the CSV file to the records that already exist in Knack using the "Phone" column.

This field was chosen because it contains a unique value for each record.

The import will use the value of that column to search for any Client records that have the same value. If Knack finds a match, it will connect the Contact record, created by the row in the imported file, to that specific Client record.

Importing to a Many-to-Many Connection Field

You can also import to a many-many connection field. To ensure a successful import for many-to-many connection fields, both tables must first exist as well as the associated many-to-many connection field.

Ensuring both tables and the connection field exist before importing is crucial to ensure that your data imports correctly as multiple connected records rather than being treated as a one-to-many connection. During the import process, you will not have the option to choose the connection type when mapping new fields to your data.

Additionally, ensure the data file is formatted such that the multiple records to be stored in the connection field are:

• Comma-separated or row-separated,

• WITHOUT a space before or after each comma, and

• Contained within a single cell

Your data can look like this:

First child,second child,third child

Or, like this:

First child

Second child

Third child

Common Import Issues

• Incorrect date formats
• Missing required data
• Duplicate entries
• Special character problems

Exporting Data

Basic Export

1. Open your table
2. Click "Export"
3. Choose format (CSV/Excel)
4. Select fields to include
5. Download file

Export Options

• Select specific records
• Choose fields to include
• Set date ranges
• Format options

Export Formats

• CSV (most compatible)
• Excel (formatted)
• Print view
• PDF reports

Best Practices

Before Importing

1. Check Your Data
   • Look for missing information
   • Fix formatting issues
   • Remove duplicate entries
   • Standardize values
2. Prepare Your Table
   • Create needed fields
   • Set up connections
   • Configure required fields
   • Plan for new data
3. Test First
   • Import a few records
   • Check the results
   • Adjust if needed
   • Then do full import

Managing Exports

1. Regular Backups
   • Schedule regular exports
   • Keep backup copies
   • Document your exports
   • Store safely
2. Organizing Exports
   • Use clear file names
   • Include dates
   • Note what's included
   • Track versions

Tips for Success

1. File Preparation
   • Use clear column headers
   • Clean up formatting
   • Remove extra spaces
   • Check for errors
2. Import Strategy
   • Start with clean data
   • Map fields carefully
   • Review before confirming
   • Keep source files
3. Export Organization
   • Label files clearly
   • Note what's included
   • Track versions
   • Store securely

Tip: Always keep a backup of your original data before importing, and document any changes you make during the process.

Troubleshooting

Common Import Issues:

• File won't upload? Check format and size
• Mapping errors? Review column names
• Missing data? Check required fields
• Duplicates? Check unique fields

Common Export Issues:

• Missing records? Check filters
• Format problems? Try different format
• Too large? Export in sections
• Connection data? Review settings

Watch and Learn

Import data into an existing table

Import data to create a new table

Importing data from a Google Sheet

Importing Multi-Part Fields, like Address and Person