Get your Companies, Contacts, and Orders into the system using the Historical Data Tool
Using the normal importers, you can upload CSV files and directly import data into the system. However, the normal importers do not allow you do to any validation testing or checks against the system database. You can use the historical data tool to import your data into the system and manipulate the data from within the system before it's imported into the database. The tool allows you to merge your data files, append missing columns to your data, view errors and correct them, check for similar companies, and directly import the data. Once data is imported, you will be able to use your old reference numbers and the new system generated ID. In the example below, we will go through importing companies, merging those imported companies with contacts, and then importing orders.
Start the process by going to Settings > Data > Historical Data Tool and uploading your data. You can upload all your data at once, but for this example, we will go data set by data set. We will start by uploading the company data. At this point, you don't have to worry about column headers or mapping.
When you upload CSV file as a 'sheet', you can name the sheet, give it a category, and choose to skip the header row, invalid rows, and strip out any HTML, which can cause importer errors. Select your file to import your data in the site.
Notice
All 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 a sheet is in the system, you need to make at least one column searchable to start the import process. The searchable fields are also used to merge with other sheets. We recommend using IDs or Name fields when doing company and contact importers. In this example, I will set both the ID and the Name to be searchable.
Now you can create a merge sheet. A merge sheet is either just your original sheet or a combination of sheets that are joined together based on a similar column. For example, you may want to import companies into the system with contacts, but you may have them on two different documents. Instead of combining them outside of the system, you can upload your Company file, then your Contact file, and then merge them on something like 'Company Name' or 'Company Reference Number'
With the merge sheet in the system, you will see a new column on your data set: import record ID. Once the system processes the import, your data set will include the new system ID so you can use it for reference for future imports or data overrides.
Now we are going to start the import process by mapping the company columns to the required column for system import.
Tip
Don't have one of the required fields on your sheet? You can always use the append tool to add a column and set a contact for the field instead of re-importing the file. You can append your files with constant values for all records, or go back and add the column to your file and re-start the process.
I can also set rules so my data is modified for import. For example, to import a company you need a User ID or email address. If your company file has a rep name, it won't work. but if I create a rule where when 'John Smith' is in a column replace with ID 1917 or email jsmith@adorbitdata.com, the system will know to update my data for import.
Always run a 'test and validate' before doing a direct import. You will see what records will be successful and which will fail. You can view errors to see exactly why a record can't get imported into the system. You can have the option of in-line editing each field, bulk editing an entire column, or use the edit errors by rows to up the same errors by row and column. You can also run a similarity check to see if any of your company may already be in the system so you can skip the record during the import process.
Important
You can't modify an appended column. If you set an appended column to a value and it fails, you need to change the appended column.
Note
You will only see one row for each error in the system. For example, if you have 150 rows that had the same error, you will see one record for the row. When you update the record, it will update all of your records.
Once your data is cleaned up, you may want to check to see if any of my companies are close in name to other companies in my system. Run the similarity check to find out what companies closely match other accounts in my system. After the similarity check occurs, you can filter to find companies that are similar to companies in your system; then you can decide to either import the companies, remove them, or merge them with the current company.
Once you fix your errors, do another test. I have re-run and fixed my error of the category field not matching. Now, all my records will successfully import so I will use the direct import option.
Now we need to add contacts to complete our company records, I will follow the same process as before: upload my sheet and make a field searchable. I am going to make ID searchable so I can map it to my company sheet that I already imported.
I will now create a merge sheet, but this time I will join it with my company sheet. I will join on the ID to create a new data set. My company data now has a newly populated field: Company Import ID. I will use this field to make sure my contacts are mapped to the correct company. I could use the name, but I prefer to use an ID.
With my new merge sheet created, I will map by contact columns, starting with the company import ID. I will also include a dynamic attribute for the contact's birthday. I will also make sure to set the primary contact field for at least one company contact and I will map the billing and artwork contacts if I know them.
I checked the error and found none, so here goes the import! I now have companies and contacts in my system. The next step is to enter in some historical order.
Before I can import any historical order, I must have products set up in my system. I am going to import orders with print and digital ads, services, and impression items. Some of my items will be past orders and others will be in the future. I will also have a mix of invoiced items and items that need to be invoiced
I will go through the same process as before: upload the sheet, make a column searchable, and merge the sheet with my company file. I want to merge my orders with the company import because my order file used an old company ID and not a name. I am able to map this old company ID to the company data set I already imported. Now I can use the new company ID on my order file. I would have had to do this excel before.
I need to map the file to the historical import tool and test and validate the data. I have some errors, so I will fix them using the edit column feature. My ad size names don't exactly match what I setup, so I can change every ad size name using the columns errors.
Now with my errors fixed, I will re-test. All my errors are fixed, so in go the orders! I will have a record of everyone of my merge files and imports in case I need to go back and modify any data. I can update and re-run an import, which will override my system data.
Warning
If you do a new direct import, the system will update ALL fields on the company or contact; not just the updated column.
