Importing Records

This article provides instructions on importing records into your Knack app, along with an overview of the import process.

Knack makes it possible to import records into your database from a spreadsheet. You can use the import process to both create new tables and to insert or update records to existing tables.

Note: Currently, the only limits to the size of your file are that it cannot be any larger than 250 MB. Trial plans are limited to 10k records.

 

This article covers the following topics:

 

Save as .CSV, .XLS, or .XLSX

We currently support various formats for importing, including .CSV, .XLS (Excel), and .XLSX (Excel).

Adding Column Headers

It's highly recommended that you add a single row at the top of your spreadsheet that will act as the headers (or label) for each column. These headers are used with the table fields as follows:

  • When you create a new table using an import, each header is used as the name of the field that’s automatically created for that column.

  • When you update existing records, the header is used to match that column with an existing field. It uses the header to find any fields with the same name.

Formatting Your Data

It's worth reviewing your data and making sure that the formatting is consistent before importing. This can be much more painful to correct after the import.

For example, say you have a spreadsheet of t-shirts, including a "Size" column that you want to be a multiple choice. These sizes need to be consistent. If you have values of "large," "Large," and "L,” each of these will be a different option for size in your new Knack “Size” field.

Multi-Column Fields

Some fields, like names and addresses, can have more than one component and will need to be separated into multiple columns. For example, a name field can contain a title, a first name, and a last name.

If you want to import columns into these special field types, you'll need to follow these rules:

  • Make sure each component is in a separate column. If you have the full name in one column, the import will not be able to parse out the first name from the last name. It will just treat it as a normal text field (without the special formatting of a name or address field).
  • Make sure the columns are sequential and not separated by other fields. The import will combine all sequential name and address fields into a single field.

The following is an example of correctly formatted address fields in a spreadsheet:

importrecords1

Starting a New Import

You can use an import to either create a new table or update an existing table.

Creating a New Table

To create a new table, click on the “+” button in the left column above any existing tables. This will open a new window:

importingrecords1

Select the "By Import" tab to start the import process. You can now upload your file by dragging and dropping it into the space provided, or by clicking the space to open your computer’s file finder:

importingrecords2

You will then be directed to define your headers and map your columns. Once that is done, you can give your new table a name and start the import:

importingrecords3

Updating an Existing Table

To update an existing table, navigate to the Records section in the Builder and choose the desired table from the menu on the left-hand side. Next, click on the Import option located at the top of your list of records.

importingrecords4

A new window will be opened to initiate the import process:

importingrecords5

You then have the option to upload your file and indicate whether the spreadsheet includes column headers.

importingrecords6

 

Matching to Existing Records

In order to update records that already exist using the spreadsheet, you will need to select a field that can be matched to the existing records. This field should be unique, such as an ID or email, and your spreadsheet should contain a column for that field.

During the import process, Knack will search for the value in the specified column among all existing records. If a match is found, the corresponding row in the spreadsheet will be used to update that record. If no match is found, the row will be inserted as a new record.

Click "Next", and Knack will analyze your import and prompt you to confirm the columns in your spreadsheet (as shown below).

Notes on Matching to Existing Records:

  • If you're using matching to update existing records, simply remove all columns from your file except for the matching column and the columns that contain the data you want to update.

  • Please note that matching multi-part fields, such as Name & Address fields, may not consistently provide accurate results. We're actively working on improving this, but in the meantime, we recommend matching to other unique fields such as email address or an ID field.

Caution: If you import a file with blank values during the matching process, it will overwrite the value of all fields with the exception of connection fields.

 

Mapping Columns to Fields 

The next step is to map the spreadsheet columns to Knack fields:

importingrecords7

  • Map Columns to Knack Fields: Here's where you are able to select which columns in your file should be connected to the existing fields in your table.

  • Use Columns to Add New Fields: If there are any fields in your file that cannot be matched to an existing field in Knack, you have the option to choose the appropriate field type for that column in the file. By selecting this option, a new field will be created in your table and populated with the data from your file.

  • Data Preview: On the right side of the page, you'll find a data preview of your file. Take a look at the first few rows to make sure the column headers and field types are correct.

Tip: If you are updating records, you'll want to select the "Map To" option and map each column to an existing field. These will be available above any field types.

Note: You will not be able to map to any fields in your table that are non-mutable, such as auto-increment or formula fields.

 

Batch Importing Images

