Table of Contents
Import Records
Updated
by Lesley
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.
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.
Notes:
- 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 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.
If you don't have headers, that's fine. You'll have 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.
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:
Start 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:
Click on the "By Import" tab to start the import process. You can now upload your CSV file by dragging and dropping it into the space provided, or by clicking the space to open your computer’s file finder:
Once you define your headers and map your columns, give your new table a name and start the import:
Updating an Existing Table
To update an existing table, click on the Records section of the Builder and select that table from the left-hand menu. Click on the Import option at the top of your records list:
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 or email), 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 multi-part fields, such as Name & Address fields, currently do 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.
Map Columns to Fields
The next step is to map the spreadsheet columns to Knack fields:
- Map Columns to Knack Fields: This is where you can choose to map the columns from your CSV file to the existing fields in your table.
- Use Columns to Add New Fields: For any fields in your CSV file that can’t be mapped to an existing field in Knack. Here you can choose the appropriate field type for that column in the CSV file. This option will create a new field in your table, and populate that field with the data from your CSV file as well.
- Data Preview: To the right of the page you will see a data preview of your CSV file. 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 table that are non-mutable, such as auto-increment or formula fields.
Import Connections
You can use an import to create connections to other records. When you import records into an existing table, you can choose which field from that table to match the column in your CSV to. Likewise, you can also match a column in your CSV to a field in a connecting table. Knack will then use that match to find a record to connect to.
When importing into a connection field, Knack gives you the option to choose the column in your CSV file to match to the connection field, choose which field in the connected table matches to that column in your CSV file and choose what happens if no match is found.
In the example above, we’re importing a CSV of employees. We match a column for company names to a connected Company table'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. If no match is found, the import will skip the Company value. You can also choose to insert a new Company value if no match is found as well.
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
- Contained within a single cell
It will look like this:
First child,second child,third child
Or this:
First child
Second child
Third child
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.
Import Images
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 tables.
Import 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 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 tables.
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/yyyy 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/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).
Import File URLs
At this time existing File fields cannot be mapped to on an import.
Import Signatures
At this time it's not possible to import into Signature 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 tables.
It's recommended that you refrain from making other data changes to that table 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.
Notes & Troubleshooting
- Currently, on an import, a field's Required setting is not checked.
