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.

NOTE: For a small service fee, our Customer Success team will upload your customer list or standard billing items. Please contact us through any of the regular support channels if you are interested in this service. It is important, however, to ensure that your data is clean and accords with the instructions on this page so that your data is usable and as expected.

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.

Field Usage Description
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
Email 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)
Street 1 optional Street address of location
Street 2 optional Second street line (e.g. "Apt 4")
City optional City of location
State optional State abbreviation (e.g. "MA" for Massachusetts)
Postal Code Optional 5-digit zip code

If you wish to import multiple Phone Numbers, you will need independent columns for each phone number.

NOTE: 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.

Field Usage Description
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
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

NOTE: 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.

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.

NOTE: Zapier may pause your import if it contains more than 5,000 records at a time. If you have a large data set, such as 7,000 customers, we may suggest separating your Google Sheet into multiple worksheets.

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.

NOTE: Be careful when you use this technique because it will overwrite data in the next column if present. Before completing this action you will want to Insert the number of blank columns to accommodate the data split.

Here is an example scenario to demonstrate this workaround. Consider an export that has the first and last name in one field. Select the data or column that you want to split.

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.

Still need help? Contact Contact