You have the ability to import images in batches. However, please note that the image field should be set to upload, not URL. In the import file, you will find either an <img> tag or URLs to the images, depending on where they are currently hosted.

When you import the images, they will be automatically uploaded to our image server. However, please note that there may be some issues when importing images from Dropbox URLs. The URLs must be specific image URLs that are publicly accessible in order for the import to work correctly.

Predicting Field Types

We have the ability to predict certain field types during the import process. If you import any columns with valid HTML, we will automatically recognize them as new Rich Text fields. Similarly, if you import values in any of our supported phone formats, we will identify the column as a new Phone field.

When you import valid URL formats, Knack will automatically identify the column as a new Link field. If the URL ends in a supported image format, it will be predicted as an Image field.

Importing Connections

You have the ability to establish connections between records by using the import feature. When importing records into an existing table, you have the flexibility to select the field from that table that corresponds to the column in your file.

Similarly, you have the option to match a column in your file with a field in a connecting table. Knack will utilize this match to locate a record to establish a connection with.

When importing data into a connection field, Knack provides you with the flexibility to select the column in your file that corresponds to the connection field. You can also choose which field in the connected table matches with that column in your file, and decide what action should be taken if no match is found.

importingrecords10

In the example provided, we import a file containing Account data. We match a column for company names to the Company name field in the connected Company table.

The import process will utilize the value in that column to search for companies with the same name. Once a match is found, the Account record created by the corresponding row in the file will be connected to that company.

If no match is found, the import will disregard the Company value in the file and not input any value for the "Company" connection field in the Account record. Alternatively, you have the option to include a new Company value if no match is found which will then trigger Knack to create a new record in the Companies table.

Importing to a Many-to-Many Connection Field

You also have the option to import data into a many-many connection field. Just make sure that the file is formatted correctly so 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

  • Contained within a single cell

Here's examples of how it should appear:

First child,second child,third child

Or:

First child

Second child

Third child

Note: When formatting your CSV in Excel, there is no need to manually include quotation marks. Excel will automatically add them for you.

To ensure that the format is correct, you can open your CSV file in a text editor application and verify it.

 

Importing Names & Addresses

When importing multi-column fields, it is important to ensure that you match each column to the corresponding field part. This is especially true for addresses, where you need to match the street column to the street part, the city column to the city part, and so on.

importingrecords9

If you're adding new name and address fields, make sure to group the corresponding columns consecutively in your spreadsheet. If there are other columns in between, the import will treat them as separate fields.

 

Importing Images

There are two ways to load images into image fields:

1. You can upload images directly into your Knack app. Once uploaded, the image fields will display the images.

2. Alternatively, you can use external URLs for images that are already hosted elsewhere on the web. Knack will display the images from those URLs.

To import multiple images at once, make sure that the image field is set to "upload" instead of "URL". 

The import file will include an <img> tag or URLs to the images from their current hosting location. When you import the file, the images will be automatically uploaded to our image server.

Note: There may be some issues when importing images from Dropbox URLs. The URLs must be specific image URLs that are publicly accessible for the import to work correctly.

To import image URLs, you need to ensure that your image fields are set up to use the URL option as their source:

importingrecords8

Note: You will be able to match these fields in your import process. However, please note that in order to import images, the image field must already exist and be formatted as shown. 

 

Importing Dates

Importing dates can be tricky due to the numerous ways this data can be formatted. If at all possible, we recommend formatting your dates as follows:

  • mm/dd/yyyy (i.e. 03/28/2004 for March 28, 2004)

Once imported, you can then change the format in the field's settings as needed any time.

Importing Dates with Times

You can import into a Date/Time field that is set up to hold both date and time values.

Importing Dates with Start and End Dates and Times 

You can also import into a Date/Time field that is set up to hold date values and start/end times

Importing File URLs and Signatures

Currently, it is not possible to map existing file fields during the import process or import into signature fields.

 

Submit Your Import

Click on the "Submit Import" button to proceed with submitting and initiating the import process.

Note: Imports may take some time, especially if you have more than 1,000 records or if you are connecting the imported records to other tables.

It is advisable to avoid making any modifications to the data in the table during the import process. This is to prevent any potential overwriting of changes by the imported data.

Once the import process is finished, a friendly pop-up message will appear to let you know that the records are now available.

 

Notes & Troubleshooting

  • Currently, on an import, a field's required setting is not checked.
  • If you import your data via API call, this method will enforce the unique setting that can be applied to certain field types. When importing your file via the Builder, this method will not enforce the unique setting.