Control - Text Based Importing

This document will discuss how to export customers and contacts from Control and also import into Control

• Choose the Company/ Contact explorer and use the column chooser for these fields to maximize the accuracy of the importing process

Company Explorer

• Company Name
• Status
• Department
• Address 1
• Address 2
• Primary Num
• Secondary Num
• City
• State
• Postal Code
• Payment Terms
• Credit Limit
• Customer Credit Balance
• Pricing Level
• Company Pricing Plan
• Active
• Tax Exempt
• Industry
• Region
• Tax Class
• In the Company Search Criteria for the Company Explorer be sure to select all company types and also select Include Inactive Companies.

Contact Explorer

• First Name
• Last Name
• Shipping Address 1
• Shipping Address 2
• Shipping City
• Shipping State
• Shipping Postal Code
• E-mail
• Contact Type
• Primary Contact
• Billing Contact
• In the Contact Search Criteria for the Contact Explorer be sure to select Include Inactive Contacts, Full Contact, and Limited Contact.
• In both explorers, make sure that you are viewing all the contacts/customers respectively

1. When finished, right-click anywhere on each grid then choose export grid
2. Make the export format of the export file a Comma-Delimited Text(CSV) and Export to File.

Below are any columns that require changes and the changs that they need

• Company Export File * The status field is broken up into 3 columns: IsClient, IsProspect, IsVendor which are Boolean values
  • in this example the following formulas are used
    • Cell C2=IF(B2=“Client”, “true”, “false”)
    • Cell C3=IF(B2=“Prospect”, “true”, “false”)
    • Cell C4=IF(B2=“Vendor”, “true”, “false”)
  • To distribute the cell down each column, double click the bottom right corner of the cell you wish to duplicatete.
• In the phone number field, remove all '(', ')', '-', ' '
  • Use text to columns to seperate into 3 fields: Area Code, Phone Number, Extension
• Any Custom Payments, Primary Salespersons, Pricing Plans, or Tax Classes must exist in Control before the the import or they won't be mapped
  • Tax Exempt should be an IsTaxable column with a Boolean value.
    • Cell XX=IF(XX=“Tax Exempt”, “true”, “false”)
  • Active should be an IsActive column with a Boolean value.
    • Cell XX=IF(YY=“TaxExempt”, “true”, “false”)
• Contact Export File
  • In the phone number field, remove all '(', ')', '-', ' '
  • Contact Type should be an IsFullContact column with a Boolean value
    • Cell XX=IF(YY=“Full Contact”, “true”, “false”)
  • Primary Contact should be an IsPrimaryContact column with a Boolean value
  • Billing Contact should be an IsBillingContact column with a Boolean value
    • Use text to columns to seperate into 3 fields: Area Code, Phone Number, Extension

1. In the CompanyExport file, sort in ascending order based on the CompanyName field
2. In the companyexport file, create a CompanyID field in column A
3. Number the first 3 rows, 1, 2, 3, respectively
4. After instering “3”, double click on the small box in the bottom right handed corner

• Be sure to scan down the list of CompanyID's to make sure that any CompanyName has a CompanyID

1. In the ContactExport file, sort in ascending order based on the CompanyName field
2. Copy both the A(CompanyID) and B(CompanyName) columns from the CompanyExport file into the ContactExport file and put them in the A and B column without overwritting data
3. Leave column C blank and make sure that the CompanyName is in the D column
4. In row 2 of column C write this function which would assign CompanyID's to the contact list EVEN IF NOT ALL COMPANIES ON THE CONTACTEXPORT EXIST IN THE COMPANYEXPORT
   • =INDEX($A$2:$A$NUM,MATCH(D2, $B$2:$B$NUM,FALSE),1)
   • where A = CompanyID from CompanyExport
   • B = CompanyName from CompanyExport
   • NUM = The number of the row in which the LAST companyID from column A lies
5. After instering the function, double click on the small box in the bottom right handed corner
6. Copy all data in that column, right click and paste special
7. Paste special using values
8. Remove both columns A and B from the CompanyExport file
9. Compare the ID's from CompanyExport and ContactExport file and make sure that nothing is mismatched
10. On both files, “Save As” a tab delimited file

