Difference between revisions of "Importing Customers"

From High5Wiki
Jump to navigation Jump to search
 
(10 intermediate revisions by one other user not shown)
Line 1: Line 1:
When importing customers, you will be importing into both the Cust (Customer) table and the CustCont (Customer Contact) table. You will need the following:
+
To import customers, you will need the following:
 
<br>Spreadsheet with your billable customer
 
<br>Spreadsheet with your billable customer
<br>Spreadsheet with your site customers (If you are using parent/ child structure)
+
<br>Spreadsheet with your site customers indicating the parent for that site (If you are using parent/ child structure)
<br>To review parent child structure, please click [[Customers]]  
+
<br>To review parent child structure, please see the video on adding customers by clicking '''[[Customers]]'''
<br>Spreadsheet for your contacts and what customer they are associated with
 
 
 
  
 
<br>First you will import your billable customer (parent)
 
<br>First you will import your billable customer (parent)
 
<br>Then you can import the site customers and link them to the parents
 
<br>Then you can import the site customers and link them to the parents
 
<br>**Make sure each customer record you import is a unique name. QuickBooks does not allow you to have the same name twice.**
 
<br>**Make sure each customer record you import is a unique name. QuickBooks does not allow you to have the same name twice.**
 +
 +
<br>Importing contact names and phone numbers will be done by importing into the CustCont table and linking them to the Cust table.
  
 
<br>Before preparing for the import, please read this page: '''[[Before Importing - About UID]]'''
 
<br>Before preparing for the import, please read this page: '''[[Before Importing - About UID]]'''
Line 15: Line 15:
 
== Step 1: Prepare the Billable Customer import: ==
 
== Step 1: Prepare the Billable Customer import: ==
 
<br>1. Open Excel and create columns for the site and billing tab based off image 1 below.
 
<br>1. Open Excel and create columns for the site and billing tab based off image 1 below.
<br>2. Paste the customer name in the '''Name''' and '''BillName''' column, as well as the address info.
+
<br>2. Paste the customer name in the '''Name''' and '''BillName''' column, as well as the address info. '''**'''Customer names must be no longer than '''40 characters''' if you will be syncing the customer to QuickBooks'''**'''
 
<br>3. In the '''IsBillable''' column, enter TRUE for each row
 
<br>3. In the '''IsBillable''' column, enter TRUE for each row
 
<br>4. If the customer needs to sync to QuickBooks, create a column for '''QBExport''' and enter TRUE in each row.
 
<br>4. If the customer needs to sync to QuickBooks, create a column for '''QBExport''' and enter TRUE in each row.
Line 23: Line 23:
  
 
<br>5. Enter a column for '''IsActive''' and enter TRUE in each row
 
<br>5. Enter a column for '''IsActive''' and enter TRUE in each row
<br>6. If there are other fields you want to import into such as Terms, TaxGroup, Business Type, you will need to export the tables to get the UID as described above.
+
<br>6. If there are other fields you want to import into such as Terms, TaxGroup, Business Type, you will need to export the tables to get the UID as described on [[Before Importing - About UID]].
  
 
<br>[[File:CommonFieldsInCustomerImport.png]]
 
<br>[[File:CommonFieldsInCustomerImport.png]]
Line 36: Line 36:
  
 
<br>[[File:CustTableColumnHeaderNames.png]]
 
<br>[[File:CustTableColumnHeaderNames.png]]
 
  
 
== Step 2: Import the Billing Customers ==
 
== Step 2: Import the Billing Customers ==
Line 66: Line 65:
 
<br>1. Open an Excel file
 
<br>1. Open an Excel file
 
<br>2. Prepare the column headers as shown in the below screenshot
 
<br>2. Prepare the column headers as shown in the below screenshot
<br>-  Column A - F is for the ''site'' information (Name, Address1, Address2, City, St, Zip)
+
<br>-  Column A - F is for the ''site'' information (Name, Address1, Address2, City, St, Zip) '''**'''Name must not be longer than '''40 characters''' if you are syncing the customer record to QuickBooks'''**'''
 
<br>-  Column G - M is for the ''parent'' info (BillingUID, BillName, BillAddress1, BillCity, BillSt, BillZip)
 
<br>-  Column G - M is for the ''parent'' info (BillingUID, BillName, BillAddress1, BillCity, BillSt, BillZip)
 
<br>[[File:SiteImportSpreadsheet.png]]
 
<br>[[File:SiteImportSpreadsheet.png]]
Line 73: Line 72:
 
<br>-  '''IsBillable''' - value in column is FALSE for “child” customers
 
<br>-  '''IsBillable''' - value in column is FALSE for “child” customers
 
<br>- ''' QBExport''' - if you want the customer to sync to QB, enter TRUE in the column
 
<br>- ''' QBExport''' - if you want the customer to sync to QB, enter TRUE in the column
<br>-  '''TaxGroup and TaxGroupUID''' - as described in the parent import
+
<br>- '''IsActive''' - value in column is TRUE to make sure customers have an active status
 +
<br>-  '''TaxGroup and TaxGroupUID''' - as described in the parent import - you will need to get the UIDs out of your SME database.
 
<br>-  '''Terms and TermUID''' - as described in the parent import
 
<br>-  '''Terms and TermUID''' - as described in the parent import
 
<br>[[File:CustomerImport_AdditionalColumns.png]]
 
<br>[[File:CustomerImport_AdditionalColumns.png]]
 
  
 
== Step 5 : Import the Site Customers ==
 
== Step 5 : Import the Site Customers ==
Line 88: Line 87:
 
