Importing Customers and Billing Items
Using a couple of tools -- Google Sheets and Zapier (3rd Party Tools not managed by Dispatch) -- you can import your existing customer list or standard product/service billing items by following the instructions in this article. Not only can you bulk import data into Dispatch, you can also import data in pieces, such as 500 customers now, another 800 customers next week. We've put the power in your hands at your discretion and on your time.
Before starting this process, you will need to export your existing customers or billing items from whatever system in which they are stored. Many systems offer the ability to export as a Comma Separated Values (CSV) file or as an Excel spreadsheet. For the import to work, it is essential that you understand what fields are required and available to complete the import, which are detailed below.
Customer import fields
Your can download our import customers CSV template to see what customer fields can be imported into Dispatch. You can open the CSV file in Excel, Google Sheets, or a similar program. The headers for the columns are the fields that you will need to ensure exist in your exported file.
The field names and order of columns do not need to agree perfectly with those found in the template, but you will need equivalent required fields in your exported file at the very least. Below is a table of the available and required fields for customer imports. You must have a column for each of the Field that you wish to import.
|First Name||required||Customer first name|
|Last Name||optional||Customer last name|
|Description||optional||Description or other notes about Customer|
|Company||optional||Company associated with Customer|
|optional||One valid customer email address|
|Phone Number||optional||One valid 10-digit phone number|
|Phone Type||optional||"mobile" or "home" or other|
|Primary Phone||optional||"yes" or "no" ("yes" should be mobile to receive text alerts)|
|Home Street 1||required||Street address of location|
|Home Street 2||optional||Second street line (e.g. "Apt 4")|
|Home City||required||City of location|
|Home State||required||State abbreviation (ex: "MA" for Massachusetts)|
|Home Postal Code||required|| 5-digit zip code
|Home Country||required|| "United States" or "Canada"
If you wish to import multiple Phone Numbers, you will need independent columns for each phone number. If your exported fields do not match the above or have obvious equivalent fields, you may want to check out the Workarounds section at the bottom of this article.
Billing Items import fields
Many users have a standard set of product and service billing items that are stored in QuickBooks or other inventory/billing management systems. You can bulk import these items into Dispatch to use in our Estimates and Invoices.
You should download our import billing items CSV template file to see what billing items fields can be imported into Dispatch. You can open the CSV file in Excel, Google Sheets, or a similar program. The headers for the columns are the fields that you will need to ensure exist in your exported file.
The field names and order of columns do not need to agree perfectly with those found in the template, but you will need equivalent required fields in your exported file at the very least. Below is a table of the available and required fields for product/service items imports. You must have a column for each of the Field that you wish to import.
|Title||required||Item title such as "copper pipe" or "dishwasher installation service"|
|Type||required||"product" or "service"|
|Description||optional||Additional, longer item description|
|Price||optional||Default price for the item - if not supplied, the default will be $0.00|
|SKU||optional||Specific SKU number/ID for internal usage|
|Taxable||required||"true" or "false"|
|External Item ID||optional||Unique ID reference from your accounting system|
|Active||optional||Unique ID reference from your accounting system|
If your exported fields do not match the above or have obvious equivalent fields, you may want to check out the Workarounds section at the bottom of this article.
Preparing your data into Google Sheets
In order to import your exported file, you will first need to convert it into a Google Sheets file to prepare it for Zapier. You can do so with Google Drive or Google Sheets.
Importing into Google Sheets
1. Login to any Google Account and go to Google Sheets.
2. Create a new Blank spreadsheet
3. In the File menu, select Import and choose your exported file of customers or billing items
4. Select Replace spreadsheet and click Import at the botto
Adding your Import Sheet
In order to get your data into Zapier, you will not actually use the Sheet that was created with the file. Instead, you will need a new sheet with your first two rows (column headers and first record)
Start by clicking on the Plus (+) button in the lower left to Add Sheet, or using the Insert menu to select New sheet.
You will then need to select Row 1 and Row 2 in the original Sheet so that you can copy and paste it into the new Sheet (Zapier requires that you have a header row and at least one row with an example record in it.)
Finally, we recommend that you rename this new Sheet to "Import" or something else that you will recognize when you see it in Zapier. You can double click on the name to do so.
Importing your data into Dispatch using Zapier
If you do not already have one, you will need to create a Zapier account first.
After logging in to Zapier, type "Dispatch" into the Search bar
Click on the Dispatch icon and scroll down to see the available Zaps.
Open the appropriate Zap: for customers, it is called "Add Dispatch customers from new rows on Google Sheets"; for billing items, it is called "Add products and services from new rows on Google Sheets".
From there, you will follow Zapier's prompts to authenticate your Google account and select the Google Sheet you created.
Then, you will follow the prompts to authenticate your Dispatch user account in Zapier. Your account must have the role of Dispatcher or Both to complete the import.
- Be sure to select "Production" from the "Environment" field
- Your Dispatcher user email address goes in the "Username" field
- Your Dispatcher user password goes in the "Password" field
In the next steps, you will map the fields between your Google Sheet and the fields in Dispatch. Zapier needs to know which information from the Google Sheet belongs in each customer or item field in Dispatch. The process is self-explanatory, but you should be very careful that your mapping is accurate because it cannot be undone.
Next, you will run a test import of your example row, the first customer record. If the customer or billing item appears successfully in Dispatch, set the Zap to live. The last step is to copy all of your customers from your imported customer list sheet into the "Import" worksheet below your test row. The Zap will run the import for all of the new rows added to the worksheet.
Workarounds for data issues
The most common issues that prevent users from importing their data into Dispatch are caused by different data formats in exports from existing systems, since each software/database stores information differently. Fortunately, Google Sheets have some tools that can help you overcome these problems.
Splitting fields into two columns
One common problem we have seen is that customer names are exported as a single field, rather than as the required two distinct fields for a customer's first name and last name. In order to fix this, you may be able to split data into columns in Google Sheets.
Click on the Data menu and select Split text to columns...
In the dialog that opens, choose the type of Separator, in this case a Space.
Your one column will now be split into two columns.
Setting a Primary Phone
Some of our customers have exported data from their existing systems only to discover that the CSV file has no column for Primary Phone. This field is particularly important for Dispatch because it identifies the mobile phone that will receive customer text alerts. A workaround for this missing field is possible if you have a Phone Type field that includes a mobile phone value (e.g. "mobile" or "cell").
Insert a new column next to the Phone Type column and add a header row of Primary Phone
Notice that there are three mobile numbers and three other types of numbers in the above data. In this case, add the following formula into cell E2.
The formula reads cell D2 to see if "mobile" is present. If so, it inserts "yes"; if not, it inserts "no", the desired entries for importing this field. You can then select this field and drag the lower righthand corner down the column to propagate this formula to the fields below. You should see something like this completed version of the example data above.
Replacing incorrect data formatting
Another issue can arise when data is not formatted in such a way that it can be imported into Dispatch. One example might be the complete spelling of a state rather than its postal abbreviation, which you can rectify by finding and replacing data in Google Sheets.