The Power of Claritysoft’s Data Import Tool
The Claritysoft import tool can import data into your database in batches, drastically reducing the amount of time required to create new data or modify existing data in your database. While the data importer is a powerful tool with built-in business logic, it should be approached with great discipline. Use these tips, best practices, and recommendations to get the most from the import tool and streamline data management in your Claritysoft database.
1. KNOW WHAT IS REQUIRED
The import utility can only import records to one module at a time. The seven modules that allow you to import data are: Leads, Accounts, Contacts, Activities, Opportunities, Quotations, and Sales.
Since there is a certain hierarchy to how your records are structured within Claritysoft, it is important to import your Account records prior to importing your Contact records, as only Contacts that are associated with existing Accounts can be imported. If an existing Account cannot be found in the system for a Contact, the record will simply be skipped. Similarly, Opportunities, Quotations, and Sales, can only be imported once the associated Account and Contact exist within your database. If the associated Account and Contact do not already exist, the record(s) you are trying to import will be skipped. When importing Activities, if the associated Account/Contact combo does not already exist, your Activity will still be imported, but as a General Activity, and will not be directly associated with any record.
Note: The same worksheet can be re-used for importing to each module. In the mapping table you will simply map only the fields in your worksheet that are applicable to the selected module.
2. KNOW WHAT YOU WANT YOUR DATA TO DO
While most modules only import data to create new records, the Leads, Accounts, and Contacts modules permit you to select an alternative option which can be used to perform updates to existing records. This option is helpful if you need to mass update fields or field values for records that already exist in your database. All updates are performed at the field level. Your existing data will only be overwritten if your import specifically contains a new value to overwrite the existing with. It is important to know that any fields containing blank, null, or invalid values will not overwrite existing values. Imported values will also not overwrite any existing data contained in a Multi-Select Drop-Down field. The new value being imported will simply be added to the record in addition to any previously selected values for that field.
Create new records, only – Use this option if you don’t want your import file to overwrite existing information in the database. If records contained in your import already exist in your database, they will simply be skipped. The data in the import file will not overwrite any existing information in the database.
Create new records and updating existing records – Use this option if you want to update the data for existing records in your database with the data from the import file. The data in the import file will overwrite data in Claritysoft. Empty cells in your import file will not erase any existing data. Additionally, new records will be added if they do not already exist.
3. IDENTIFY RECORDS IN EACH IMPORT
Though it is not required, we highly recommend the creation of a custom text field in each module (i.e.: Import Source) that can be utilized to help you identify records created and/or updated by a specific import. For each import you perform, the column for this field would then be added to your import sheet and each record populated with the same value of your choosing (file name, date, etc.). Once your records have been imported, you can then easily perform an advanced search or filter on this field using the unique value entered to see all records created and/or updated by the specified import. This field is also very useful in helping you identify where records originated if you are importing data from multiple workbooks or sources.
4. IDENTIFY YOUR COLUMN HEADERS AHEAD OF TIME
While it is not required that the column headers in your import data be named in any specific way, taking the time to do so can certainly save you a lot of time later in addition to preventing any unintentional errors caused by manual data mapping. Assuming you are taking the time to perform a trial run (mentioned in tip #5), it is possible that you might run your data several times before getting it right. Having your column headers in your worksheet already configured to pre-map to the fields in your database can drastically cut your import time and save you the hassle of manually mapping each field each time. This also helps to ensure that you don’t map a column to the wrong field within your database, which could be detrimental to the integrity of your data, especially if your import will be affecting existing records.
5. PERFORM A TRIAL-RUN
While identifying your records (Tip #3) provides a certain means of method, unless you are importing to an empty database, imports typically cannot easily be undone. If you process a large batch of records and have made a mistake, you run the risk of requiring a lot of time and resources to attempt to manually correct the error. That is why it is important to run a small test batch using a handful of records before you import your full file. Use a manageable number, so if you make any mistakes you can manually recover from those mistakes. Run your full import file only once you are confident the import will work the way you are intending.
6. REVIEW YOUR IMPORT FILE
Before you start importing or attempting to import any data, it is recommended that you review your import file carefully. Check for the following inconsistencies and correct any inconsistencies before you import or attempt to import your file. While double checking for these items doesn’t guarantee that your file will run without error, it will certainly improve your chances of a successful import.
• Your import file must be in an Excel .XLS or .XLSX file format.
• The import worksheet must be less than or equal to 100,000 rows. This number includes the column header in row one, so it is limited to 99,999 rows of actual data.
• The first row of your worksheet must contain column headers, rather than actual data.
• Check for erroneous column fill downs. Sometimes a user will unintentionally perform a column fill down that extends data well beyond the rows of actual intended data. If this happens, you will likely receive an error message that your file exceeds the maximum allowance of rows.
• Account Names must be split into separate columns for each component part (only if utilizing the Site field): Account Name and Site.
• Contact Names must be split into separate columns for each component part: First Name, Last Name, Middle Name, Prefix Name, Suffix Name.
• Addresses must be split into separate columns for each component part: Address1, Address2, City, State, Postal Code, Country.
• Check for formatting consistencies. Does the format you are using in your import data match the format of the field you are importing to in Claritysoft?
• Remove columns with data that you don’t need to import. This will keep your data file clean and save you time during any manual mapping.
7. INVEST TIME NOW TO SAVE TIME LATER
Imports can save you and your organization a large amount of data entry time. It is much easier to enter 3,000 new Accounts in a batch than it is to enter them manually one-by-one. However, don’t be hasty with your import processes. Take time to properly format your files, review the data manually by giving it a visual inspection, test your imports, and evaluate your data import summary report emails. While you may spend a handful of hours following rigid methods and procedures for your import, you will still save an abundance of time versus manually entering or updating the data. Keep in mind that if you were to manually enter 3,000 Accounts at two minutes per account it would take you over 100 hours to complete! Generally, the time to follow proper import procedures will always be less than manually entering information in the database (unless it is a significantly small amount of data to be entered). However, if you make mistakes during your import, you may spend more time cleaning up than you would manually entering the data. Picking up the pieces of an error-ridden import can be a long and arduous process. As a result, take your time, follow strict methods and procedures, and get the import right. Good data in equals good data out. Therefore, the time required to import “good data” into your Claritysoft database is well worth it.
8. CHECK THE DATA IMPORT SUMMARY REPORT
After your import has processed, you will receive a Data Import Summary Report to the email address indicated on your user profile. Details regarding the processing of your data will be provided within this email. Any errors contained within the row of data for a record will cause the entire record to be suspended. The line number will be provided for all suspension errors, including a suspension reason detail.The most common scenarios to result in a suspension of a record are as follows:
• Duplication detection
• Import data contains a field value that exceeds maximum character allowance.
• Import data contains a field value that is invalid formatting or unaccepted input.
• Hierarchical pre-requisites not met (such as attempting to import Contacts before associated Accounts).
Imports can be a technical process, but these eight tips can help you get them right. Because imports cannot be reversed and they have the potential to muddy your database quickly and in batch, it is important to be systematic and intentional with your imports. Always double check, recheck, test, and retest your import files before you press the “Import File” button. That way you can import your data with confidence.