<br>1. You will be asked if you want to backup your data. You should be regularly backing up your data, so usually people click No to skip the backup.  
 
<br>1. You will be asked if you want to backup your data. You should be regularly backing up your data, so usually people click No to skip the backup.  
 
<br>1. When the import is complete you can close the Data Import window and look in the Customer module for the site customers you imported.
 
<br>1. When the import is complete you can close the Data Import window and look in the Customer module for the site customers you imported.
 +
 +
 +
== Next - Importing Contacts for the Customers ==
 +
Click [[Importing Contacts for Customers]] to learn how to import contacts for the customers.
 +
<br> If you aren't importing contacts, you can now sync the customers over from SME over to QuickBooks. If you are import contacts, wait to do the sync until after the contacts are imported.

Latest revision as of 11:46, 11 October 2013

To import customers, you will need the following:
Spreadsheet with your billable customer
Spreadsheet with your site customers indicating the parent for that site (If you are using parent/ child structure)
To review parent child structure, please see the video on adding customers by clicking Customers


First you will import your billable customer (parent)
Then you can import the site customers and link them to the parents
**Make sure each customer record you import is a unique name. QuickBooks does not allow you to have the same name twice.**


Importing contact names and phone numbers will be done by importing into the CustCont table and linking them to the Cust table.


Before preparing for the import, please read this page: Before Importing - About UID


Step 1: Prepare the Billable Customer import:


1. Open Excel and create columns for the site and billing tab based off image 1 below.
2. Paste the customer name in the Name and BillName column, as well as the address info. **Customer names must be no longer than 40 characters if you will be syncing the customer to QuickBooks**
3. In the IsBillable column, enter TRUE for each row
4. If the customer needs to sync to QuickBooks, create a column for QBExport and enter TRUE in each row.


Image 1:
CustomerImport Step1Image1.png


5. Enter a column for IsActive and enter TRUE in each row
6. If there are other fields you want to import into such as Terms, TaxGroup, Business Type, you will need to export the tables to get the UID as described on Before Importing - About UID.


CommonFieldsInCustomerImport.png


ImportCustomer Step1Image2.png



To see all the column header names in the table you are importing into:
- Go to Utilities > Utilities > Export Data and click Run it
- In the Export window that open, select the Cust table in the drop down
- The Source Fields column show you all the column header names you would use in your Excel spreadsheet


CustTableColumnHeaderNames.png

Step 2: Import the Billing Customers

It is a good idea when importing, import first a small sample of your whole spreadsheet including the column headers. Check your import before importing the whole spreadsheet. (for example, copy row 1,2 & 3 into another spreadsheet and call it Test Import. Import that first, check your import, and if all is fine import the full spreadsheet, but remember to take out the records you imported off the full spreadsheet - the customers that were in row 2 & 3)


1. Go to Utilities > Utilities > Import Data and click Run It. The Data Import window opens
2. In the Source Filename field, browse for your import file. (when browsing for your file, make sure you are search “All Files”)
3. In the Destination table drop down, select Cust
4. You will see a preview of your spreadsheet in the grid
5. Click start.
6. You will be asked if you want to backup your data. You should be regularly backing up your data, so usually people click No to skip the backup.
7. When the import is complete you can close the Data Import window and look in the Customer module for the billing customers you imported.


DataImportWindow Cust.png


Step 3: Export out the Billing Customers to get the UIDs


1. Go to Utilities > Utilities > Export Data and click Run It
2. In the Export Window, select Cust
3. Move the following fields from the source field side to the destination field side: UID and Name
4. In Output Directory field, select your desktop
5. In the Output Filename, enter Cust.csv
6. Click start.
7. When completed, go to your desktop and open the file.


Step 4: Prepare the Site Customer Import

(site customers are customer records linked to the parent/ billing customer)
1. Open an Excel file
2. Prepare the column headers as shown in the below screenshot
- Column A - F is for the site information (Name, Address1, Address2, City, St, Zip) **Name must not be longer than 40 characters if you are syncing the customer record to QuickBooks**
- Column G - M is for the parent info (BillingUID, BillName, BillAddress1, BillCity, BillSt, BillZip)
SiteImportSpreadsheet.png
3. Fill in the columns with the correct information
4. Add the following columns if necessary:
- IsBillable - value in column is FALSE for “child” customers
- QBExport - if you want the customer to sync to QB, enter TRUE in the column
- IsActive - value in column is TRUE to make sure customers have an active status
- TaxGroup and TaxGroupUID - as described in the parent import - you will need to get the UIDs out of your SME database.
- Terms and TermUID - as described in the parent import
CustomerImport AdditionalColumns.png

Step 5 : Import the Site Customers

First import a small portion of your import file, such as the first three rows including the column header row- row 1. After you import the test file, check what you imported. If it looks fine, then you can import the rest. Remember to take the customers you imported on the test import off the full import list or they will be imported twice.


1. Go to Utilities > Utilities > Import Data and click Run It. The Data Import window opens
1. In the Source Filename field, browse for your import file. (when browsing for your file, make sure you are search “All Files”)
1. In the Destination table drop down, select Cust
1. You will see a preview of your spreadsheet in the grid
1. Click start.
1. You will be asked if you want to backup your data. You should be regularly backing up your data, so usually people click No to skip the backup.
1. When the import is complete you can close the Data Import window and look in the Customer module for the site customers you imported.


Next - Importing Contacts for the Customers

Click Importing Contacts for Customers to learn how to import contacts for the customers.
If you aren't importing contacts, you can now sync the customers over from SME over to QuickBooks. If you are import contacts, wait to do the sync until after the contacts are imported.