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

• Add Column Headers

• Format Your Data

• Formatting Date/Times

• Predictive Imports

• Importing to a Many-to-Many Connection Field

• Multi-Column Fields

• Importing into Rich Text Fields

• Troubleshooting

Save as .CSV, .XLS, .XLSX, or Import from Google Sheets

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

 

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.

 

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

 

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:

• For importing into a date/time field configured to accommodate date values along with start and end times (where the Calendar Options in the field settings is set to "Yes"), we recommend ensuring that all related data is contained within a single column in your spreadsheet and in the following 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
• When the date/time field is configured to include start and end dates without time, we recommend ensuring that all related data is contained within a single column in your spreadsheet and in the following 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.

For date/time fields with calendar options enabled:

If your data isn't already formatted as specified above, you can use formulas in Google Sheets or Excel to ensure proper import into Knack. Follow these steps:

1. Create a new column in your spreadsheet.
2. Apply the appropriate formula to format your date/time data correctly.
3. Use this newly formatted column when importing data into new or existing date/time fields with calendar options enabled.

This approach ensures that your date/time information will be imported correctly into Knack.

Here are a few scenarios to illustrate the process:

Scenario 1

In the scenario where your data consists of three columns labeled as Date, Start Time, and End Time:

Google Sheets formula for the Output column: 

=TEXT(A1, "dd/mm/yyyy") & " " & TEXT(B1, "h:mmam/pm") & " to " & TEXT(A1, "dd/mm/yyyy") & " " & TEXT(C1, "h:mmam/pm")

Excel formula for the Output column: 

 =CONCATENATE(TEXT(A1,"dd/mm/yyyy")," ",TEXT(B1,"h:mm AM/PM")," to ",TEXT(A1,"dd/mm/yyyy")," ",TEXT(C1,"h:mm AM/PM"))

Scenario 2

In the scenario where your data consists of four columns labeled as Start Date, End Date, Start Time, and End Time:

Google Sheets formula for the Output column: 

=TEXT(A1, "dd/mm/yyyy") & " " & TEXT(C1, "h:mmam/pm") & " to " & TEXT(B1, "dd/mm/yyyy") & " " & TEXT(D1, "h:mmam/pm")

Excel formula for the Output column:

=CONCATENATE(TEXT(A1,"dd/mm/yyyy")," ",TEXT(C1,"h:mm AM/PM")," to ",TEXT(B1,"dd/mm/yyyy")," ",TEXT(D1,"h:mm AM/PM"))

Scenario 3

In the scenario where your data consists of two columns labeled as Start Date and End Date, without time included:

Google Sheets formula for the Output column:

=TEXT(A1, "dd/mm/yyyy") & " " & " to " & TEXT(B1, "dd/mm/yyyy")

Excel formula for the Output column:

=CONCATENATE(TEXT(A1,"dd/mm/yyyy"), " to ",TEXT(B1,"dd/mm/yyyy"))

 

 

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, except 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: