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 this import process to both create new tables and to insert or update records to existing tables.

Save as .CSV, .XLS, or .XLSX

The only formats that are currently accepted for importing are .CSV, .XLS, and .XLSX.

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:

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 green “+” 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

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.

 

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

 

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.

In the example provided, we import a file containing employee 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 employee 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. Alternatively, you have the option to include a new Company value if no match is found.

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

First child

Second child

Third child

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

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. 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 Knack as needed.

If you are using an international format (dd/mm/yyyy) and are unable to change the format for importing, it will be necessary to include your date field before initiating the import and update the format to international.

After you have properly formatted the date field, it will be ready to import your dd/mm/yyyy dates accurately. It is important to note that this field must already exist and be correctly formatted in order to import international dates like this into existing tables.

Importing Dates With Times

To import in to a Date / Time field that is set up to hold both date and time values, use the set up: mm/dd/yyyy 00:00am (i.e: 04/11/2017 09:00am for April 11, 2017 at 9am).

Importing Dates With Start and End Dates and Times 

To import in to a Date/Time field that is set up to hold a date values and start/end times, use the set up: mm/dd/yyyy 00:00am to mm/dd/yyyy 00:00pm (i.e. 04/11/2017 9:00am to 04/11/2017 5:00pm for April 11, 2017 from 9am-5pm).

If the Date/Time field is set up to have start/end dates but ignore time, use this set up: mm/dd/yyyy to mm/dd/yyyy(i.e. 04/11/2017 to 04/12/2017 for April 11, 2017 to April 12, 2017).

Importing File URLs and Signatures

Unfortunately, it is currently 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.