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 insert or update records to existing tables.

 

This article covers the following topics:

• Save as .CSV, .XLS, .XLSX, or Import with Google Sheets

  • Adding Column Headers

  • Formatting Your Data

  • Multi-Column Fields

• Starting a New Import

  • Adding a New Table

  • Blank Table

  • Pre-made Table

  • Importing a Data File to Add a New Table

  • Person, Date/Time, and Address Field Options

  • Confirm Your Import

  • Importing a Data File to Update an Existing Table

  • Advanced Import Options

  • Batch Importing Images

  • Predicting Field Types

  • Importing Connections

  • Importing to a Many-to-Many Connection Field

  • Importing Images

• Notes & Troubleshooting

 

Save as .CSV, .XLS, .XLSX, or Import with Google Sheets

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

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 labels) 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).

• Columns do not have to be sequential in the upload. They can be separated by other fields. The import will combine only the columns you select into the multi-column field.

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

Starting a New Import

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

In this section, we'll start with the options for adding a new table. To learn about updating an existing table with an import, see this section below. 

Adding a New Table

To create a new table, click on the “+” button in the left column:

You will then have options to upload your file, create a blank table, populate a pre-made template table, or upload data from Google Sheets.

To upload a file, you can start by dragging and dropping it into the space provided, or by clicking into the space outlined to open your computer’s file finder.

Blank Table

By selecting the Blank Table option, you can create a new table without importing data into it immediately. You will be directed back to the Data section of the Builder with your new table highlighted on the left:

Pre-made Table

By selecting the Pre-Made Table option, you can pick between several template tables offered:

Importing a Data File to Add a New Table

After uploading your data file, Knack will predict and allow you to edit field type predictions on the left based on your data:

Person, Date/Time, and Address Field Options

If you're importing name data, or address data, you will need to have the data for these separated into multiple columns. For more information about multi-column fields, see our section here.

The options for mapping your data file's column for addresses are the following:

• Street Address

• Street Address 2

• City

• State / Region / Province

• Zip / Postal Code

• Country

• Latitude

• Longitude

You can map person field data from your column to the following options:

• Title

• First Name

• Middle Name

• Last Name

For date/time data in your file, the following options are:

• Selecting the date format

  • mm/dd/yyyy

  • dd/mm/yyyy

  • M D, yyyy

  • Ignore Date

• Selecting the time format

  • HH:MM (am/pm)

  • HH:MM (24 hour)

  • Ignore Time

 

Confirm Your Import

The final step of the import process summarizes the number of records being imported and the destination data table.

You can also choose how to handle records with import errors:

After selecting the Import button, you will be directed to the Records view of your new table with a progress bar showing the import status:

 

Importing a Data File to Update 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.

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

When importing a data file to update an existing table, Knack will predict and allow you to edit the existing fields selected or create new fields for columns in your file.

Advanced Import Options

After the above step 2 of the import process, you will have the option to select whether you want to update existing records with your data file or insert each row as a new record.

When selecting yes, you can choose to update records where the following fields match. To add more than one rule, there is an option to select "+ Add Rule" below.

Lastly, if no matching record is found, you can select to insert the data file's row as a new record or skip the row and do nothing.

After selecting the next button in step 3 of the import process, you will be directed to the Confirm Your Import screen, where you will have the option to set defaults for any empty or missing fields:

Batch Importing Images

You can 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 for the import to work correctly.

Predicting Field Types

Knack can 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 can 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. 

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

 

Importing Images

There are two ways to load images into image fields:

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

• 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 in the image field's field settings, the image source is set to "Upload image files" instead of "Load images from an external URL". 

The import file will include a <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.

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