====== 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 {{::importsample2.jpg?nolink&|}} * 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 ===== See Also ===== * [[my_links]] * [[include_pagepage_componentbacklinks]]