1. In Control go to Tools–>Import Data–>Import from Text Files
2. In the Source field, browse for the location of your CompanyExport tab delimited file
3. If necessary, Select the “Use Secondary Source” and locate the ContactExport tab delimited fle
4. In the Key column, place a check next to the CompanyID field from the CompanyExport file and another by the ContactID from the ContactExport
5. Choose Companies as the template for the import
6. Map the fields from the Source Data to the Control field
   • CompanyExport CompanyName—> Company→Customer→Company Name
   • CompanyExport IsActive—> Company→Customer→Is Active
   • CompanyExport IsProspect—> Company→Customer→Is Prospect
   • CompanyExport IsClient—> Company→Customer→Is Client
   • CompanyExport Indsutry—> Company→Customer→Industry
   • CompanyExport Payment Terms—> Company→Customer→Payment Terms
   • CompanyExport IsTaxExempt—> Company→Customer→Is Tax Exempt
   • CompanyExport Tax Class—> Company→Customer→Tax Class
   • CompanyExport Origin—> Company→Customer→Origin
   • CompanyExport Credit Limit—> Company→Customer→Credit Limit
   • CompanyExport Credit Balance—> Company→Customer→Credit Balance
   • CompanyExport Primary Area Code—> Company→Customer→Phone Number→ Area Code
   • CompanyExport Primary Number—> Company→Customer→Phone Number→Phone Number
   • CompanyExport Primary Ext—> Company→Customer→Phone Number→Extension
   • CompanyExport Fax Area Code—> Company→Customer→Fax Number→Area Code
   • CompanyExport Fax Number—> Company→Customer→Fax Number→Phone Number
   • CompanyExport Address 1—> Company→Customer→Billing Address→Street Address 1
   • CompanyExport Address 2—> Company→Customer→Billing Address→Street Address 2
   • CompanyExport City—> Company→Customer→Billing Address→City
   • CompanyExport State—> Company→Customer→Billing Address→State
   • CompanyExport Address 1—> Company→Customer→Billing Address→Street Address 1
   • CompanyExport Zip Code—> Company→Customer→Billing Address→Postal Code
   • CompanyExport IsVenddor—> Company→Customer→Is a Vendor
   • Contact Export First Name—> Company→Customer→Contacts→First Name
   • Contact Export Last Name—> Company→Customer→Contacts→Last Name
   • Contact Export Shipping Address 1—> Company→Customer→Contacts→Shipping Address→Shipping Address 1
   • Contact Export Shipping Address 2—> Company→Customer→Contacts→Shipping Address→Shipping Address 2
   • Contact Export City—> Company→Customer→Contacts→Shipping Address→City
   • Contact Export State—> Company→Customer→Contacts→Shipping Address→State
   • Contact Export Zip Code—> Company→Customer→Contacts→Shipping Address→Postal Code
   • Contact Export Primary Area Code—> Company→Customer→Contacts→Phone Number→ Area Code
   • Contact Export Primary Number—> Company→→CustomerContacts→Phone Number→Phone Number
   • Contact Export Primary Ext—> Company→Customer→Contacts→Phone Number→Extension
   • Contact Export Fax Area Code—> Company→Customer→Contacts→Fax Number→Area Code
   • Contact Export Fax Number—> Company→Customer→Contacts→Fax Number→Phone Number
   • Contact Export Email—> Company→Customer→Contact→Email Address
   • Contact Export IsPrimaryContact—> Company→Customer→Contact→Is Primary Contact
   • Contact Export IsAccountingContact—> Company→Customer→Contact→Is Accounting Contact
     • After mapping these fields Save Mapping
7. Select the Advanced Settings button
8. Make sure that Source Files Delimiter = Tab( ) and that Source File String Delimiter = Double Quotes (“)
9. Use All Lines of the Source Files, Heading Row in Source Files, and Skip Blank Lines in Source Files all need to be checked.
   • IF YOU ARE IMPORTING INTO A DATABASE THAT ALREADY HAS COMPANY AND CUSTOMER INFORMATION, BE SURE TO CHECK THE “LOOKUP RECORD BEFORE IMPORTING”
     • Match this field with “Company Name”
10. Hit the Save button when ready and monitor the import for any errors

• Topic Item