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.
Another 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 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.
Any data containing commas is also worth a review. For values with commas, the entire value needs to be quoted. For example, say you have this text:
In your spreadsheet, this needs to be:
All values, including blank values, must be contained in double quotes so that the comma is escaped.
Importing to a Many-to-Many Connection Field
When importing 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:
- Between a single pair of double quotes,
- 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.
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: