Aristotle Support

Focus On: Data Import Tool

Article ID: 794
Last updated: 07 Mar, 2010

Focus On: Data Import Tool

 

Welcome to our Focus On series. In this series of e-mail tutorials, we will show you a few overlooked features in our system. We encourage you to print this document to follow along through the lessons.

 

Tutorial: Data Import Tool

 

Data imports are a way to import new information into your database at the same time. In most cases, your campaign has received a new list of people and you wish to import their basic information into the database. This can drastically reduce the amount of time spent doing data entry.

 

In this tutorial, you will learn:

Lesson 1: Defining your spreadsheet

Lesson 2: How to convert a spreadsheet into a .csv file

Lesson 3: Importing the file into the database

Lesson 4: Deleting imports and Data Import History Report

Lesson 5: De-duping records and standardizing addresses after import

 

In order to use the Data Import Tool, you must have the administrator access permission level.

 

Lesson 1: Defining your spreadsheet

 

In most cases, you will have an existing spreadsheet that you wish to import into the database. For the import to work correctly, you spreadsheet must have all the columns and they must be in this exact order. If you miss a column, the import may fail or may import your information in the wrong fields in the database.

 

This means that if you do not have any information for a particular column, you still must include it in your import.

 

 

Here is the column headers that must be present in your spreadsheet. Again, they must appear in this exact order. Row one will not be imported so they may include the actual names of the headers (like, Name Title, First Name, etc).

 

1.Name Title

2.First Name
3. Middle
4. Last Name
5. Name Suffix
6. Salutation
7. Occupation
8. Employer
9. Organization Name (do NOT use this column if this is an individual)
10. Primary Address (H/W) (Put an H for Home or W for Work. If you leave it blank or if you have any other value, it will set as home).
11. Home Adr1
12. Home Adr2
13. Home City
14. Home State
15. Home Zip
16. Work Adr1
17. Work Adr2
18. Work City
19. Work State
20. Work Zip
21. Work Phone
22. Home Phone
23. Fax Phone
24. Mobile Phone
25. Other Phone
26. Pager
27. Email
28. Email (second)
29. Notes
30. Code1 (All Codes must already exist in the database. Use the Code ID.)
31. Code2
32. Code3
 Columns 30-33 can be repeated up to 20 codes.

 

Important Notes:

 

·         If there is a value in the "Organization Name" field, the record will be imported as an organization and the first 8 columns will be ignored.

·         If there is a value other than H or W in the "Primary Address" field, the home address will be set as the primary.

·         The codes fields must match codes already added to the database, otherwise they will be ignored.

 

Some campaigns prefer to work with a spreadsheet of already defined columns and paste the information into that spreadsheet so they do not have to work about column order. If you use the already defined spreadsheet, you can skip step two. If you would like to download a .csv file with the columns already defined to work from, you may do it here.

 

Ok, now that we have defined our spreadsheet, let’s convert it to a .csv file!

 

Lesson 2: How to convert a spreadsheet into a .csv file

 

If you have defined your spreadsheet in step one and it is in Excel, you will need to convert it into a .csv file before uploading.

 

  1. Open your excel file.

  2. Click on File and Save as.

  3. Select where you would like to save the file (like your desktop or a drive – remember where you saved it!).

  1. In the File Name area, name your document.

  2. In the Save as Type area, select CSV(Comma Delimited) from the pull down menu.

  1. Click Save.

  2. You may receive one or two warning about the formatting of .csv, click yes.

 

The file is now a .csv, let’s import it into our database!

 

Lesson 3: Importing the file into the database

 

  1. From your BackOffice database, click on the Management menu.

  2. Click on Imports.

  3. Next to File, click on Browse.

  4. Find where you saved your file (from step two) and click on that icon and click the Open button.

  5. Select Standard Large Import as your Layout.

  6. Click the Upload button.

 

You should get a message that your upload was successful. Do a common sense check. Review a couple of the entries from your spreadsheet with the database to make sure everything was transferred correctly.

 

You will also see a new box appear for Post-Import tools. If this is your first import, or if you’re still new at imports, don’t use these tools until you have done a common sense check. In Lesson 5, you will learn how to use the same tools after the common sense check.

 

Lesson 4: Deleting Imports and the Data Import History Report

 

Hopefully everything went smoothly in steps 1-3. However, in your review if you notice that your address line 1 field is appearing in your address line 2 field, there is a problem. Review your spreadsheet for misaligned columns. Before you import again, let’s delete the first import.

 

To delete an import:

  1. From your BackOffice database, click on the Management menu.

  2. Click on Imports.

  3. Scroll down to your Data Import History Report.

  1. You should see the date of your import. Click the Details button next to your import.

  1. If you wish to view the records you imported before deleting, click the View All button. To delete your import, click the Delete Import button. You only have two days to delete this import. After two days, this option will no longer be available.

  2. You will receive a warning, click Ok if you wish to proceed. You will receive a second warning since this action can not be undone. Click OK if you wish to proceed.

 

Now this import is deleted. If you have fixed your spreadsheet, you may repeat step 3.

 

Lesson 5: De-duping records and standardizing addresses after import

 

After our records have been imported successfully, we may want to clean-up this data. First, we will want to standardize the addresses we have just imported.

 

To standardize addresses:

  1. From your BackOffice database, click on the System menu.

  2. Select Standardize All Addresses.

  3. You will see a summary of your addresses. Not Checked is how many address are in the system but have not been through the standardization process. Failed is how many addresses that have gone through standardization but have not been found in the USPS database. Certified is how many addresses have passed standardization.

  1. Click on Standardize Added Today.

  2. You should receive a message when address standardization is complete.

  3. Note your summary should have changed:

 

 

Using the Combine Duplicates tool will allow you to do several records at once and reduce the time spent doing this task for many duplicate records.

 

To combine duplicates:

1.      Click on System and Combine Duplicates.

2.      Select the criteria that you want the database to search the records with.

 

 

Method -  Select what information you will be using to compare the records, whether it is by name and address or by name and phone number, you will use this information to compare the records to make sure that they are in fact duplicates.

 

The Records Added Today Only checkbox will allow you to refine your search to records that were entered into your database today for duplicates.  We recommend checking this box if you are reviewing duplicates that might have occurred during the import process.

 

Exact Name Matcheswill find only names that match each other exactly. However, it will not pull exact matches for addresses.

 

3.      Once you selected your criteria, click on Search.

4.      The system will display possible duplicates in your system.

 

 

5.      Review your duplicates  To compare the information of the two records click on the name given.  Each creates a hyperlink back to the individual records where you are able to view them side by side if you wanted. 

 

6.      You can click on the Show Details box, which will then show General Information on the individual and allows you to select information that may be different.  For example, one record has Mrs. Benita Smith and the other record has Ms. Benita Smith, you will be able to choose the name title for the new combined record.

 

7.      Once you have decided that two records should be combined, click on the Combine checkbox. Those that are not checked will not be combined!

 

8.      Once you have completed reviewing this page of records, click on the Combine button to Combine Duplicates. If you have more than one page, the button will say Combine & Next. Be aware that once you click on the combine button at the end of the page, there will be no chance to undo the change.  This can not be undone.

 

When you have reached the end of your duplicates, we recommend you running the report several times to combine triplicates.

 

We recommend this process for removing duplicates, however you may also choose to use automatic duplicate removal.

 

Once you have standardized your addresses and removed your duplicates, you’ve completed all the steps to importing a spreadsheet.

 

Hopefully you now have a better understanding of the Data Import Tool and how it can reduce the amount of time your campaign spends adding records into the database.

 

Do you have an idea for our next Focus On? Is there something you would like to know more about? Simply email us at: support@completecampaigns.com. We’d love to hear from you!

This article was:   Helpful | Not helpful Report an issue


Article ID: 794
Last updated: 07 Mar, 2010
Revision: 1
Views: 437
Prev     Next
Letter Templates Tutorial       Focus On: Database Maintenance