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 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.

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.

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, please see our Program article.

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.

    4. To start an import of new information into the system, select Prepare for Import            from the right hand palette.

2. Select the form you would like to import information into.

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.

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.

- "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.

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.

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.

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.

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.

Instead of choosing "Run All," you may choose to manually select each Import Progress step.

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.

Verify Errors

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.

Prepare Errors

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.

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 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.

Reconcile Errors

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.

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.

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.