Getting Started
Review these helpful tips prior to using the Historical Data Tool.
To import your data into the system, you will need a Comma Separated Values file, referred to as a CSV. To create a CSV file, save an Excel document as a CSV file type. Before you do this, you want to clean up your Excel file to make the importing process go smoother. Here are some tips:
All columns need a header.
Excel often has issues determining the number of columns for a CSV if the last column in the excel file contains blank cells. To prevent this and create a clear stopping point, add a final column and fill in the cells with dummy information. You can simply copy and paste the same information for every cell in this column, and it can be anything you want.
When converting from an Excel document, certain types of formatting do not translate well to the CSV file. For example, a comma in a CSV may mess up the actual information in the cells. We recommend replacing all of the commas in your file with a semi-colon to combat this issue. You can either replace them in your Excel document, or in the CSV file.
When you have finished cleaning up your data, save your Excel doc as a CSV file type. This is the file you will use to import your data.
You may want to go over your system configurations and settings before doing an import. For example, you will want to determine what fields are required and determine if contacts need unique email addresses to get your system off to a good start.
Require Unique Email Address on Contacts
Set this configuration to 'Yes' if you want the system to prevent users from adding a contact if the email is already in the system. The user will get a warning that the email already exists and they won't be able to continue until they update the email.
The default email can be used multiple times even if you have this set to yes. Users can continue to add as many contacts as they wish if they all use the default email. The unique check is only made when the email is different than the default.
A sheet is an uploaded or imported CSV file which houses data that can be referenced later in the Historical Data Tool. The sheets are not connected to anything within your system. This ‘holding area’ can be helpful if you would like to store information that is not able to be imported, such as invoice history or a specific report from a previous system. You can always return here to reference this information. However, the sheets section is not just for storage. To import your data into the system, you have to start with a sheet. You can then go on to create a merge sheet with one or more of your sheets, then import your data.
Start the import by going to Settings > Data Import/Export > Historical Data Tool. You can upload all your data at once, or you can choose specific data, such as information for your companies.
Click on Create New in the Sheets section. Name your sheet clearly and simply, as you will be referencing this sheet throughout the import process. Choose the CSV file you want to upload. You can also choose to give it a category. If you are planning on importing large amounts of data, the categories can help you stay organized.
Tip
You will likely want to choose the options to convert characters to ASCII, strip HTML, and skip invalid rows when you are creating your sheet. All of these options will help clean up your data and make the whole importing process easier. Here is what each option does:
Note
The system requires information in columns that you may not collect. If you do not fill these columns: Publisher, Market, Payment Terms, Payment Method, Special Billing Flag, Company Type and Credit Status you will be able to use the append columns on the export to add those to your sheet.
Click Create to upload the sheet.
Notice
All uploads and imports are processed in the background, so you don't have to wait for your data to get into the system. You will receive an email once the process is complete. If you are working with a small data set, you can wait on the page for the alert. Otherwise, go get a coffee!
Once the sheet has finished uploading, you need to make at least one column searchable to start the import process. Click on the name of the sheet to choose which columns you would like to be searchable. The searchable fields are also used to merge this sheet with other sheets. We recommend using IDs or Name fields when doing company and contact importers. Select Save Settings and Import.
Tip
If you are planning to combine multiple sheets, for example if you had one sheet with your contacts and another with companies, upload those sheets at this stage.
Tip
Be sure to select the same searchable fields on each sheet you would like to merge. For example, if you have two sheets one with companies with a company ID and a contact sheet with a company ID, you would want to make sure you are selecting company ID as searchable on both sheets.
A merge sheet is either one sheet that contains all necessary data or a combination of sheets that need to be joined together. For example, you may want to import companies into the system along with contacts, but you have them on two different documents--instead of spending the time to combine them, you can merge them easily within the system.
Once you have created a merge sheet, you can create an export. Up until now, your sheets have been in a ‘holding area’, where you can interact with and reference the data. However, the data has not yet been integrated into the system. The export stage of the process is what lets you integrate the data from your sheets into the system. You will match up the data on your sheets with the data that is in the system, and run tests to check for errors and duplicates. These tests are extremely helpful, because they allow you to fix the errors and prevent duplicate records before any of the data is actually in your system.
Creating an Export
Click on Create New in the Exports section.
Tip
You should name the export the same as your original sheet for easy reference and to prevent confusion.
Choose the merge sheet you want to use, and choose the importer. The importer is based on what type of data you are importing. For example, if you are dealing with data for your companies, then you would choose Company Import. Click Create.
Mapping the Data
The next step is to ‘map’ the data. This means you need to line up the columns of data that are required in the system, with the columns of data that are on your sheet. For each column, there will be a Merge Sheet Column drop down, from which you can choose the header from your sheet that matches.
Note
You might have the header “company_name” on your merge sheet, but the same field is titled “Company Name” in the system. So, for the Company Name field, you will need to select “company_name” from the Merge Sheet Column drop down menu.
If there are columns of data that are required in the system, that you do not have on your sheet, you have the option of editing the columns on your original file and restarting the importing process. However, you can easily Append the columns instead. Click Append Columns and select Add New Column. You will need to choose an Importer Field (a column) and input a value for that field. Click Save.
Tip
On the mapping page, all the columns have a description that displays the acceptable values for that field. You can also navigate to Settings > Data Import/Export > Import Data and select different importers to see acceptable values. If you are using a name as the value, instead of a numerical value or ID number, the name needs to match what is in the system exactly.
Warning
For the Special Billing Flag field on the Company Importer, the acceptable values are 0 = false, and 1 = true. Therefore, if you did not have a column for a Special Billing Flag on your sheet, and none of your companies need special billing, you could choose Append Column – Add New Column. You would then select Special Billing Flag from the importer field, and add a 0 to the value field.
Once you have lined up all of your columns of data, and appended as needed, click Save at the bottom of the page.
Test and Validate
You can now select Test and Validate, which is essentially a trial run of the import, and will show you any errors that occurred, before you fully import the data into the system.
Tip
You should always run a Test and Validate before doing a Direct Import, so that you can fix any errors, and make sure all of your data is getting correctly imported into the system.
Fix Errors
Once Test and Validate is finished running, click on the name of the export, and select View Data. When viewing the data, you can choose to search the results by a few different fields, including all the errors that occurred. Any rows with errors will display in red. By clicking on the ellipsis action on the right side, you can select View Errors, which will show you what caused it to fail.
There are multiple ways to fix the errors
Editing Errors Individually: By clicking on a specific value in a row, you can edit that individual item. Click the check mark icon to approve the edit, or click the x icon to stop editing and revert it to the original value.
Edit Errors by Column: A good way to bulk edit your errors is by clicking on Edit Errors by Column. Selecting a column from the drop-down menu will display all of the values in that column that failed. You can edit these values by clicking on the pencil icon. When you edit the value, it will fix all of the rows with that error.
Example: A common error is the Country column on the Company Importer. Many people will have a country written out (United States of America), but the system will only accept a two-letter country code (US). So, by selecting Country in the drop-down menu, you will be able to edit the United States of America value and change it to US.
Note
You cannot modify an appended column like you can the other columns on this page. If you create an appended column and it fails, you will need to return to your merge sheet and fix the appended column there.
Bulk Edit: By clicking on Bulk Edit, you can select any column from your sheet and apply a rule, such as ‘replace values’. You would then proceed to fill in the prompted information, including the new value for the column. While you can use Bulk Edit to fix your errors, we recommend using Edit Errors by Column instead, as you can edit any column, and not just the columns that have errors. This is a more useful tool for updating or changing your data later on.
Creating Rules: On the Historical Data Home Page, under the Exports section, you will see an option for View Rules. This feature allows you to create custom rules for items that are often inaccurate, or that you know will be an issue ahead of time. With rules created, you will not have to manually fix those errors when they come up. To create a rule, on the View Rules page, select Edit Custom Rules and click Add Rule. You will then be able to select the importer you would like the rule to apply to, and the field (column) you want to make the change in. Then, you will need to add the current value, and what it should be replaced with. Once you are finished, click Save Rules.
Example: On the Company Importer, you need a primary rep column. The acceptable values for this column in the system are either a user ID or an email address. If your sheet has the rep name filled in for the primary rep column and not their ID or email, the column will fail. Therefore, you can create a custom rule where the value ‘John Smith’ for the primary rep column is replaced with ID ‘1917’ or email ‘jsmith@adorbitdata.com'. The system will then automatically replace any ‘John Smith’ with an acceptable value and the column will not fail.
Check Similarities
After you have fixed the errors on your data, you may want to run the Check Similarity feature. If you are on the Historical Data Home page, click the name of the export. This will bring you to the page that you can perform the Check Similarity action. Check Similarity shows you if anything you are importing closely matches items that are already in your system. If you are using the Company Importer, for example, this feature will help prevent duplicate companies. Click Check Similarity. Once it is finished running, click on the name of the export.
Now, you can search your data by ‘Has Similarity’ and add a similarity index filter. The similarity index filter is just to tell the system how closely a name can be to something that already exists in your system. Select View Data to see all of the errors that are caused by similarities.
By clicking on the action ellipsis on the right side, you can select View Similarities. This will show you the item that already exists in the system, and its similarity index. There are a few different things you can do at this point.
If you still want to import the item
You can edit the name of the item you are trying to import so that it does not get flagged as similar.
If you know the item you are trying to import is actually different than the item that is in the system, even if it is getting flagged as similar, you can choose to ignore the error and move on to the steps for Finalizing the Export below. You would not need to perform any other actions at this stage.
You can merge the item with the existing item in the system. On the View Similarities pop-up, you would check the Use option, and click Save. This will give the item an import record ID, and will merge any information you have on your sheet with the data that is already in the system.
If you do not want to import the item
By clicking on the action ellipsis on the right side, you can select Unset Ready for Import. When you run the import, it will skip over that item.
You could also remove the item from your original CSV file and start the process from the start.
Finalizing the Export
Once you have edited the errors and run the similarity check, click on Test and Validate once again to double check the results. When the test is done running, click on the name of the export. If there are no more errors displaying, select Direct Import. This will add all of the data into the system.
Tip
You will always have this data recorded in the system, so you can bulk update what you already imported, by clicking on the export name, and selecting Bulk Edit. For example, maybe you imported your records to the wrong rep. Bulk edit the rep column to the correct rep and then do another direct import. If you do a new direct import, the system will update ALL fields on the company or contact; not just the updated column.
You have reached the end of the Importing Historical Data process. CELEBRATE!