Importing Special Data Types

What You'll Learn

This guide explains how to prepare and import complex data types that require special handling. You'll discover how to format dates and times correctly, establish connections between tables during import, handle multi-select fields, and import images effectively.

Date and Time Formatting

Importing dates and times can be challenging due to the variety of possible formats. For best results, follow these guidelines:

Basic Format Requirements

To import into a date/time field with calendar options enabled, format your data as follows:

For events with start and end times:

Format: mm/dd/yyyy 00:00am to mm/dd/yyyy 00:00pm
Example: 04/11/2024 9:00am to 04/11/2024 5:00pm

For date ranges without times:

Format: mm/dd/yyyy to mm/dd/yyyy
Example: 04/11/2024 to 04/12/2024

Formatting with Spreadsheet Formulas

If your data is split across multiple columns, use formulas to combine them properly before importing:

Scenario 1: Single Date with Start/End Times

For data in three columns (Date, Start Time, End Time):

Google Sheets formula:

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

Excel formula:

=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: Different Start/End Dates with Times

For data in four columns (Start Date, End Date, Start Time, End Time):

Google Sheets formula:

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

Excel formula:

=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: Date Ranges Without Times

For data in two columns (Start Date, End Date):

Google Sheets formula:

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

Excel formula:

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

Important: When importing time-only data, you must include a date - importing "12:30 PM" alone will fail, but "06/30/2024 12:30 PM" will work. You can select "Ignore Date" during import mapping.

Importing Connections

You can establish connections between records by using the import feature. When importing records into an existing table, you have the flexibility to select the field from that table that corresponds to the column in your file.

Similarly, you have the option to match a column in your file with a field in a connecting table. Knack will utilize this match to locate a record to establish a connection with.

When importing data into a connection field, Knack provides you with the flexibility to select the column in your file that corresponds to the connection field.

One to Many Connection Import

When importing records into a table with connection fields:

You can select which field from your file should match with fields in the connected table
Knack uses this match to find the correct record to connect to

For example, when importing Contact records:

You might match the "Phone" column in your file with the Phone field in your Clients table
For each imported Contact, Knack will search for a Client with a matching phone number
If found, it creates the connection between the Contact and Client records

Tip: Choose unique fields (like email addresses or ID numbers) for matching to ensure accurate connections.

Many-to-Many Connections

You can import data into many-to-many connection fields, allowing a single record to connect to multiple records in another table.

Prerequisites

Before importing:

Create the table and many-to-many connection field first
For multiple choice fields, ensure the layout is set to "Dropdown - Multiple Selections"

Data Formatting

For many-to-many connections, format your data either:

Comma-separated (in a single cell):

First child,second child,third child

Row-separated (in a single cell):

First child
Second child
Third child

Important formatting rules:

No spaces before or after commas
All values must be in a single cell
Values must exactly match the display field in the connected table

📘
If you're importing and setting connection values to multi-part fields, such as Person & Address fields, you'll get inconsistent results, or the values won't be set at all. We suggest you use other unique fields, such as email address or an ID field, as your display field in the connected table and map to that value in your import.
This can even be done temporarily until after you have completed your import and the records have been indexed.

Importing Images

There are two ways to import images into Knack:

Option 1: Upload Images

To batch import images:

Ensure the image field is set to "Upload image files" (not URL)
Your import file should contain either:
- <img> tags referencing the images
- URLs to the images' current location
During import, images will be automatically uploaded to Knack's servers

Option 2: Use Image URLs

To import image URLs:

Set your image field to "Load images from an external URL"
Import the complete URLs that point to publicly accessible images

Note: When importing from Dropbox URLs, ensure they are specific image URLs that are publicly accessible.

Rich Text Content

When importing data into rich text fields:

Use HTML formatting for styling (e.g., <b>Bold text</b>)
Use <br /> for line breaks rather than relying on Enter/Return characters
Ensure HTML tags are properly closed

Knack automatically detects valid HTML during import and will predict these columns as rich text fields.