Upload or update entry data in bulk using our automatically generated Excel templates. With the ability to import entries, you can:
- Consolidate entries from multiple accounts or organizations into a single account
- Move entries to Cognito Forms from another form builder
- Quickly populate data for Lookup fields, such as inventory lists, prices, etc.
How to import entries
Before you start importing, please note that:
- Imported entries will count toward your organization’s entry limits.
- Once you update existing entries, the previous versions cannot be restored. You may want to export the existing data first and keep it just in case!
- Importing entries does not trigger any notifications or integrations associated with the form.
Users with Editor level permissions can import entries from an Excel spreadsheet:
Click the Actions tab at the top of the Entries page and select Import.
Download a pre-generated template Excel document for your form. The first row of the template will contain internal field names. The internal field names in the first row must match the field names on the form. If you are only importing new entry data (and have no repeating data), the entry ID column can be left blank.
Once you finish populating your document, select the Upload button or drag the file into the Import Entries dialog.
Under Manage Matching Entries, select one of two options:
- Always create a new entry for each row: All spreadsheet rows will be imported as new entries.
- Update entries when entry IDs match: Entries will be updated when the values in the entry ID column (ex: FormName_ID) correspond to existing form entries. When updating existing data, if a row for an entry does not exist in the import file, the entry will be left as is.
Select the Import button to begin importing entries. Depending on the volume of entry data, this process could take some time - feel free to leave the page.
Once the import is completed, you will receive an email notification containing a link to the uploaded file. You can also download an annotated import file with comments if any errors occurred during the import. All imported entries will appear on the Entries page with a status of Submitted and the import details will be recorded in the entry audit log.
Field types and requirements
You cannot import Signature fields or File Upload fields.
| Field type | Excel cell format | Requirement(s) |
|---|---|---|
| Address | Text/Number | Zip Code/Postal Code column must use the Number Format. |
| Choice | Text | Use only the available choice options unless you allow for Other. |
| Currency | Number | Requires the addition of 2 decimal places (e.g.100.00). |
| Date | Text | Written format must match the form’s Location (ex: US format is MM/DD/YYYY) |
| Text | Format must include @ and .com/.org/.net/etc. | |
| Number | Number | Match the number of decimals with the field type. Decimal and Percent require .00 |
| Phone | Text | US-type numbers require the format (XXX) XXX-XXXX XXXX. |
| Rating Scale | Text | Include only the text value (ex: Satisfied). Do not use the numeric rating. |
| Time | Text | Must match form Localization settings. (ex: 12:00AM for the US and 00:00 for UK) |
| Website | Text | Requires http:// |
| Yes/No | Text | Must be filled out with either Yes or No. Do not leave blank. |
Repeating section and table data
When importing repeating section and table data, the Excel file must contain a sheet with the same name as the repeating section or table. The sheet must have an entry ID column (ex: FormName_ID) and an item ID column (ex: RepeatingSectionName_ID), and the values in the item ID column must be unique.
You can import up to one nested repeating section (i.e. a repeating section within another repeating section). The sheet for the innermost repeating section must have an ID column for the repeating section itself, the form, and all parent sections.
When updating data, existing repeating section data will be left as is if a sheet does not exist for a repeating section. If a sheet does exist, the repeating section items that are not in the import file will be deleted.
Lookup field data
If your form has Lookup fields, you can import data to those fields using the value’s name (ex: “Flavored Coffees”) or the corresponding entry ID number from the source form (ex: “5” if the entry number is #5). When importing checkbox values into Lookups, the import value should be a comma-delimited list of display names (ex: First Choice, Second Choice, Third Choice). If your Lookup fields have any connected cascading dropdowns, make sure to include those values in the import file as well.
Additionally, you can use the import feature to populate data on the lookup source form, such as a product catalog. Learn more.
Choice – Checkboxes
For Choice fields set to the Checkboxes type, choice options containing commas or quotes will be contained within double quotes in the export/import. Additionally, we do not validate that the choice options you enter match the values on your form. For example, if the options in your Choice field are Red, Green, and Blue, but you enter Yellow in the import template, the Choice field will be blank in the entry.
Troubleshooting tips
- Only Excel files smaller than 1MB can be uploaded.
- Entries that exceed quantity limits will not be imported.
- Excel files that contain duplicate entry IDs or repeating data IDs will not be imported.
Common errors and solutions
| Error | Meaning | How to correct |
|---|---|---|
| …must be formatted as … or … | Yes/No value is null. | This field cannot be left blank. You must enter ‘Yes’ or ‘No’. Custom options should use ‘True’ or ‘False’. |
| …_Id is required | No ID value was included, or the cells are not number format. | Add an ID value when using the ‘Update entries when entry IDs match’ option. Must use Number Formatted Cells. |
| Sheet…must have an ID column… | The Repeating Section/Table Sheet’s first column does not include a matching number from the Form_ID column. | Check the numbers in the main and Repeating Data sheets’ first columns to ensure the numbers match correctly. |
| …must contain a sheet named… | The secondary sheet for Repeating Section/Table data is missing. | Redownload import template & copy data into all columns. Do not delete secondary sheets. |
| …does not contain an entry with… | The Repeating Section/Table Sheet ID column is empty. | Add values in the Repeating Section/Table Sheet ID column. |
| …which does not exist in sheet… | The ID column is not working correctly or formatted correctly. | Redownload the import template and map data to the right columns and sheets. |
| A field exceeded quantity limits | The imported data exceeds the quantity limit. | Change or remove the data in this column. If using a Quantity field, adjust the number accordingly. If exceeding the quantity limit is fine, remove the limit via the Build page before starting the import. |
| … contains more than one row with ID… | Duplicate number in the first column, _ID. | Check all rows of the first column and change duplicate numbers. |
| Object reference not set to an instance of an object | The first column, _ID, is not a number. | Enter a number (e.g. 1, 2, 3) in the first column. Only use integers. |
| …must be formatted as a date | Date format is not correct. | Update the value to use the correct format. For example, the standard US format is DD/MM/YYYY. |
| …must be formatted as an integer | Value in Excel is not an integer. | Must be a number without a decimal value. |
| … contains more than one column with label… | A column label has been duplicated. | One column needs to be renamed or removed. |
| …must be formatted as a decimal | The value must be a decimal. | Must be a number with decimal places. |
| Index was outside the bounds of the array | The file type is incorrect. | Try to import an xlsx file. |
| …contains no data | The file selected didn’t have any header or row data to import. | Choose another file or download the import template and populate your data. |
| Imported file failed validation checks | The ID column in the repeating section sheet has no header and no value. | Redownload the import template and map data to the right columns and sheets. Do not delete any column headers. |
| … is not a valid value for DateTime | The DateTime value is incorrect. | Update the format. The date format is based on localization settings for the form. The standard US format is DD/MM/YYYY. |
| Couldn’t convert value to type… | Lookup value in the import does not match an existing option for that lookup. | You must use only values available to choose on a lookup. Check to ensure the Lookup field value is correct on the source form. |
| …must be formatted as a time | Value does not have the correct time format. | Use either HH:MM:SS AM/PM or as 24 hour (e.g. 13:00) formats. |
| … not a legal OleAut date | The value entered does not have the correct date format. | The date format is based on localization settings for the form. The standard US format is DD/MM/YYYY. |
Partial imports
In some cases, an import may fail and only import some of the entries. To import the missing entries:
- Open the Excel file that is flagged with errors.
- Fix the identified errors.
- Re-upload only the lines/entries that contain the errors. Please note that the entries that needed to be fixed will not be placed into their original order - they will be added to the end of the entries list.
In cases of a partial import, we do not recommend deleting all your entries and re-uploading the entire file unless absolutely necessary. While the entries would import in their original order, the entry numbers wouldn’t start at 1. Additionally, imported entries are counted in your entry limits, and re-importing the same entry would result in that entry effectively counting twice toward your entry limit.