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

  • Creating a New Table

  • Updating Records in an Existing Table

  • Matching to Existing Records

• Mapping Columns to Fields

  • Batch Importing Images

  • Predicting Field Types

  • Importing Connections

  • Importing to a Many-to-Many Connection Field

  • Importing Names & Addresses

  • Importing Images

  • Importing Dates

  • Importing Dates with Times

  • Importing Dates with Start and End Dates and Times 

  • Importing File URLs and Signatures

• Submit Your Import

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

• 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:

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:

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:

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:

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.

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

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

 

Matching to Existing Records

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.

 

Mapping Columns to Fields 

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

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

• Use Columns to Add New Fields: If any fields in your file 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.

 

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

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

 

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.

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 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:

 

Importing Dates

Importing dates can be tricky due to the numerous ways this data can be formatted.

You can now choose the date and time format for the date field when importing records or adding a new table by import. The following formats are available:

Dates:

• mm/dd/yyyy

• dd/mm/yyyy

• M D, yyyy

• Ignore Date

Times:

• HH:MM am

• HH:MM (military)

• Ignore 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.

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.