The Apricot Import tool allows information pertaining to multiple records to be entered and saved at one data entry point, eliminating the need to manually create or update individual records. It can be used to transfer data from Excel files or from a historic database into Apricot.
The Import tool can be used to create new records or to update records that already exist in your Apricot database. This article explains how to update Tier 2 records. For instructions on how to create new Tier 2 records, click here: Import Create Tier 2 Records.
The Import tool will guide you through a preparation page where you will decide which form to import data into. Then you will download a pre-configured template in .csv format where you will paste or enter the information to be imported. Once this has been uploaded to the Import tool, you will be guided through the process of validating and verifying the data and finally adding it to your Apricot.
Before Updating Records
Before records can be updated with the Data Import tool, make sure all the records you want to update have been created and saved in Apricot. Using data archives, you can download existing data from a form into an excel spreadsheet to see the current data and determine which records need to be updated.
Prepare Your Data for Import
Prior to using the Import tool, prepare the data you plan to import into Apricot. Your data should be in a format - like an Excel spreadsheet - that will allow you to select columns or rows of data to paste into the Import tool template.
You will have to do a separate import for each form in Apricot that will be accepting imported information. If you are importing data from an old system or from a third party database that collects information in multiple Apricot forms, you will need to do an import for each of the forms. It can save time to separate the information beforehand into multiple documents.
Each import will also be assigned to a program, so to prepare your data you will also have to make sure that each Excel spreadsheet is specific to a particular program in your site. For more information on programs, please see our article Limiting or Granting User Access.
In the example above, we created an Apricot report to find Record IDs for each of the records we want to update. Then we exported the report, leaving us with an Excel spreadsheet showing information the Import tool can use to find each record to be updated.
For more information on creating a Record ID report to use with the Import tool, please check out our article: Building a Record ID Based Report for Importing Data.
Access the Import Tool
To access the Import tool, click the Administrator tab at the top of the page.
In the left hand menu, select Imports.
Under "Form List," all previous files that had been imported will be listed by the form.
This list will only contain information about imports that have been completed with the new Import tool. For instructions on how to access the information imported with the legacy import tool (available prior to September 2015), see "Finding Old Imports."
4. To start an import of new information into the system, select Prepare for Import from the right hand palette.
Expand the black arrow under Form.
2. Select the form you would like to import information into.
Choose Import Type
1. Select import type.
- Data: A data import will add information to the fields on the selected form. The instructions that follow on this page will describe how to import data.
Linking: A linking import will add connections between the selected form and a second form. For instructions on how to complete a linking import, please see the article "Import Links
2. Select options.
- Enforce All Required Fields will allow you to update records only if you provide information for each required field.
3. Select record action.
"Create New" will use the information in your import file to create new unique records in your Apricot database. For instructions on how to create new records with an import, please see the article Import Create Tier 2 Records.
- "Update Existing" import will use the information in your import file to update or write over the information that already exists in your database. The instructions that follow on this page will describe how to update existing records with an import.
"Both" will first apply the information in your import file to existing records to see if they need to be updated; then it will use the remaining information to create new records. For instructions on how to complete an import that both updates existing records AND creates new records, see article Update Tier 1 Records.
4. Reconcile Using Record IDs: The Import tool will need to match the information you enter with information that already exists in the database to correctly identify which records should be updated. When you update a Tier 2 record, you must reconcile using Record IDs. Each record is assigned a unique record ID at the time it is created. Record IDs for the records you would like to connect can be found using a report. More information on how to build a Record ID report can be found in the article: Building a Record ID-Based Report for Importing Data.
Note: Duplicate Check cannot be used to reconcile Tier 2 records because Tier 2 Duplicate Check only runs within a particular Document Folder. If two Tier 2 records in different Document Folders have the same Duplicate Check fields, the import tool wouldn't know which record to update.
1. Under the Column Selection header, select only the fields on your form you would like to update. Each item on this list is a field on the form you selected at the top of the page.
2. Because we will reconcile our updates using Record IDs, we can de-select any other fields on the form. Fields that have been grayed out will be required for the import to be completed successfully.
* Make sure you select the fields you want to update.
3. Other fields can be deselected, either by selecting the "Unselect All" button or by unchecking individual boxes.
In the CSV Preview header, the Import tool will display a preview of the column headers in the .csv import file. The Import tool will prepare a spreadsheet in .csv format to hold the data you will be importing. The first two rows will be dedicated to "Column ID" and "Column Name" as shown in the example above. These two rows contain information that will ensure the Import tool places your data in the right place in your Apricot.
This is what the column headers will look like in the .csv template. These should match up with the fields you have selected above.
Import Instructions will display details for the kinds of data that will be accepted for import into each individual field on the form. Some fields - like name and text fields - will accept a variety of kinds of data, like numeric values or special characters, etc.
1. Some fields - like option fields - have a set list of options that can be entered into the field. These values must match an option exactly to be imported successfully.
2. Note: it can be helpful to print these instructions or to export them as a .pdf document for reference.
3. When you are ready, select Download .CSV File.
4. This is the template where you will enter information to be imported into the Apricot database. It will be downloaded according to your Internet browser protocols.
Open CSV and Paste In Data
Begin by opening both the .csv template as downloaded from the Import tool as well as your spreadsheet containing the information you would like updated in the database.
1. This is the .csv template as downloaded from the Import tool.
2. It has been configured with two header rows describing where the data will be added when it has been imported into Apricot. Do not change anything in these top two rows or the first column.
3. Each column in this .csv template represents a field on a form. Each row (after the two header rows) represents one record.
4. Open the file that contains the data you would like to import. Select information from the old file and paste it into the appropriate spot in the new .csv template, including the Record ID. The Record ID will "match" the new information and update it to the correct record.
Save the .CSV Template
Once the data has been added to the .csv template, save it to your desktop or to another location on your computer with an identifiable name where you will be able to easily find it later.
Return to Apricot
Once you have finished adding data to the .csv template and have saved that template to your computer, return to Apricot and select the Administrator tab at the top of the page.
Select Imports from the left hand menu.
Select Upload file from the right hand palette.
Click the "Choose File" button.
Find the .csv template that contains the information you want to import
Import Overview and Progress
Once you have uploaded your .csv file, the Import Overview page will open.
1. Under Name, you can adjust the name of your import if necessary...
2. ...and see details about the file that has been uploaded into the system.
3. Before the information can be added to the Apricot database, it will need to go through each of the steps in the Import Progress palette.
4. You will guide the process by selecting options under the Import Actions palette. You can choose to "Run All" ...
5. ...or select each step one at a time.
6. Your progress will be recorded by green check marks next to each step in the import process.
If you select "Run All," after selecting which program(s) the records should be assigned to, the Import tool will automatically run each of the steps in the Import Progress palette (with the exception of the "Revert" which should only be used if you want to undo the changes an import has made in your Apricot).
Section headers will show details for each step in the Import Progress palette. If there are errors, see instructions in the below steps, otherwise, the below Import Progress steps can be skipped.
A message will appear telling you how many records were imported.
Post-processing refers to system actions that are automatically performed on a record at the time it is filled out and saved. For example, setting a default value where appropriate or performing a calculation in a calculation field. More details are under the heading "Post Processing" below.
Revert will "undo" an import that *has been fully imported*. If you have completed an Import in error and need to either remove it completely or re-do it, click the Revert button.
Instead of choosing "Run All," you may choose to manually select each Import Progress step.
If you utilize User Record Level Access it is important to assign the imported records to a specific owner. If not, you can ignore this field. If you are unsure whether you use User Record Level Access, please read our knowledge article User Record Level Access (User RLA)
Select the program or programs that the records should be assigned to, you can also establish program assignments at the row level, see our article Row Level Program Assignment for more information. If you do not include the Assigned Programs column in your import, all records will be assigned to the program that you choose here. It will also serve as the default when a particular row might not include a program or set of programs in the Assigned Programs column.
Note: The Assign Access step will only add programs to records; existing Assigned Programs will be unaffected.
During the verify step, the Import tool checks the structure of the .csv file you uploaded to make sure it is suitable for importing.
1. When a file passes the verify step, you will see a green check box here.
2. Details about the columns will be listed here.
3. To continue to the next step, click Prepare.
1. If your file is found to contain errors during the verify step, you will see a red x in the Import Progress palette.
2. Details about the error will be found under the "Verified" section of the Imports page. In the example above, there was a problem with the column related to "Program Intake - Record ID."
3. Towards the bottom of the page, an error message will give you instructions for fixing the error or will tell you "This file cannot be used for import." To move beyond this step with a successful import, a new .csv file with the appropriate columns and headers will have to be uploaded and pass the verify step.
If you attempt to continue the import by selecting the next step, Prepare, you will get another error message: "This import has failed and cannot continue."
During the prepare step, the Import tool loads your data and tells you how many rows are going to be imported. Because two of the rows on the spreadsheet are devoted to the header columns, this number should be two less than the total number of rows on your spreadsheet.
1. If your file passes the Prepare step, it will be shown with a green check mark.
2. Details about your rows can be found here.
3. You may also download a new .csv file by selecting "Get CSV" or you may view the rows by selecting view.
1. If your file is found to contain errors during the prepare step, you will see a red x in the Import Progress palette.
2. Details about the error will be found under the "Prepared" section of the Imports page. In the example above, the .csv file contained the proper header rows but no rows of data.
3. At the bottom of the page, an error message will give you instructions for fixing the error; in this case: "Please verify that your file contains data." To move beyond this step with a successful import, a new .csv file with at least one row of data will have to be uploaded and pass the prepare step.
If you attempt to continue the import by selecting the next step, Validate, you will get another error message: "This import has failed and cannot continue."
During the validate step, the Import tool checks the quality of each of your rows of data to ensure they can be imported.
1. If your file passes the validate step, it will be shown with a green check mark.
2. Details about your rows can be found here.
Unlike other Import progress steps, if errors are found during the validate step, you do not have to stop your import and re-start after the files have been corrected. Instead, you can import only the records that can be validated with good data and download a new .csv file of the rows containing errors. These error rows can be fixed and uploaded as a second import.
1. If your file contains errors, you will see a green publish icon in the Import Progress palette.
2. Details about the errors will be found under the "Validated" section of the Imports page. In the example above, the .csv file contains 1 row of invalid data.
3. If you would like to download a .csv file containing only the error rows, click the "Get CSV For All Errors" button. This would be useful if you have a large file to import and would like to be able to fix the errors and upload a separate file for a separate import.
4. The View button will display the error rows so you can see which pieces of data did not pass validation.
5. If you would like to move forward with your import and fix the errors later, you may continue with the import process by clicking Reconcile.
During the reconcile step, the Import tool checks the other records in your database to ensure that the records you are updating exist in the database.
1. When a file passes the reconcile step, you will see a green check box here.
2. Details about the rows will be listed here.
1. If your file is found to contain errors during the reconcile step, you will see a yellow alert icon in the Import Progress palette.
2. Details about the error will be found under the "Reconciled" section of the Imports page. In the example above, one row does not match an existing record.
You may choose to go ahead with your import even if you have reconcile errors. The error rows will be skipped if you select to continue.
Before you can import, you will get the warning message "Some rows contain errors." These rows will not be imported. As with validate errors, you should download the .csv for all errors, located under the "Validated" section of the Import page, fix the error rows, and upload them as a separate import.
Import and Post Processing
You made it! You have imported rows!
1. A message will tell you how many rows you imported.
2. Under the "Imported" section, you can see details...
3. and download a .csv file of the rows that were imported. This can be useful if in the future you need to check what was imported.
4. The next step in the Import Actions palette is "Post Processing."
- Post-processing refers to any action that is usually performed on a record at the time it is filled out and saved. For example, setting a default value where appropriate or performing a calculation in a calculation field. Please bear in mind that Date/Time Calculations and Default Dates will not run in Post Processing.
5. More details about available post-processing actions will be displayed under the "Post Processing" section. In the example above, a numeric value needs to be set. This is considered "pending" until you click the Post Processing button under Import Actions.
If there are post-processing actions listed that you need to show up in reports or in record search, you must proceed with post-processing. If you skip post-processing, any of the actions listed will remain blank in the database until each imported record is opened and re-saved.
Post Processing will run calculations in the order they appear on the form. If a calculation field uses default values or other calculations then it must be placed after those fields for post processing to work correctly.
1. Once post-processing is complete, the "pending" status will be changed to a green check mark.
2. The other post-import action available is Revert. This will "undo" an import that *has been fully imported*. If you have completed an Import in error and need to either remove it completely or re-do it, click the Revert button.
Note: Clicking Cancel Import after clicking Run All will not cancel the import. The only way to cancel an import after clicking Run All is to revert the import after it has run.
Other Important Information
Dynamic Dropdown values can not be import updated if the fields already contain a link. These types of values can only be adjusted manually, at the record level.
Dynamic Dropdown fields which were previously empty can be imported onto, but require two imports. Both a data import and link import will be necessary
Dynamic Checkbox values can be imported whether or not the fields already contain a value, but require two imports. Both a data import and link import will be necessary. Options selected prior to the import will remain selected after the import.
Reverting an import will only revert records that have not yet been modified. The import tool will provide you a CSV of the records that were not reverted.
When importing data containing special characters (such as á, Á, é, É, í, ó, Ó, ú, Ú ñ, Ñ), do not use Microsoft Excel. Use other spreadsheet/text editors such as Google Sheets or LibreOffice Calc. Certain versions of Microsoft Excel do not handle character encodings correctly which can result in these characters displaying incorrectly in Apricot when imported.
We recommend limiting CSV files used for imports to no more than 20,000 rows for maximum efficiency and to reduce the potential for your import to time out. With imports that contain numerous columns, a lower row count may be necessary to ensure optimal performance.