Whether you are new to ServiceWorks, or have been a customer for awhile, sometimes you may need to upload a spreadsheet with customer information. The guide below will help you with that, while avoiding any pitfalls.
First let’s start by downloading our Customer Import Template. Once it has downloaded please open in Excel, Libre’ or your editor of choice for .XLSX files. **The file is a .xslx file so it can be downloaded to you, please save as a .CSV file when you are ready to upload. We only can upload .CSV files for import.
Once you open the file you will see the first row has the column headers (in blue) and we have some sample data populated (light gray). Those can be deleted when you are comfortable.
Let’s cover the rules of the spreadsheet and importing:
The first and most important rule is to not change any of the existing column headers. These are mapped to specific places in ServiceWorks and if the names are changed, the data may not import.
When importing a spreadsheet we don’t allow for special characters in some columns. Many malicious hacking attempts begin with trying to upload/import files and special characters can be included in code in those cells. Some columns we allow certain characters, for example, as part of an address or email
Account Number: This column if left blank will default to your customer’s phone number, otherwise you can put any combination of letters and numbers into this field to become their account id when imported into ServiceWorks. Do not add an special characters and make sure there are no extra spaces in front of or behind the numbers.
Company Name: Not required. This column is allows alphanumeric entry.
Harry’s Lock & Key would be accepted
123 Coffee & Me would be accepted
Party #House would cause an error
The “&” character and the ” ‘ ” are allowed in this column. Please do not use other special characters. If a company name does happen to have a special character, please import without the character it can be added after the import
First Name: Not required. Self Explanatory, also be sure no extra spaces in front of or behind the name. This field is not mandatory, you can import a customer file with just a company name.
Last name: Not required. Self Explanatory, also be sure no extra spaces in front of or behind the name. This field is not mandatory, you can import a customer file with just a company name.
While each of the “Name” fields individually is not a required field, one of the fields must have data in order to save the customer profile.
Email Address: 2 characters are allowed in this field, the “@” and the “,”. If you need to add multiple emails in the email field you may separate the email addresses with a comma, but no additional space. For example; info@service.works,support@service.works,help@gmail.com
Phone 1: The phone number is formatted to accept only digits,
(555)555-5555 not correct format
555-555-5555 not correct format
555 555 5555 not correct format
5555555555 is the correct format
Phone 2: Same format rules as Phone 1
Address 1: 2 Characters are also allowed in this field “#” and “-” List only the house/business number and the street name. Apt, Suite or other types of secondary identification will be entered into Address 2.
Address 2: 2 Characters are also allowed in this field “#” and “-“. This column is used for things like Apt. #306 or Unit-72
City: Is not a necessary field. We pull the city based on the zip code you enter in the next column
Zip: Only one special character is allowed here the “-” can be entered if you know the Zip+4 code.
Column Pricing: Column Pricing can be set here for more information on column pricing check out the [insert column pricing blog here] Available values are shown in the graphic below.
Enable Credit Limit: In the customer profile there is a check box for enabling the customer’s credit limit. If N is entered in this column, the box will be left unchecked and the customer will have no credit available, IE., they will not be able to order anything “On Account” . If this column is marked as Y this check box will be checked and the customers credit limit is activated. If column is left blank, the default is Y
Credit Limit: Enter only digits in this column. $13,000 would be entered as 13000. while $13 would be 13. If left blank a 0 will be entered into the field. This gives the customer unlimited credit with your company.
Add Finance Charges: If finance charges are used, Enter Y in this column to check the “Add Finance Charges” box in the customer profile. If “No” is entered in the column, the box will remain unchecked. If a cell is left blank in the column it will default to N
Before the finance charge option can be used, you must activate this setting in your account before you import. Go to configuration settings > Invoice Settings > Invoice Charges. To read more about setting up your finance charges check out our tutorial How to Set Up Finance Charges
Tax Exempt: If a Y is entered into this column the tax exempt check box in the customer profile will be checked. Taxes that are setup in teh account would not be applied to that customer. If N is entered the box will remain unchecked and taxes would apply. These can be manually over ridden in the job by a check mark as well.
Customizing your customer import
You may have information about your customer that that we do not represent in the spreadsheet. With a little bit of setup, that is not a problem! Since we are importing customers, we need to create some Customer Custom Fields, that tutorial will walk you through creating the fields to hold your data.
Once you have your fields created, you can add a column to the spreadsheet, populate your desired data in the cells and you are ready to go.
Here you can see that 2 custom fields have been added in the ServiceWorks configuration settings.
And 2 columns with those titles have been added to the spread sheet
The blue color is decorative, not required for data import
Now, lets get to importing!
Navigate to the Customer menu and select “Customers”, then select the import icon
Now, a “Browse Csv” button is on the screen, click that to get started.
Once you “Find a File” window opens navigate to find the file you have specially prepared for this import. and click open.
The window will close and your file that you selected will be listed next to the Browse Csv button
Click “Upload” and the mapping page will display.
1. Lists the column headers from the file
2. Shows a sampling of the data just for reference
3. These drop downs are the fields that data will “map” to in ServiceWorks.
You can see from the example, ServiceWorks can do some smart matching and match most of those columns up for you. Always double check though to be sure!
At the bottom of your mapping page, you will see the custom fields that were entered earlier
Just remember to create the fields you need before you import.
When you are happy with the mapping, click the “Import” button
Congratulations – Your data import is underway!
Just a couple notes:
-If you are importing less than 50 customers that data import is going to happen as soon as you click import. And you will see them populate into the system pretty quickly
-If you are importing more than 50 customers, it will go into a queue to save on resources. This process runs every half hour, and then your customers will begin populating into your system.
-You will receive a report/email when the importing is done. If any of the rows in your file failed for some reason you will be notified in that report.
Our import sheet does not “create” any fields. All fields must exist in ServiceWorks before you import, otherwise your import could fail.
