This feature is only available for PRO Connect or Work Subscribers.
We offer the ability for your company's users with Administrator privileges to import your existing customer, location, and job data via the ADMIN > Import Data tab.
Please also note that we'd be happy to assist with the import of your data! You may provide us with your data in either Excel or CSV format.
First some background information:
Our system is built upon a hierarchy of Customers > Locations > Jobs. Your customer must have one or more physical locations, and each location can have an unlimited number of jobs. Every job must be associated to a specific location for a specific customer, meaning that your customer must exist before its locations can be imported, and a location must exist before its jobs can be imported. As such, please first import your customer(s), then your location(s) followed by your job(s).
There are sample import files attached to this article for each file type. Using our sample files and overwriting the sample data with your own (vs. creating your import file from scratch) is the best way of ensuring that your import is successful.
If any records fail or are skipped after importing, check the 'Message' column to identify the cause. After correcting the errors, you can resubmit your spreadsheet to complete the import. Tip: To speed up processing, we recommend removing successfully imported rows and uploading a new file containing only the corrected records.
- Your import file must "match" our sample/template in that it must include a header row and all fields (columns) must be present and have the exact same labels. Do not make any changes to the column headers (top row) in the sample/template file.
- Fields marked in red in our template are required to have values while data is optional for all fields that are not red
- The required field "customerid" (customer, location and job files) is your internal unique identifier for the customer. That is, each of your customers must have a unique value for customerid.
- The required field "locationid" (location and job files) is your internal unique identifier for the specific physical location for the customer. That is, each location must have a unique value for locationid.
- The required field "jobnumber" (job file) is your internal unique identifier for the job. That is, each job must have a unique value for jobnumber.
- State/province values must be provided as their official 2 character abbreviations
- Zip/postal codes can be provided as 5 digits (including leading zeros as necessary) for US and proper "XXX XXX" format for Canada. Providing a "ZIP +4 Code" digit area code in the zipext column allows you to set a more precise location.
- Country must be provided as either "US" for United States or "CA" for Canada
- All phone and fax values must be provided in the format "xxx-xxx-xxxx"
- All dates must be provided as "mm/dd/yyyy"
- The fields "timezone" (customer, location and job files), "stage" and "jobtype" (job file) all have a limited number of possible values as described in our import specification below
- If a leademail is provided (job file), we will check for a matching user account. If no match is found we will not set a project lead
- If a salesrep is provided (job file), we will check for a matching user account. If no match is found we will not set a salesrep
Available fields for import, with required fields noted in bold and by *:
Customers
customerid* (your internal unique identifier for your customer)
customername *
address
address2
city
state (provided as official 2 character abbreviations)
country (provided as US or CA)
zip
zipext
createLocation (1 - yes, 0 - no. If 1 or "yes" a new location will automatically be created for the customer using the customer address)
timezone (valid entries are Eastern, Central, Mountain, Mountain no DST, Pacific, Alaska, Hawaii, Hawaii no DST)
phone (if provided must be in the format xxx-xxx-xxxx)
fax (if provided must be in the format xxx-xxx-xxxx)
contactfirstname
contactlastname
contactphone (if provided must be in the format xxx-xxx-xxxx)
contactphoneext
contactmobilephone (if provided must be in the format xxx-xxx-xxxx)
contactemail
contact2firstname
contact2lastname
contact2phone (if provided must be in the format xxx-xxx-xxxx)
contact2phoneext
contact2mobilephone (if provided must be in the format xxx-xxx-xxxx)
contact2email
website
notes
taxExempt (Is this customer tax exempt 1 or 0)
Locations
locationid * (your internal unique identifier for this location)
customerid * (your internal unique identifier for this location's customer)
locationname *
address
address2
city
state (provided as official 2 character abbreviations)
country (provided as US or CA)
zip
zipext
timezone (valid entries are Eastern, Central, Mountain, Mountain no DST, Pacific, Alaska, Hawaii, Hawaii no DST)
locationnumber (an allowance for an internal "friendly" location identifier unique to this customer)
phone (if provided must be in the format xxx-xxx-xxxx)
fax (if provided must be in the format xxx-xxx-xxxx)
contacttitle
contactfirstname
contactlastname
contactphone (if provided must be in the format xxx-xxx-xxxx)
contactmobilephone (if provided must be in the format xxx-xxx-xxxx)
contactemail
website
notes
Jobs
customerid* (your internal unique identifier for this job's customer)
locationid * (your internal unique identifier for this job's location)
jobnumber * (your internal unique identifier for this job)
title*
leademail (email address of the project lead - must match that of an existing user)
type (valid values are those that you've established here: ADMIN > Settings & Preferences > Stage & Type Values)
stage (valid values are: those that you've established here: ADMIN > Settings & Preferences > Stage & Type Values)
jobid (an allowance for an internal "friendly" job identifier unique to this customer)
orderdate (if provided must be in the format mm/dd/yyyy)
duedate (if provided must be in the format mm/dd/yyyy)
description
invoicenumber
ponumber
salesrep (must match the full name of a sales rep that has already been defined in your PRO/Work instance)
gccompany (allowance for a job general vendor company name)
gcfirstname
gclastname
gcemail
gcphone (if provided must be in the format xxx-xxx-xxxx)
gcmobilephone (if provided must be in the format xxx-xxx-xxxx)
gctimezone (Eastern, Central, Mountain, Mountain no DST, Pacific, Alaska, Hawaii, Hawaii no DST)
Items
category* (the categoy for the specific item)
item * (must be unique)
internal_label
description
quote_text
print_on_quote (requires 0 or 1)
taxable (requires 0 or 1)
sku
type * (one of the following; HXW, 1Dimension, Hourly Rate, Quanity, Markup.)
markup
unitcost
minprice
priceprotected (requires 0 or 1)