Control - Text Based Importing
This document will discuss how to export customers and contacts from Control and also import into Control
Exporting Customers and Contacts
- 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
- When finished, right-click anywhere on each grid then choose export grid
- Make the export format of the export file a Comma-Delimited Text(CSV) and Export to File.
Formatting Contact and Customer List
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
Mapping Companies and Contacts Using Two Different Files
- In the CompanyExport file, sort in ascending order based on the CompanyName field
- In the companyexport file, create a CompanyID field in column A
- Number the first 3 rows, 1, 2, 3, respectively
- 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
- In the ContactExport file, sort in ascending order based on the CompanyName field
- 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
- Leave column C blank and make sure that the CompanyName is in the D column
- 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
- After instering the function, double click on the small box in the bottom right handed corner
- Copy all data in that column, right click and paste special
- Paste special using values
- Remove both columns A and B from the CompanyExport file
- Compare the ID's from CompanyExport and ContactExport file and make sure that nothing is mismatched
- On both files, “Save As” a tab delimited file
Importing Customers and Contacts
- In Control go to Tools–>Import Data–>Import from Text Files
- In the Source field, browse for the location of your CompanyExport tab delimited file
- If necessary, Select the “Use Secondary Source” and locate the ContactExport tab delimited fle
- In the Key column, place a check next to the CompanyID field from the CompanyExport file and another by the ContactID from the ContactExport
- Choose Companies as the template for the import
- 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
- Select the Advanced Settings button
- Make sure that Source Files Delimiter = Tab( ) and that Source File String Delimiter = Double Quotes (“)
- 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”
- Hit the Save button when ready and monitor the import for any errors
- Topic Item
You could leave a comment if you were logged in.