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 existing 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. Let's get started!
1. 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.
Please keep in mind that each import will need to be assigned to a Program. This means that your data should be broken down by program prior to the import process to ensure that all accesses are applied correctly.
2. Access the Import Tool
1. To access the Import tool, click the Administrator tab at the top of the page.
2. In the left hand menu, select Imports.
3. Under "Form List," all previous files that have been imported will be listed by the form.
4. To start an import of new information into the system, select Prepare for Import from the right hand palette.
3. Choose Form
1. Expand the black arrow under Form.
2. Select the form you would like to import information into.
4. Choose Import Type
1. Select import type.
- Data: A data import will add information to 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 two forms. For instructions on how to complete a linking import, please click here: Import Links.
2. Select options.
- Enforcing all required fields (Click here to see related article: Basic Field Properties) will only allow you to import data if you include 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" will use the information in your import file to update or write over the information that already exists in your database. For instructions on how to complete an update import, please click here: Import Update Tier 1 Records
- "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 any unmatched information to create new records. For instructions on how to complete an import that both updates existing records AND creates new records, please click here: Create and Update Tier 1 Records.
4. The Import tool will reconcile or match the new information with information that already exists in the database to make sure you are not recreating duplicated information. When you create new Tier 1 records, this will be done by reconciling the information in Duplicate Check fields.
5. Column Selection
1. Under the Column Selection header, you can select which of the fields on your form you would like to add information or data to. Each item on this list is a field on the form you selected at the top of the page.
2. In the example above, we selected to import both required fields and Duplicate Check fields, so any field that is marked as required or Duplicate Check on the form will be grayed out - this means you may not deselect it and you will need to provide data for these fields when you import.
3. Other fields can be deselected, either by selecting the "Unselect All" button or by unchecking individual boxes.
6. CSV Preview
Under the CSV Preview header, the Import tool will display a preview. 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.
7. 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.
1. Some fields - like name and text fields - will accept a variety of kinds of data, like numeric values or special characters, etc.
2. Some fields - like date and phone number fields - require the data to be formatted in a specific way.
3. Some fields - like option fields - have a set list of options that can be entered into the field.
4. Note: it can be helpful to print these instructions or to export them as a .PDF document for reference.
5. When you are ready, select Download CSV File.
6. 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.
8. 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 add to 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 after it has been imported into Apricot.
* Do not change anything in these top two rows.
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.
9. 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.
The 'Save as Type:" should be CSV (Comma delimited)
10. Return to Apricot
1. 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.
2. Select Imports from the left hand menu.
3. Select Upload file from the right hand palette.
1. Click the "Choose File" button.
2. Find the .CSV template into which you have copied the data you want to import.
12. 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. If you wish for the Import tool to automatically add the data, you can choose to "Run All"
5. Or, if you wish to manually control the process, 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.
Automatic Import Steps
13. Run All
1. If you select "Run All," you will be prompted to select the program or programs that these records should belong to, and then 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).
2. A section for each step in the import process will show details of that step in the Import Progress palette.
3. A message will appear telling you how many records were imported.
4. Post-processing refers to any actions that are usually performed on a record at the time it is filled out and saved. For example, this could include setting a default value where appropriate or performing a calculation in a calculation field. More details are under the heading "Post Processing" below.
5. 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.
Manual Import Steps
14. Import Actions
Instead of choosing "Run All," you may also choose to manually run each Import Progress step.
1. Your progress will appear under the Import Progress palette. The first step, "Uploaded" will already be completed when this page opens.
2. The next step will appear as a button under the Import Actions palette. To move to the next step, click the "Assign Access" button. If your import does not include the Assigned Programs column this will assign every record to this program. If your import does include the Assigned Programs column, this will be the default for any rows that do not have a proper value entered into the Assigned Programs column. See our Article on Row Level Program Assignment for more information on using this column.
1. If you use User Record Level Access you will need to select an owner for the records. The field will default to the Administrator running the import. If no User RLA is applied, the record owner will not affect access for these records.
2. Select the program or programs you would like these records assigned to...
3. Click "Add"...
4. And "Apply"
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 move to the next step, click the Prepare button.
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 "Service Recipients - Name: last."
1. 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.
2. If you attempt to continue the import by selecting the next step, Prepare,
3. 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.
4. To move on to the next step, click the Validate button.
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.
1. 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.
2. If you attempt to continue the import by selecting the next step, Validate,
3. 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.
3. To move on to the next step, click the Reconcile button.
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.
1. If your file contains errors, you will see a green alert 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. The dropdown to the left of the view button gives you different options based on how you want to approach the invalid data. You can either choose to not import the row entirely via the "Do Not Import Row" option, or alternatively you can select "Import with Blank Value Field" so that all of the valid data still imports while the invalid data fields remain blank.
6. 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 information you are trying to import does not already 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.
3. To move on to the next step, click the Import button.
1. If your file is found to contain errors during the reconcile step, you will see a green 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 matches an existing record.
1. 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.
2. Before you can import, you will get the warning message telling you how many rows will be updated, and how many will be skipped depending on the matching data the system finds.
15. 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 you need to check what was imported at some point in the future.
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, this could include 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 used in a date calculation 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 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 individually 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.
Other post-import actions include:
2. 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.
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.
When importing users be sure to use the "Password: hash" field to enter their password into. The "Password : mod_time" field is not necessary to include on your prepared CSV, as this can be run in the Post Processing portion of your import.
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.