The iModules import utilities are intended to be used for incremental updates in order to keep your iModules Encompass database in sync with your offline database. Full data base refreshes (all records/all fields) are NOT permitted without coordination with your project manager or customer success manager.
Import Member Data
Importing constituent data allows you to sync your offline database with your iModules system. The Import Member Data tool pulls information from a spreadsheet (.csv) file exported from your offline data store and adds it to your iMODULES online data source.
The import tool is designed to import a changes only file (incremental changes file) since the last import into Encompass. The Import tool in not intended to upload all records/all fields during each data import. The file should only contain records that have had a change/update since the last time each record was imported into Encompass. If your offline database has 100,000 constituents but only 10,000 had a change in the last week (or any other import frequency you use); then we expect the import file to only contain 10,000 records.
The Import Queue shows you all the data files scheduled for import into your iModules online data store. These files stay in the queue until 35 minutes after the hour before the import occurs. Any imports in the queue at 35 minutes after the hour will be sent, so the window of time to make changes is between the time you put the import into the queue and 35 minutes after the hour. Click a file's name to double-check the data. If you identify a reason to abort an import, click the Cancel button and start again. Once data has been imported, the action cannot be undone.
- You must use a text or CSV file for the import. The file must be comma delimited. Quotes will work for a text qualifier. There is a size limit of 20MB (20480KB). If your file is larger, it should be split into multiple files and include the header row.Full data base refreshes (all records/all fields) are NOT permitted without coordination with your project manager or account manager.
- Importing Member Data via the Manual Import Tool
- Importing Member Data via Automated SFTP
- Delete an Import from Import Queue or Import History
- Import History
- Importing Data into a Membership Campaign
- Common Errors
- Best Practices for Importing Data
Importing Member Data via the Manual Import Tool
There are two ways to navigate to begin importing:
- From the Data menu, choose Import Member Data. Use this option to import data into your profile form.
- Click for the Campaign, Event, etc. that you want to import into. Use this option to import data into a specific Campaign or Event.
1. If you would like to review the headers that are used for your community, select Review iModules Schema (only available if you selected Import Member Data above).
2. To begin the process to import data, select Set up New Import. (or Click for the Campaign, Event, etc. that you want to import into).
3. A Disclaimer Statement will be displayed on the next page. It is important to read this statement and fully understand it. Once the statement has been read, click Yes.
4. A list of the fields for your import will be shown on the top part of the screen.
5. Click Browse to locate the file you want to import and select it.
NOTE: As a Best Practice, enter a description even though it is not required. The description will be shown in the Import History. It is also helpful to add the initials of the person performing the import, especially if more than one person is importing files.
6. Data Rules: Set the data rules for your import.
- Forced Length: This box may be filled in if your primary key will need to be a set length. For example, if the Constituent ID is 8 characters long, the length would be set to 8.
- Padding Character: This box may be filled in with the leading character that may have been removed by Excel. For example, the leading character for the Constituent ID may be a 0. The padding character is added to the left side. If this is unnecessary, just leave this area blank.
- Save History Records: This box is checked by default. (Saving the historical records is NOT recommended if you are doing a data refresh prior to your site going live.)
- Don't Overwrite Any iModules Fields Updated Between [startdate] and [enddate]: This option is useful if your import file was prepared several days earlier, but your members may have updated their information online after that date, and you don’t want that new data overwritten. The dates that are entered will be recognized at midnight.
- Write All Data in File: This option is used when you are importing all new data into the community. If you choose this option, everything in a record will be overwritten. For example, you would use this when you want to add a new class/group of constituents to your community or you have created a new field that you would like to populate.
The data marker allows you to select certain pieces of data to be ignored during the import process.
- Blank Value: This is the most commonly used option. With this option, the online value entered by the constituent is kept if the value in the import file is blank. It will ignore cells where there is nothing displayed.
- Null Value: This option works like Blank Value, but the difference is what the Import Tool looks for in the data file. To ignore “NULL” values in your file then you would select Null as your data marker. (This happen when the offline database populates blank fields with the word “NULL”.) The word “NULL” will be ignored.
- Custom: This option works like Blank Value, but the difference is what the Import Tool looks for in the data file. With Custom, you may enter the value that you want ignored. Use the Custom option to enter specific symbols to ignore during the import process. For example, if your data file contained an asterisk in any field that did not contain a value.
- Write All Data in File: This is the default. This option will remove the online values and use the information in the import including any “blank” values. The Write All Data in File option will import all the values exactly as they are in the file being imported.
7. Click Next once your choices have been made.
8. A confirmation screen will appear showing the choices you made. Click Next.
9. Preview your report.
- Change the header name in your file to match the Export Header Name in the iModules system.
- Or, change the Export Header Name in the iModules system to match the header name in your import file.
Any ignored/unrecognized columns will be shown in blue. The data in these columns will NOT be imported.
NOTE: When the system recognizes a Constituent ID (primary key), it will update that member record. If it does not, it will create a new member record.
When importing into a checkbox field, it will require a 1 for Checked (True/Yes) or a 0 for Unchecked (False/No).
10. Click Next.
11. You will receive a success message stating that your import was processed successfully.
The Import back-end process is set to run at 35 minutes after the hour. Should you find errors in the submitted file, you have the period of time from when you submitted the request until the start of the next Import process to remove the file from the queue in the Import Manager.
12. You will also receive a success email when the import is completed. (This is sent to the email established in your profile form). Please be aware that large files may take several minutes to process. The tool is also used by multiple clients, so it is possible that multiple files are being imported at the same queue time.
NOTE : Email Opt-Outs and Non-member Merge Files will not trigger an email upon upload completion.
NOTE : Click here for more information on Importing Memberships. NOTE: When data is exported from a large text area field, the export tool will only export the first 1000 characters. The data will remain in the database, the characters are only truncated on the export. Contact Customer Support to request an extension the maximum number of characters to 4000.
Importing Member Data via Automated SFTP
Automated secure FTP (SFTP using SSH2) is a system that mimics the manual import tool. A .CSV text file is uploaded to the SFTP folder. The file is automatically picked up by the Import Member Data Tool and imported into the community as a regular import with the administrator receiving the standard import tool email notification that it has been imported.Automated SFTP enables the client to build an automated process from their back end data system to generate an import file and send to the SFTP folder for importing into their online community.What is needed to use SFTP?
- An understanding of the Encompass Manual Import tool
- An SFTP account - contact your Customer Success Manager to get the account set up. A unique directory on iModules secure server will be provided
- An XML configuration file must be set up within the configuration folder – Your Account Manager will provide initial configuration information
- Secure FTP client software e.g. Filezilla®
- You can supply your own RSA password – preferred RSA key is id_rsa.pub
ANY files put into this directory will be picked by this system twice daily at 5:30 AM and 7:30 AM Central (USA).
- You must use a text or CSV file for the import.
- The file must be comma delimited; Quotes will work for a text qualifier.
- Incremental changes only. Full data base refreshes (all records/all fields) are NOT permitted without coordination with your project manager or account manager.
Data Rules: XML configuration file
An XML configuration file will be set up within the configuration folder once the SFTP account is created.There are 3 parameters within the XML file, all are required.
- This is how many days previous need to be excluded. Integers only.
- Note, this app runs past midnight, so if you upload a file at 3 PM on 1/15 and you want to exclude 1/13 through when the file is imported this needs to be set to "3".
- If you want to write all data "NA" is the proper option
2. dataMarkerThere are a few options here:
IMPORTANT: The last option is custom, if you do not include one of the three above in its exact syntax whatever is entered will be considered a custom data marker.
3. memberIdThis refers to the member that is responsible for the import and must be populated. This member will get emailed the standard import tool results. Access the SFTP folder and drop the import file in the root directory. The data file will be removed after being queued for import.The Administrator will receive an email notification after the Import file has been processed. The email notification will contain an import summary log. Below is an example of the configuration file:
<!--Whether you want to log all the changes made during imports or not.-->
<!--Put true if you want to log all changes-->
<!--Put false if you DO NOT want to log all changes-->
<!--This is how many days previous to today need to be excluded.-->
<!--Put in NA if you want it to write all-->
<!--Note, this app runs past midnight, so if you upload a file at 3 PM on 1/15 and you want to exclude 1/13 through when the file is imported this needs to be set to three days.-->
<!--The options here are as below, WriteAll is going to be the most common-->
<!--IMPORTANT - The last option is custom, if you do not include one of the three above in its exact syntax whatever is entered will be considered custom-->
<!--This is what member is responsible for the import, this must be populated with something. This member will get emailed the standard import tool notifications.-->
Delete an Import from Import Queue or Import History
1. Check Delete next to the file.
2. Click Delete at the bottom of the page.
3. A confirmation box will appear. Click OK to remove the import.
The Import History lists the files that have been imported and their corresponding logs. Import files will remain in this list for 90 days unless you delete them; however, the list will show the full history of imports to the site. Click on any of the column headers to sort the import file information.
To review the contents of a logged import, click the imported file's name.
Description: The File Description is shown next. If no description was entered when the import was done, this area will contain no information.
File: To view the file, click on the link provided in this column.
- Error Log:This link provides a very detailed report on the import including Ignored Columns, Error Detail Report and the Error Row Report.
- Ignored Columns: Displays any columns that were ignored during the import process.
- Error Detail Report: Gives you a brief explanation of the errors that took place during the import process. You will be provided with the Constituent ID, row number & column number for the record that contained the error.
- Error Row Report: File that contains any records that encountered errors during the initial import process.
- Once you have made the necessary adjustments to this file you will want to save this to your computer and then go through the import process again with this new file. This will ensure that any data in the error rows gets imported into the community as well.
- Success Log: The Success log shows the amount of New Rows, Changed Rows, Unchanged Rows, and Failed Rows in the import. New rows will be counted as changed rows as well.
- Below this is a tool to show any updates that were made with this import. You may enter a Constituent ID to see any fields that were updated for a certain member. You may also choose a field from the Data Column Heading dropdown list to show any members that had an update to that particular field in the import. Click Generate Report to get a report.
Dates to Exclude: If you have a two way process set up between your offline database and iModules where updated data is going in both directions then you need to use the Dates to Exclude. The first date in the range should be the last day you received an export from iModules and the ending date will always be the day after tomorrow. By selecting a date range the import tool will protect and not make changes to any updates made online during the time period set forth by the client.
Importing Data into a Membership Campaign
You can import into a Membership campaign by using the following fields which will put values into the areas on the Admin Only – Membership Tab on the Profile Form:
- [Membership campaign name] - Membership Level
- [Membership campaign name] - Purchase Date
- [Membership campaign name] - Expiration Date
The Expiration Date column is a required column, but for Lifetime memberships the value in the column should be left blank.
The system stores the Expiration Date as mm/dd/yyyy X:XX where this is the Date and Time the membership was originally purchased online. When you import memberships, the Expiration Date column is populated with mm/dd/yyyy and the system inserts 0:00 for the time. It is possible to get an error message. For example, you could break auto-renewals and scheduled payments for a membership because the system thinks you are shortening the term of the membership (via the Expiration Date). You might be importing the Expiration Date as 03/15/2013 0:00 and the system has 3/15/2013 12:00, and because the time is earlier, it sees this as shortening the term even though you really are not. Shortening memberships manually will also break auto-renewals and scheduled payments (but you will not see an error message).
- [Membership campaign name] - Status - Valid statuses for Lifetime memberships are current and cancelled; lapsed cannot be imported for lifetime memberships.
On the Membership Transaction History on a member's profile, imported memberships will display in the membership transaction list and will show the details for the membership. The payment history for imported memberships will not show the amount and will not include credit card information.
Automated SFTP Membership Imports to Sealed Sub-communities
Membership data can be imported via SFTP to sealed sub communities by specifying the Group ID in the file name. To specify the file name, '_sealedid#' needs to be added to the end of the file name. For example, if you have a file with membership data called MembershipImport that you want to import into GID 230 the file should be named MembershipImport_sealedid230.csv.
- There needs to be a separate file for each sub-community.
- The rest of the import process is the same, only the file name needs to be updated to include the Group ID.
Only the following date formats are accepted: mm/dd/yyyy or mm-dd-yyyy.
Exceeding the Character Limit
When your database is built, we create the character limit of each field according to the information that we are given. So, for example, if you try importing “Male” and “Female” into a Gender field that was built to hold 1 character ( “M” and “F”), you will receive errors for exceeding the character limit.
Any records with errors will be skipped and compiled into the Error Row Report. However, if the import process finds a large amount of errors on a recurring basis throughout the file, the import tool will timeout and stop the import process. The error report will display any of these errors and should be checked after each import.
Empty Columns and Rows
If using Excel, watch out for empty columns and rows. When values have been previously stored in a column/row and then cleared out, Excel will store this as an empty column/row. Be sure to highlight any of these columns/rows, right click, and then delete. Empty columns will cause an error on the import tool - “There was an error reading and loading fields from the import file”. These empty columns can be viewed in a text editor. You can also view and delete empty columns in CSVed (http://www.snapfiles.com/get/csvdb.html).
A member with Constituent ID “001234” will not be matched to a member with the Constituent ID “1234”. Please remember to always include your leading zeros (or other padding character) if this is what you normally use. It is very important to keep the format of your Constituent ID consistent, as there is the potential to create duplicate member records for the same member.
If your string is wrapped in double quotes, you'll need to escape additional double quotes contained within. For example, you want to write John "Jack" Smith to the database and your entire string is wrapped in quotes. There are two ways to get that with the new parser:
|Value in CSV||New Parser||Old Logic||Change in behavior?|
|"John ""Jack"" Smith"||John "Jack" Smith||John ""Jack"" Smith||Changed|
|John "Jack" Smith||John "Jack" Smith||John "Jack" Smith||Unchanged|
Best Practices for Importing Data
Match Your Dropdown Values
Remember to match the values that are in your dropdown lists, multi-select boxes, and radio button fields to your import file data so that no options are missed. You also have the ability to create custom database values that differ from what is actually displayed to the user. If you add a new value to a field that is a dropdown list in your community, always remember to add the custom database value to the list. These dropdown list options are easily accessible in the form tools.
The Round Trip Update
Get in the habit of using the iModules updated fields export to update your offline database and then immediately updating the iModules database with the latest data from the offline database. This round trip update keeps your data fresh and is the best way to sync the two databases. The iModules updated fields export can be scheduled for various frequencies (daily, weekly, etc.), and an email is then sent prompting you to download the export file. This will help you maintain a regularly scheduled update process.
CSVed is a great freeware program for .csv file viewing and basic edits. It helps avoid any unwanted formatting, stripping of leading zeros, and empty columns.
You can set this as your default .csv viewer within the program. In the menu bar, click on Tools, choose Options, under System select Associate Extension, click the Associate CSVed with Extension button, and finally click OK. This will automatically open your .csv files in this recommended program.
Please contact Customer Support if you have any questions.