Knack makes it possible to import records into your database from a spreadsheet. You can use this import process to both create new objects and to insert or update records to existing objects.
Note: Currently, the only limits to the size of your CSV are that it cannot be any larger than 250 MB. Trial plans are limited to 10k records.
Prepare your spreadsheet
Before you start the import, make sure your spreadsheet is optimized for importing.
Save as .CSV
The only format that is currently accepted for importing is CSV. CSV stands for "Comma separated values" and is the most common format for exporting and importing spreadsheet data.
Please consult the help files of your spreadsheet or database programs for instructions on exporting your data to a CSV file. At the time of this writing, the following is a tutorial for exporting an Excel file to csv:
Make sure your CSV file is using the comma delimiter, not a semi-colon (if you have that option).
All imports are encoded with unicode (UTF-8). Your CSV will need to be encoded as UTF-8 or Unicode to preserve any special characters in your data. Some programs (such as Excel) make it difficult to set the encoding. If you copy and paste your data into a Google Spreadsheet and then export to CSV, it will automatically be encoded as unicode.
Be careful about opening a CSV file and re-saving in a program like Excel. Doing so can add unwanted formatting -- such as trimming the leading zeros in zip codes. Saving that formatting will then result in corrupted data.
If you have any problems creating a CSV file from your data, let us know and we'd be happy to help import your data.
One way to optimize your import is to make sure your CSV only contains columns you’re going to import into Knack fields. For example, if your CSV has 1,000 columns and only two are going to be used in Knack, it would be better if your CSV only contained those two columns.
Add 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 object fields as follows:
When you create a new object 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.
If you don't have headers, that's fine -- you'll the option to create your own headers during the import process.
Format 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.
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:
Start a New Import
You can use an import to either create a new object or update an existing object.
Creating a New Object
To create a new object, click on the "+ ADD" button in the left column above any existing objects. This will open a new window. Click on the "Import a spreadsheet" tab to start the import:
You can now upload your CSV file, give your new object a name, and select whether the spreadsheet has column headers. Knack will then analyze your import and ask you to confirm the spreadsheet columns (see below).
Updating an Existing Object
To update an existing object, select that object from the left-hand menu, click on the "records" tab above the list of that object's fields, and then click the "Import" link:
This will open a new window to start the import:
You can now upload your CSV file, and select whether the spreadsheet has column headers.
Match to Existing Records
If you want to use the spreadsheet to update records that already exist, you'll need to use a field that can be matched to existing records. This will need to be a unique field (such as ID, email, or name), and your spreadsheet will need to have a column for that field.
The import will then search all the existing records for the value in that column. If it finds a match, it will use that row in the spreadsheet to update that record. If it doesn't find a match, it will insert that row as a new record.
Click "Next", and Knack will analyze your import and ask you to confirm the spreadsheet columns (see below).
Notes on Match to Existing Records:
- If you are using matching to update existing records, remove all columns from your CSV except for the matching column and the columns containing the data you wish to update.
- Matching to a Name field currently doesn't not provide consistent results in matches. We are working on this, but in the meantime suggest matching to other unique fields such as email address or an ID field.
Warning: When matching, if you import a CSV file with blank values, it will overwrite the value of all fields except for connection fields.
Map Columns to Fields
The next step is to map the spreadsheet columns to Knack fields:
Column Headers: If you indicated that your spreadsheet has column headers, they will be listed here.
Include: un-check any columns that you don't want to be included with the import.
Select the field type that matches the column. Knack will make an educated guess by analyzing the data, but confirm the selection for each column and change as necessary.
- For each column, you can choose to "Add" as a new field or "Map To" an existing field.
Data Preview: use the first rows to confirm the proper column headers and field types.
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.
You will not be able to map to any fields in your object that are non-mutable, such as auto-increment or formula fields.
Knack Tip: You can batch import images. The image field must be set to upload, not URL. The import file will contain and <img> tag or URLs to the images wherever they're currently being hosted. Upon import, the images will be uploaded to our image server. There are issues with importing images from Dropbox URLs - they have to be the specific image URL for public use.
Knack Tip: We can predict some field times upon import. If you import any columns with valid HTML, we'll predict this as a new Rich Text field. If you import values in any of our various supported phone formats, we'll predict the column as a new Phone field. If you import any valid URL formats, we'll predict the column as a new Link field, or an Image field if the URL ends in a supported image format.
You can use an import to create connections to other records. When you import records into an existing object, you can choose which field from that object to match the column in your CSV to. Likewise, you can can also match a column in your CSV to a field in a connecting object. Knack will then use that match to find a record to connect to.
These connection fields are in the format of ObjectName > FieldName and will be at the bottom of the list of current fields:
In the example above, we’re importing a CSV of employees. We match a column for company names is to a connected Company object's Company name field. The import will use the value of that column to search for any companies that have the same name. If it finds one, it will connect the employee record - created by the row in the CSV - to that company.
Importing to a Many-to-Many Connection Field
You can also import to a many-many connection field. Make sure the CSV 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
It will look like this:
First child,second child,third child
Note: If you are formatting your csv in Excel, you do not need to add the quotation marks, Excel will do this automatically. You can verify the format is correct by opening your csv in a text editor application.
Import Names & Addresses
When importing multi-column fields, you'll need to make sure you match each column to the respective field part. For addresses this would mean matching the street column to the street part, the city column to the city part, etc.
For new name and address fields, these columns should be grouped consecutively in your spreadsheet. If they are split with other columns in between them, the import will consider them two different fields.
For existing name and address fields, your existing fields will be available in the dropdown with all the address parts available:
Image fields have two options for how they load images. The first option is to upload images directly into your Knack account, which the image fields will then display. The second is to use external URLs for images already hosted elsewhere on the web, and Knack will display the images from those URLs.
You can batch import images. The image field must be set to upload, not URL. The import file will contain and <img> tag or URLs to the images wherever they're currently being hosted. Upon import, the images will be uploaded to our image server. There are issues with importing images from Dropbox URLs - they have to be the specific image URL for public use.
To import image URLs, you'll have to first make sure your image fields are formatted to use the URL option as their source:
These fields will then be available to match in your import. Because this image field must exist and be formatted as shown, images can only be imported into existing objects.
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 can't update the format for importing, you'll need to add your date field before the import and update the format to international.
Once that date field is formatted, it will be able to properly import your dd/mm/yyyy dates. Because this field must exist and be correctly formatted, you can only import international dates like this into existing objects.
Import 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/yyy 00:00am (i.e: 04/11/2017 09:00am for April 11, 2017 at 9am).
Import 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/yyy 00:00am to mm/dd/yyy 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).
Import File URLs
Note: At this time existing File fields cannot be mapped to on an import.
After confirming your fields, you'll have to the option to set defaults for the import. These defaults will be used for any fields that aren't included in the spreadsheet, or for any rows that don't have values for those fields:
Submit Your Import
Click the "Submit Import" button to submit and start the import.
Imports can take a while, particularly if you have over 1,000 records or are connecting the imported records to other objects.
It's recommended that you refrain from making other data changes to that object while the import is happening. These changes could be potentially overwritten by the data the import adds.
Once the import is completed, you'll receive a pop-up message alerting you the records are now available.