Below are some of the common issues you may encounter when Bulk Importing Database Records, or Bulk Updating Database Records.
Click quick links below to jump to that section.
- Supported documents for import
- Drop-down selections mapping
- Character limits for text fields
- Numerical fields in imports
- Invalid date form issues
- Records with the same name
- Fields / columns with the same name
- Use of commas in multi-select field values
- Large spreadsheets
- Formatting
- Unsupported for import
Supported documents for import
Ensure the document using for import is one of the below:
- Excel
- CSV
Drop-down selections mapping
Example: You have a Region field in your Database with a drop-down selection of Australian States and Territories: VIC, NSW, TAS, ACT, etc.
In your spreadsheet however you have this information filled in for every record however you have listed them 'Victoria', 'New South Wales', 'Tasmania', etc.
This info will not map to the database because it does not exactly match the pre-set drop-down options for that field.
Ensure that when you have drop-down selections that the info you import matches these options exactly to avoid this error.
Character limits for text fields
Example: You have free-text State field in your database and have set the character limit for '3' for this field, such as 'VIC'.
If importing a record which has spelled out the full State, such as 'Victoria', this will however result in an error as it is over the character limit.
To resolve, relax the character limits against that field and ensure that these limits are not set up unless you really require them.
Numerical fields in imports
If your database has specific fax and/or phone fields, note that these will only allow numbers to be entered against them.
Example: If you are importing records with any letters (such as "Ph: 03 8618-7800") this will result in an error.
To fix this change the fax/phone field type to a general text field to allow both numbers and letters, and retry your import.
Invalid date format issues
For Date fields, please make sure that they are in the default Date format set on your platform (you may need to check with your Administrator what the default is):
- DD-MM-YYYY
- YYYY-MM-DD
- MM/DD/YYYY
Records with the same name
If you have records with the same main name info keep in mind that when importing in bulk the system does not know which record to map the other record info against.
To resolve this, some editing might need to be made to 'Name' information of your records to make sure they are different, then retry your import attempt. Alternatively, switch to a Title field that will be unique for each record in the Form Builder.
When re-importing existing records, export the data first, and then map to the UUID column, which is always unique for each record.
Fields / columns with the same name
Likewise if you have multiple fields in the database form with the same name, then the import is likely to fail or partially fail. It is always best for all fields to have unique names for importing.
If you only need to import once when first using a database, then it is suggested to temporarily rename any fields with the same name with a number or character appended to the end, such as 'FieldName1', 'FieldName2', etc. Then after import has been successful, these can be edited to revert to original names.
If however ongoing importing will be likely as party of your processes, then it is advised to ensure all fields have unique names.
This is also the reason why duplicable sections are not supported for import - since fields within will by default share the same names with fields in other duplicable sections.
Use of commas in multi-select field values
When importing into a select field that allows for multiple values (such as a 'Multiple Select Dropdown' or a 'Select - Checkbox / Checkbox Group' field) it is advised to ensue that the values for these fields do not include any commas (",").
This is because the import processing will look to a comma a delineation point for splitting up multiple values. This is useful when wanting to import multiple values at once (where no commas exist in those values) such as this example field:
Having a spreadsheet field formatted using commas to separate these values allows for more than one to be added at once (assuming the field has been configured to allow for multi values):
Because the system will try to parse these separate values from the inclusion of a comma, it can cause issues where commas may exist in the values themselves. For example it will think the value "This is some text before a comma, this is text after a comma" are two values it is trying to match against, not one.
It is therefore recommended not to include commas in values for these fields if importing will likely occur for this database, or to use alternative characters to break up a sentence/text instead (see last example below):
You will need to ensure that the values in the spreadsheet match the values in the field exactly for the import to be successful.
Large spreadsheets
While there is no defined limit to the number of records that can be imported, if the spreadsheet has a large number of columns, rows, or a combination of those, then try splitting the spreadsheet up into two or three to import in batches (either breaking up by rows, or if by columns you can split the spreadsheet up by columns and import some at first as new records, and then try updating those records with the rest of the data as a secondary step.
Formatting
Occasionally a spreadsheet might have formatting that cannot be supported for import. It is best to use simple, 'clean' spreadsheets.
Unsupported for import
- Import of records is not supported for staged databases
- Calculation fields cannot be updated via import (these will be automatically updated based on other field values)
- Upload fields cannot be supported in import
- Sequence ID fields are excluded from import as these are system generated
- Fields in duplicable sections are not supported for import
Related:
Comments
0 comments
Please sign in to leave a comment.