Table of Contents

CSV Format Guide for Importing

Lesley Updated by Lesley

Before you start the import, here is an overview to 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.
  • 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 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.

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:

hello, world

In your spreadsheet, this needs to be:

"hello, world",

All values, including blank values, must be contained in double quotes so that the comma is escaped.

Predictive Imports

In Knack, we can predict some field types during the import process:

  • Images: If you import an <img> tag or URL ending in a supported image format, we'll predict this as a new Image field. We'll download the image and then upload it to your file storage storage.
  • Links: If you import any valid URL formats, we'll predict the column as a new Link field, with the exception of image URLs as mentioned above.
  • Phone Numbers: If you import values in any of our various supported phone formats, we'll predict the column as a new Phone field.
  • Rich Text: If you import any columns with valid HTML, we'll predict this as a new Rich Text field.

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:

  1. Comma-separated or row separated,
  2. WITHOUT a space before or after each comma, and
  3. Contained within a single cell

It will look like this:

First child,second child,third child

Or this:

First child 

Second child 

Third child

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.

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:

image alt text

How did we do?

Delete Records

Contact