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 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 create new Tier 2 records that will live under existing Tier 1 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 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 added it to your Apricot.
Note: It is best practice to upload no more than 20,000 records at a time with the Import Tool. Whether creating new records or updating existing records in Apricot, it is expected that the Import Tool may experience issues when processing more than 20,000 records with each upload.
Prepare Your Data for Import
Before working with 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 your old system had one form to collect a certain kind of information but your Apricot system has two forms to collect that same information, you will need to do two imports - one import into each of the Apricot forms. It can save time to separate the information beforehand into two documents.
Important: When you choose to create new Tier 2 records with the Import tool, please note that the related Tier 1 records already need to be created and saved in your Apricot database.
Access the Import Tool
To access the Import tool, click the Administrator tab at the top of the page.
In the Navigation Menu, select Imports in the Record Manager section.
Under "Form List," all previous files that had been imported will be listed by the form.
To start an import of new information into the system, select "Prepare for Import" from the right hand palette.
Choose Form
Expand the black arrow under Form.
Select the form you would like to import information into.
Choose Import Type
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
2. Select Options:
Enforce all required fields will only allow you to import data that includes information for each required field as indicated on the form you have selected.
3. Select Record Action:
"Create New" will use the information in your import file to create new unique records in your Apricot database. The instructions that follow on this page will describe how to create new records with an import.
"Update Existing" import will use the information in your import file to update or write over the information that already exists in your database.
4. Each Tier 2 you create with the Import tool must be matched to an existing Tier 1 record in the system. You can choose to do this by selecting how you will reconcile the existing records, either with Record IDs or by Duplicate Check fields. Both of these choices refer to information that must be taken from the related Tier 1 records:
Record IDs: Each record in your Apricot is assigned a Record ID when it is created and saved.
Duplicate Check: Each Tier 1 record in your Apricot has at least one field set to Duplicate Check that must contain unique information. You may know the unique Duplicate Check information for the Tier 1 records you will be updating with this import - it is often a person's name or social security number, etc.
Note: Finding the accurate information, either the Record ID or the Duplicate Check information - protects the integrity of the data you will be importing.
Column Selection
Under the Column Selection header, you can select which of the fields on the Tier 2 form you would like to add information or data to.
Because we are creating new Tier 2 records and need to reconcile them to their related Tier 1 records, one section under Column Selection will include fields from the Tier 1 - either Duplicate Check fields or Record IDs, depending on the choice you made above. In this scenario we chose Duplicate Check fields. All Duplicate Check fields are indicated by a double apricot icon.
Each item on this section is a field on the Tier 2 form you selected at the top of the page. Required fields have been grayed out and cannot be deselected. You may select the remaining fields based on the amount of information you want to import.
.CSV Preview
Under the CSV Preview header, the Import tool will display a preview of the headers of 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 of this spreadsheet 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
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 option fields - have a set list of options that can be entered into the field
Some fields - like date and phone number fields - require the data to be formatted in a specific way.
.Some fields - like name and text fields - will accept a variety of kinds of data, like numeric values and special characters, etc.
When you are ready, select Download .CSV File.
Note: it can be helpful to print these instructions or to "save as" as a .pdf document (with select, compatible browsers) for reference while you are completing the rest of your import.
The downloaded .CSV File should populate on the bottom bar of your browser.
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 to add to the database.
This is the .csv template as downloaded from the Import tool.
It has been configured with two header rows describing where the data will be added once it has been imported into Apricot. Important: Do not change anything in these top two rows.
Each column in this .csv template represents a field on the form. Each row (after the two header rows) represents one record.
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.
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 where you will be able to easily find it later.
Note: Make sure the "Save as type" is CSV (Comma Delimited) only. This is not the default if you use a non-Windows operating system.
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.
Upload
Click the "Choose File" button.
Find the .csv template that contains the information you want to import.
Open.
Upload.
Import Overview and Progress
Once you have uploaded your .csv file, the Import Overview page will open.
Under Name, you can adjust the name of your import if necessary,
And see details about the file that has been uploaded into the system.
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.
You will guide the process by selecting options under the Import Actions palette. You can choose to "Run All,"
Or select each step one at a time.
Your progress will be recorded by green check marks next to each step in the import process.
Run All
If you select "Run All," the Import tool will automatically run each of the steps in the Import Progress palette (with the exception of the "Revert" step 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.
Post-processing refers to any 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.
Note: If you choose to "Run All" and your import runs into an error at any step along the way it may be unclear which part of your import needs to be edited. If you run each step one at a time you will be provided with a greater level of detail.
Assign Access
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.
Click 'Add'...
And 'Apply'
Import Actions
Instead of choosing "Run All," you may also choose to manually select each Import Progress step.
Your progress will appear under the Import Progress palette. The first step, "Uploaded" will already be completed when this page opens.
The next step will appear as a button under the Import Actions palette. To move to the next step, click the "Verify" button.
Verify
During the verify step, the Import tool checks the structure of the .csv file you uploaded to make sure it is suitable for importing.
When a file passes the verify step, you will see a green check box here.
Details about the columns will be listed here.
To move to the next step, click the Prepare button.
Verify Errors
If your file is found to contain errors during the verify step, you will see a red x in the Import Progress palette.
Details about the error will be found under the "Verified" section of the Imports page.
At 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."
Prepare
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.
If your file passes the prepare step, it will be shown with a green check mark.
Details about your rows can be found here.
You may also download a .csv file of these rows by selecting "Get CSV" or you may view the rows by selecting view.
Validate
During the validate step, the Import tool checks the quality of each of your rows of data to ensure they can be imported.
If your file passes the validate step, it will be shown with a green check mark.
Details about your rows can be found here.
Validate Errors
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 .csv file of the rows containing errors. These error rows can be fixed and uploaded as a second import.
If your file contains errors, you will see a yellow alert icon in the Import Progress palette.
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.
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.
The View button will display the error rows so you can see which pieces of data did not pass validation.
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.
Reconcile
During the reconcile step, the Import tool checks the related Tier 1 records in your database to ensure that the information you are trying to import matches an existing Tier 1 record.
When a file passes the reconcile step, you will see a green check box in the Import Progress palette.
Details about the rows will be listed here.
Reconcile Errors
If, during the reconcile step, your file is found to contain errors, you will see an alert icon in the Import Progress palette.
Details about the error will be found under the "Reconciled" section of the Imports page. In the example above, two rows do not match existing Tier 1 records. These rows will need to be corrected or the appropriate Tier 1 records will need to be created and saved in the database.
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. 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!
Under the "Imported" section, you can see details about your import.
You can also 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.
The next step in the Import Actions palette is to "Run Post Processing."
Post-processing refers to any system action that is 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. Please bear in mind that Date/Time Calculations and Default Dates will not run in Post Processing.
4. More details about available post-processing actions will be displayed under the "Post Processing" section. In the example above, a numeric value used in a date calculation needs to be set. This is considered "pending" until you click the "Run Post Processing" button under Import Actions.
If there are post-processing actions listed that you need for reports or record search, you must proceed with post-processing. If you skip post-processing, all of the actions listed will remain blank in the database. It is possible to update the records manually after the import.
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.
Date/Time Calculations and Default Dates will not run in Post Processing and must have their values imported directly.
Once post-processing is complete, the "pending" status will be changed to a green check mark.
The other post-import action 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:
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.