vWork enables you to import a variety of different types of data using a CSV file as the data source. A CSV (comma-separated values) file is commonly used to save data in a structured table format. A CSV file looks much like a spreadsheet but without formatting. It can be created, edited, and viewed in most spreadsheet packages. Many software applications enable you to export and import data using a CSV file as the data repository. This makes it a useful format for transferring data between different types of software.
In vWork, we use CSV files to bulk import data for:
- Jobs
- Customer records
- Assets
- Pricebooks
- Hazards
You can also export the following data from vWork into a CSV file:
- Customer records
- Assets
- Pricebooks
In this article
This article explains how to prepare a CSV file prior to importing data in bulk into vWork. It is a companion article to our other articles that explain how to import data using a prepared CSV file. Here, we explain what a CSV file is and describe the fields and values that you need for each type of CSV file used in a bulk import in vWork.
Topics in this article include:
- Data import CSV file preparation tips
- How to save a spreadsheet as a CSV file
- How to prepare a job import CSV file
- How to prepare a customer record import CSV file
- How to prepare an asset import CSV file
- How to prepare a pricebook import CSV file
- How to prepare hazard import CSV file
You won't need to read this entire article; click the topic link above to go to the topic you are interested in. You can easily return to this topic list - just click this icon:
You'll find it at the end of each topic.
Related articles
This article explains how to prepare CSV files for import. If you want to find out how to import data from a CSV file please take a look at these articles:
- How to import jobs in bulk
- How to import and update customer information in bulk
- How to import assets in bulk
- How do I create and edit pricebooks?
- How to import hazards in bulk
Data import CSV file preparation tips
These tips apply to all data import CSV files. For additional tips specific to each type of data import CSV file please see the relevant topic later in this article.
A CSV file is formatted by rows and columns. Every row holds a single record (ie; a job, customer record, asset, pricebook line item, or hazard). Every column corresponds to a field in vWork.
Line limits
Large CSV files with many lines and columns can take a while to import. There can be a lot of processing required to transfer the data into vWork. For this reason, there are limits on the number of lines you can bring into vWork through a single CSV file. The limits depend on the complexity of the data you are importing and vary for different types of data. If you have a lot of data we recommend you divide your data into separate batch CSV files and perform several imports to import all your data.
The line limits for each type of CSV are as follows:
- Jobs are limited to 400 jobs per CSV file.
- Customer records are limited to 1000 customers per CSV file.
- Assets are limited to 2000 assets per CSV file.
- Pricebooks are limited to 2000 line items per pricebook.
- Hazards are limited to 5000 hazards per CSV file.
Give the columns headings
Headings help you identify the data in each column. We recommend that you label each column with a heading that corresponds to the data in the field. Our import tools let you specify if your CSV file has column headings. When you select the "My CSV has a header row" option during the CSV file import we exclude the heading row from the data import.
We recommend you use the same heading label as the labels used for each field in the import matching tool. In this article, we provide the field names and descriptions for every data value that you can import using a CSV file.
Exclude empty columns
If you export existing data from vWork to create a CSV file you may find the CSV file format has columns for fields that you don’t plan to use. While not essential, it can be helpful to remove columns for fields that are empty to make it easier to view and match your data.
Make sure the columns are sorted in the order used to match the fields in vWork
The columns from the CSV file correspond to the fields that you match when you import the data. It’s easier to match the data to the fields when the columns are in the same order as the vWork matching tool fields. In the following topics, we have listed each field type in a table in the order they show when matching fields during the import process. Use these tables to help you create the data import CSV files with the columns in the best order for matching.
Custom fields that use picklist values
When you include fields in a CSV file that use a value selected from a picklist (such as a picklist custom field or a hazard picklist field), the value entered in the CSV file field must exactly match one of the corresponding options given in the selected picklist. It is helpful to review the words, capitalization, spaces, and spelling used for the selected picklist options to make sure the value entered in the CSV file field is the same. For most picklists you can look at the picklists saved in your account by going to Settings>Picklists. For Hazards the picklists are found in Settings>Health & Safety>Settings. If a value other than the correct picklist value is saved in the CSV file field, the line information that includes the incorrect value won’t import into vWork.
How to save your spreadsheet as a CSV file
Most spreadsheet packages follow similar steps to create a CSV file. These instructions provide a general guide.
- Open the data file in your spreadsheet program.
- Remove all unnecessary formatting so that you have plain data (no formulas or special formatting) in the cells. It is okay (and helpful) to keep column headings.
- Go to the File menu and click Save As or Download or Export. (The options available for you to select vary by spreadsheet package). Below are examples for Google Sheets and Microsoft Excel 2013.
- You may see a message similar to the one pictured here. This confirms that only values are saved in the CSV file format, and formulas and special formatting are excluded.
Click Yes, OK, or Save to confirm you want to keep the CSV format and save your file.
How to prepare a job import CSV file
You can import new jobs in bulk into vWork using a CSV file. The article How to import jobs in bulk explains how to do this.
The following table describes the vWork job fields used in the matching tool when you bulk import jobs from a CSV file.
When you create your CSV file remember:
- Each line in the CSV file contains the information for a single job.
- Each column corresponds with a field in the job template.
Use this table to create the headings for each column in your CSV file and as a guide for formatting the data in each column. Most fields use text for the values. Where a format is required that isn’t text, we have described the format of the field value and provided you with an example. We recommend you only include the columns you need in the job import; you don't need to include a column for fields you are not using.
Field label/ |
Description |
Job 3rd Party ID |
An ID for a job that is created in another system. This must be unique or the job import will fail. If this is left blank vWork will display the vWork Job ID in the job after it is imported. |
Customer 3rd Party ID |
An ID for the customer. This may be the vWork customer ID or an ID from another source.
|
Customer Name |
The customer’s name populates the Customer Name field in the job. This also matches the Name field in the customer record.
Note: This isn’t the name of the Site Contact or the Billing Contact. |
Worker’s email address |
The email address for the mobile worker. 🔔Important: This field is required if you want to assign the job to the mobile worker at the time of the job import. If no email is included the job is created as unassigned. |
Start Time |
The scheduled start date and time for the job. 🔔Important: When there is no start time value in this field and you include workers' emails in the CSV file we default the start time to the time of the job import. If you are supplying a worker email, It is best to include a specific start time if you don’t want this to default to the time that the job data is imported. The format for this field is; |
Group Name |
The name of the Group the job belongs to. You don’t need this column in your CSV file if you are not using Groups in your account. If you are using groups and you do not supply a group name, the job will be created in the group you are in at the time of import. |
Asset ID |
The ID number saved with an asset in the asset's details. You don’t need this column in your CSV file if you are not using Assets in your account. |
Duration (Seconds) |
The duration of time a typical job is expected to take, shown in seconds, from the time it starts to the time the last step is completed on the mobile app. For example, 30 minutes is shown as 1800. |
Capacity |
Exclusively used with the Optimization feature this field is the loading capacity of a vehicle represented by a number. If you are not using optimization or capacity you don’t need this column in your CSV file. |
Health & Safety Tags |
The tag or tags required for a job. This field is only needed if you have the Health & Safety feature enabled for your account and you use tags. Where there is more than one tag required include a vertical slash between the tag labels in the field; eg., Heights|Elec Cert|First aid. |
Import as Draft (t/f) |
Use the value t if you want jobs to be imported in a Draft state, or f if you don’t. If this field is left blank jobs are not imported in Draft. They are either unassigned or assigned depending on if a worker’s email address is included in the CSV file. You don’t need this column if you don’t plan to have jobs in a Draft state. |
Steps |
You should have a column in your CSV file for every step in the job template that has a written descriptive address; eg., Level 10, 120 Albert Street, Auckland). Sort columns for steps in the same order as the steps in the job template to make it easier to match the fields during the import process. It is helpful if the column headings in your CSV match the label given to the step in the job template; eg., En route, Delivery, Finish. |
Step coordinates |
You should have a column in your CSV file for every geocoded step in the job template. Sort the columns for the step coordinates in the same order as the steps in the job template. This makes it easier to match the fields during the import process. It can be helpful to place these directly after each formatted step address column. You don’t need to include a step coordinate for steps that don’t have a geocoded address. It is helpful if the column headings for the step coordinates include the label given to the step in the job template. This makes it easier to match the step coordinates with the correct formatted step address; eg., Delivery coordinates. The data for these fields is the combined Lat/Lng coordinates of the geocoded step address. Coordinates should be entered using decimal degrees with latitude first, followed by a comma, then longitude; eg., -66.28201,110.52305 |
Custom fields |
You should have a column in your CSV file for every custom field in the job template that has data included in the field at the time the job is created. You don’t need columns for custom fields that will be completed as the job progresses. The values entered into the custom field columns must match the format of the values required for the custom field in the job. Take extra care where these are picklist values. You may not import photos or signatures via this method. |
Equipment: |
An ID for an Equipment item. This may be the vWork ID or an ID from another source. You can add as many pieces of equipment as needed for the job to the CSV file. Add a new column for each additional item of equipment. If you want to include the equipment Name you will also need an additional Name column to correspond with each additional 3rd Party ID column.
|
Equipment: Name |
The equipment Name matches the equipment Name field in the Equipment record. It also identifies the Equipment in the job.
|
Tips for preparing your job data for import
Address and location data
- If you want to geolocate a step in your job there are two important pieces of data you must include in the CSV file:
- an address for the step in one column, and
- the corresponding Latitude and Longitude to match the address in another column.
If you have several steps with geolocated steps, make sure you include separate columns for the address and latitude and longitude for each geolocated step.
- Make sure the Lat/Long coordinates are formatted as they are in this example;
-36.860144, 174.774982.
Note: there must be a comma between the Lat and Long coordinates. You can also include a single space to improve readability but this is not required. - To make things easier, label the columns for step fields and step coordinate fields in the CSV with the same name as the step in the job template; eg., Delivery address, Delivery coordinates.
Assign workers and start times
- If you want to assign each job to a worker in the CSV file make sure your CSV includes the worker's email address. The start time for the job will be the time the job was imported into vWork unless you also include a scheduled start time for the job in the CSV file.
- If you want to schedule a specific start time for each job make sure the CSV includes a start date and time. Use this format; YYYY-MM-DDTHH:MM where,
YYYY is the year; eg., 2021
MM is the month; eg., 09 for September
DD is the day of the month; eg., 12
T is T and represents time
HH is hour from the 24 hour clock; eg., 13 for 13 hundred or 1 pm
MM is for minutes; eg., 30 for 30 minutes past the hour.
This example looks like this: 2021-09-12T13:30
- To populate a date/time custom field you use a similar format,
YYYY-MM-DDTHH:MM:SS+12 (where +12 is the time adjustment from UTC to the local time zone.)
Duplicate records
- To reduce the chance of creating duplicate customer records make sure you are familiar with how vWork treats the 3rd Party Customer ID and Customer Name fields.
- To reduce the chance of creating duplicate Equipment records when importing jobs we recommend you include the vWork Equipment ID in the Equipment 3rd party ID field where possible. If you choose to only use the Equipment name in the CSV job import file then make sure the spelling, capitalization, and punctuation used for the name are identical to the name format used in the vWork Equipment record.
Including Equipment in jobs
- Remember to add a new 3rd Party ID column and Name column for each additional item of equipment you want to add to a job.
How to prepare a customer record import CSV file
You can import customer records in bulk into vWork using a CSV file. The article How to import and update customer information in bulk explains how to do this.
This table describes the vWork customer record fields used in the matching tool when you bulk import your customer data into vWork from a CSV file.
When you create your CSV file remember,
- Each line in the CSV file contains the information for a single customer record.
- Each column corresponds with a field in the customer record.
Use this table to create the headings for each column in your CSV file and as a guide for formatting the data in each column. Most fields use text as values. Where a format is required that isn’t text, we have described the format of the field value and provided you with an example.
Field label/ |
Description |
Customer name |
The customer’s name populates the Name field in the Customer Record. |
Customer third-party ID |
A unique ID for the customer that may have been generated by another software package. This populates the ID field in the customer record. If left blank vWork inserts a unique vWork ID for the customer. This is alphanumeric and can include spaces and symbols. |
Customer notes |
Notes specific to the customer. In addition to the customer record, these notes show in the mobile app in the customer information for a job. |
Delivery contact first name |
This populates the Site Contact’s first name in the customer record. |
Delivery contact last name |
This populates the Site Contact’s last name in the customer record. |
Delivery contact phone |
This populates the Site Contact’s phone number in the customer record. Use your preferred landline display format. |
Delivery contact mobile |
This populates the Site Contact’s mobile number. The format must include the country and prefix code without the + symbol and with no spaces. Mobile numbers will look similar to these examples: 440791112356, 61411323456, 6421345678. |
Delivery contact email |
This populates the Site Contact’s email address in the customer record. |
Delivery contact fax |
This populates the Site Contact’s fax number in the customer record. Use your preferred display format. |
Delivery contact formatted address |
This populates the Site Contact’s address. Use your preferred display format. This could be an address or a description of a location; eg., Level 4, Tower A, Hometown Business Park, Cronulla, Sydney. |
Delivery contact lat |
This is the latitude in decimal degrees for the Site Address. This is used with the Delivery Contact lng field to geocode the site address. Once geocoded with the latitude and longitude coordinates, the Site Address in the customer record shows a green geolocated marker to indicate the site address can be displayed on a map. |
Delivery contact lng |
This is the longitude in decimal degrees for the Site Address. This is used with the Delivery Contact lat field to geocode the site address. Once geocoded with the latitude and longitude coordinates, the Site Address in the customer record shows a green geolocated marker to indicate the site address can be displayed on a map. |
Billing contact first name |
The customer billing contact’s first name |
Billing contact last name |
The customer billing contact’s last name |
Billing contact phone |
The customer billing contact’s landline. Use your preferred display format. |
Billing contact mobile |
This populates the customer billing contact’s mobile number. The format must include the country and prefix code without the + symbol and with no spaces. Mobile numbers will look similar to these examples: 440791112356, 61411323456, 6421345678. |
Billing contact fax |
The customer billing contact’s fax number. Use your preferred display format. |
Billing contact formatted address |
The customer billing contact’s Billing Address. Use your preferred display format. This could be an address or a description of a location; eg., PO Box 000, North Sydney, NSW 2060 |
Billing contact lat |
This is the latitude in decimal degrees for the Billing Address. This is used with the Billing contact lng field to geocode the billing address. Once geocoded with the latitude and longitude coordinates, the Billing Address in the customer record shows a green geolocated marker to indicate the billing address can be displayed on a map. |
Biling contact lng |
This is the longitude in decimal degrees for the Billing Address. This is used with the Billing contact lat field to geocode the billing address. Once geocoded with the latitude and longitude coordinates, the Billing Address in the customer record shows a green geolocated marker to indicate the billing address can be displayed on a map. |
Pricebook id |
The ID that is created for a pricebook when it is imported. This is found in the Pricebook table; go to Settings>Finance>Pricebook. This is an optional feature that may not be enabled in your account. |
How to prepare an asset import CSV file
You can import your assets into vWork in bulk using a CSV file. The article How to import assets in bulk explains how to do this.
This table describes the vWork asset fields used in the matching tool when you bulk import your asset data into vWork using a CSV file.
Note: This is not the same as the CSV file used to import assets into Repeating Jobs v2. You must have your assets in vWork before you can import them into Repeating Jobs v2. This CSV file is used to bulk import assets into vWork.
When you create your CSV file remember that,
- Each line in the CSV file contains the information for a single asset.
- Each column corresponds with a field in the asset.
Use this table to create the headings for each column in your CSV file and as a guide for formatting the data in each column. Most fields use text as values. Where a format is required that isn’t text we have described the format of the field value and provided you with an example.
Field label/ |
Description |
Asset Third Party ID |
This populates the ID field for the Asset. It is the unique ID used to identify an Asset. You can use your own, one generated by another software package, or, if you leave this field blank, vWork will generate an ID for the asset. |
Customer Name |
The name of the customer who owns the asset. This is sometimes called the Owner Name. |
Name |
The name of the asset. |
Model |
Use this to help identify the Asset. It might be a model name or something similar. This shows in the Asset List. The data can take any format that works for you; eg., the model type of a vending machine, the floor level that a customer bathroom is located on, or the name of a sports field in a multiple sports facility. |
Serial |
Use this to provide more detail about the Asset; eg., a unique serial number on a water cooler, or a location like the east wall of a building. The data can take any format that works for you. |
Notes |
Useful information about the Asset. |
Cost |
Use this to put a value on the Asset or show how much a service costs. This value is entered as a numerical value up to 2 decimal places; eg., 47.65 |
Latitude |
This is the latitude, in decimal degrees, for the asset’s address. This is used with the Longitude field to geocode the asset’s address. Once geocoded with the latitude and longitude coordinates the asset’s Address in the asset’s record shows a green geolocated marker to indicate the asset’s address can be displayed on a map. |
Longitude |
This is the longitude, in decimal degrees, for the asset’s address. This is used with the Latitude field to geocode the asset’s address. Once geocoded with the latitude and longitude coordinates the asset’s Address in the asset’s record shows a green geolocated marker to indicate the asset’s address can be displayed on a map. |
Address |
This populates the Address field in the asset record. Enter this in your preferred display format. It could be an address or a description of a location; eg., Level 4, Tower A, Hometown Business Park, Cronulla, Sydney. |
Enabled |
Enter "True" under this heading if the asset is enabled. When an asset is enabled it can be assigned to a job. If it is not enabled it won’t be listed for selection to add to a job or job template. |
Group Name |
The name of the Group the Asset belongs to if you have Groups enabled in your account. This should match the group name as it is used in vWork. |
Hazard ID |
The ID assigned to a Hazard in vWork (if you have Health & Safety enabled in your account). This is a numerical value with 4 numbers. It must match the Hazard ID as it is shown in vWork; eg., 1110. You can find the Hazard ID under My Hazards - go to Settings>Health & Safety>Hazards. |
Tag IDs |
The ID assigned to a Tag in vWork (if you have Tags enabled in your account). This is a numerical value with 4 numbers. It must match the Tag ID as it is shown in vWork; eg., 1110. You can find the Tag ID under My Tags- go to Settings>Health & Safety>Tags. |
Optimization Order |
The order number for the Asset is used to specify how an optimization arranges the order of jobs that relate to the Asset. This works with Optimization Grouping. For more information on this feature see the article, Use Optimization with Assets to schedule several jobs at the same geocoded address. |
Optimization Grouping |
The category you assign the Asset when using Optimization Ordering. This works with the Optimization Order number given in the Optimization Order field. |
Tips for preparing your asset data for import
- When you import an asset that belongs to a group the group name must match the group name exactly as it is shown in vWork (watch for capital letters and spaces).
- When you import hazards and/or tags with an asset you must use the Hazard ID or Tag ID from vWork. The Hazards and Tags must already exist in vWork before you can import them with an asset.
- When importing multiple hazards, tags, or groups with assets in a CSV file you must separate the ID's with a | symbol ("Shift+\" on your keyboard).
For example, to import tag IDs 123, 124, and 456 into one asset, your CSV cell would look like: 123|124|456.
This is the same as importing multiple hazards and groups.
How to prepare a pricebook import CSV file
To save a pricebook into vWork you first need to create the pricebook as a CSV file. The article How do I create and edit pricebooks? explains the process of adding pricebooks to vWork. Here we describe what information to include in the CSV file used to import the Pricebook data.
A pricebook contains three pieces of data for each line item. These are the Code, Description, and Price.
When you set up the pricebook CSV file remember that:
- You only need three columns of data; one each for the Code, Description, and Price.
- It is helpful to label the columns with headings. This makes it easier to match the data to the fields in vWork.
- Each line in the pricebook CSV file is a single line item in the pricebook.
This table describes the three fields used in the pricebook.
Field label/ |
Description |
Code |
A short unique code that is the main identifier for the line item. When you export a pricebook to update details for line items you can overwrite any of the existing information for line items provided you keep the code unchanged. If you change the code you will create a new line item in the pricebook. |
Description |
A label or brief description for the line item. |
Price |
The price of the item as a number. This can have up to 2 decimal places. Don’t include a currency symbol in this field. (eg: 123.45) |
How to prepare a Hazard import CSV file
You can bulk import your hazards into vWork using a CSV file. The article How to import hazards in bulk explains how to import the hazards that you will often apply to jobs into vWork. Here we explain what information you need to include in the CSV file used to import Hazard information.
Risk Picklist and Control Picklist options
When completing hazard information on the job, the Initial Risk, Control Type, and Residual Risk fields are completed by choosing one of a selection of options from a picklist. Selected options from the picklists are the only values allowed in these fields.
When you import hazards into the Hazards library you add the picklist values to the hazard fields that you want to pre-populate in the field for a hazard when the hazard is added to a job. Workers won't need to complete these fields in the mobile app unless they want to change a picklist value in the field. Fields that don't have a pre-populated picklist value don't need to be imported with the hazard; a worker will select from the picklist to complete these fields in the mobile app when they perform the job.
If you want any of these fields to pre-populate a picklist value at the time the hazard is added to the job, then:
- Make sure the picklist value is saved in the CSV file.
- Be sure to review the words, capitalization, spaces, and spelling used for the Control picklist and Risk picklist items in the Health & Safety settings (go to Settings>Health & Safety>Settings) at the time you set up your CSV file. The value used in the CSV file field must exactly match the corresponding option shown on the picklist it comes from. If anything other than the picklist values are saved in these fields the hazards won’t import into vWork.
The table below is an example of a CSV file used to import hazards. When added to a job these hazards will show the Initial Risk and Control type required to mitigate the risk as completed values in the field. The values saved in these fields correspond to the Risk Picklist and Control Picklist values saved in this account’s Health & Safety settings. These picklists are shown below the table.
Hazard CSV fields
A hazard import CSV file can have six fields for each Hazard. These are Description, Initial Risk, Control Type, Controls Required, Person Responsible, and Residual Risk.
When you set up the hazard import CSV file:
- You must include the Description field as this is the field used to identify the hazard when importing the hazard. (Once imported the hazard will have a unique ID generated by vWork).
- You only need to include columns for the fields that you want to show a value in when the hazard is added to a job. (Although it doesn’t matter if you include all the fields as columns in the CSV file - just make sure the fields are empty if you don’t want anything to show in the field for the hazard at the time it is added to a job.)
- It is helpful to label the columns with the field names to make it easier to match the CSV file fields to the vWork hazard fields.
Field label/ |
Description |
Description |
A short description of the hazard. |
Initial Risk |
The level of risk assigned to the hazard before control measures are put in place to manage the risk. Data saved in this field must match the options saved in the Risk picklist. You can see the options saved in the Risk picklist in: Settings>Health & Safety>Settings. |
Control type |
The control measures that are used to mitigate the risk created by the hazard. Data saved in this field must match the options saved in the Control picklist. You can see the options saved in the Control picklist in: Settings>Health & Safety>Settings. 🔔Important: Make sure the capitals, lowercase letters, and spelling match identically with the options in the Control picklist to avoid errors in the CSV import process. |
Controls required |
A more detailed description of how the Control Type selected from the picklist is used to mitigate the risk. For example, if the Control Type selected was Use PPE, the Controls required might be to wear a face shield, N95 respirator, nitrile disposable gloves, and disposable hooded hazmat overalls. |
Person Responsible |
The title or name of the person who is responsible for implementing control measures to reduce the risk. |
Residual Risk |
The remaining risk level after all Control measures are applied to mitigate the risk. This is selected from the same picklist that is used to establish the Initial Risk. |
Note: The information you save in the fields for a hazard can be edited in the job editor if this is required.