File Format Guide for Importing

In this article, you will find information on how to optimize your spreadsheet before importing.

This article covers the following topics:

• Save as .CSV, .XLS, or .XLSX

• Add Column Headers

• Format Your Data

• Predictive Imports

• Importing to a Many-to-Many Connection Field

• Multi-Column Fields

Save as .CSV, .XLS, or .XLSX

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

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, let's say you have this text:

"hello, world"

In your spreadsheet, this needs to be:

"hello, world",

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.

• 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 file 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

3. Contained within a single cell

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: