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, .XLSX, or Import from Google Sheets

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

Tip: See our article here for more information on importing with Excel files.

 

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 labels) 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 headers during the import process.

fileformat1

 

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",

Notes:

  • All values, including blank values, must be contained in double quotes so that the comma is escaped.
  • A field's "Required" setting is not checked during the import. 

 

Formatting Dates/Times

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:

To import into a Date/Time field that is set up to hold date values and start/end times, we recommend using this format: mm/dd/yyyy 00:00am to mm/dd/yyyy 00:00pm 

Example:
April 11, 2024 from 9 am - 5 pm would be formatted as 04/11/2024 9:00am to 04/11/2024 5:00pm

If the Date/Time field is set up to have start/end dates but ignores time, we recommend using this format: mm/dd/yyyy to mm/dd/yyyy

Example:

April 11, 2024 to April 12, 2024 would be formatted as 04/11/2024 to 04/12/2024.

Once imported, you can change the format in the date/time field's settings as needed.

 

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

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.

*Excel files are now accepted for importing! See this article here for more information.

 

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:

fileformatguide1