When using vWork in combination with Xero, you will want vWork line item codes to match your Xero item codes so that they are displayed correctly on your invoices. For this to work properly, the codes must match exactly. This can be done manually on every invoice but, to avoid errors, you should use vWork Pricebooks. There are two ways to easily achieve this:
- Copy your Pricebooks from vWork into Xero Items, or
- Copy your Items from Xero into vWork Pricebooks.
Choose which option you want to use before proceeding.
In this article
This article explains how to create a CSV file that maps vWork Pricebook line items correctly with Xero items. This can be done by copying Xero Items into vWork, or by copying vWork Pricebooks into Xero Items.
- How to share line item data between vWork and Xero
- Option 1: Copy Xero Items into vWork Pricebooks
- Option 2: Copy vWork Pricebooks into Xero Items
We recommend that you read the first topic to understand the relationship between vWork Pricebook fields and Xero item fields. However, you can also go directly to a topic you are interested in by clicking a topic from the list above. You can easily return to this topic by clicking this icon.
How to share line item data between vWork and Xero
You may want to map item code, description, and unit price from Xero to a vWork Pricebook or use vWork as the source and map this same data to Xero. Whatever source you begin with it is important to know what data is shared between vWork Pricebooks and Xero items to enable you to successfully map this shared information. When the information is mapped correctly the Xero invoice will show the same information that is in the job invoice created in vWork when a job is completed.
A key difference between vWork and Xero invoice line items is that what vWork labels as a Code in the line item, Xero labels as an Item. Xero also has several field options for saving a description and unit price. It is important to map the correct options for these with the vWork Pricebook.
vWork: Pricebook CSV file
Pricebooks are added to and exported from vWork using a CSV file. The CSV file format used has 3 columns of data (one for each Pricebook field) labeled Code, Description, and Price. Each line in the CSV file is a line item in the Pricebook. The CSV file looks like this in a spreadsheet format.
Note: you can find out more about how to use a CSV file to add a Pricebook to vWork in the article How do I create and edit pricebooks?
Xero: Items CSV file
Xero uses more fields and captures more data for line items in the CSV file than we use in a vWork Pricebook. This example of a Xero Items CSV file, in a spreadsheet, shows the same data from the vWork Pricebook above. This is the CSV file format used to import item data into Xero.
Note: When you export Xero items in a CSV file you may have data in more columns than we show in this example.
Xero: Items CSV file column headings
This table lists the column headings, in order, required in the Xero Items CSV file spreadsheet. When you upload data to Xero, you must have all these columns in the CSV file even if you don’t have data to include in the columns.
Xero: Items CSV file column headings
💡Tip: If you are logged in to Xero you can download the template for creating the Xero CSV file. Open Xero and go to Business > Products and services > Import > Items.
Shared CSV file data
The columns in the Xero Items CSV file that contain the data from the vWork Pricebook are:
- ItemCode this maps to code in the vWork Pricebook CSV file.
- PurchasesDescription this maps to description in the vWork Pricebook CSV file.
- SalesUnitPrice this maps to price in the vWork Pricebook CSV file.
CSV file data housekeeping
In order for Xero to successfully map data with vWork Pricebook line items you make sure the use of spelling, numbers, punctuation, and spaces is identical in the data for both the Pricebook and the Xero CSV files.
In addition, when setting up the Xero Items CSV file you must:
- Use the column headings in the Xero Items CSV file exactly as shown in the Xero Items CSV file template. Column headings don’t have spaces between words and each new word has a capital letter; eg. SalesUnitPrice.
- Include all Xero Item columns in the Xero Items CSV file - even when there is no data to save in those columns. You can download the template for this CSV file from Xero.
- Limit line items to 1000 in the Xero Items CSV file. If you have more than 1000 line items you can break the CSV file into batches using several CSV files to capture your data.
And, when setting up the Pricebook CSV file you must:
- Only include the columns in the Pricebook CSV file that you need to create the Pricebook in vWork if you are mapping fields from Xero. This will mean you will need to remove some of the Xero columns when creating the Pricebook CSV file.
- Limit line items to 2000 in the vWork Pricebook CSV file. vWork Pricebooks can’t have more than 2000 line items.
What does a vWork invoice look like in Xero when Pricebook data is correctly mapped?
The example below shows an invoice created by a job in vWork and how this translates to an invoice in Xero when the Pricebook fields are correctly mapped to the Xero Items fields.
Notice that the Code from vWork goes into the Items in Xero. The Description and Unit Price from the Pricebook in vWork go in the corresponding Description and Unit Price in the Xero Invoice.
Option 1: Copy Xero Items into vWork Pricebooks
You can use CSV files to export items that already exist in Xero and save these in a Pricebook in vWork.
To export Xero line items and create a vWork Pricebook you export the items from Xero into a CSV file and then edit the CSV file to change it to the required vWork Pricebook CSV file format.
To copy Items from Xero into vWork:
- Open Xero and go to Business > Products and Serivces.
- Click the Export button and select CSV to download the CSV file.
- Open the CSV file in a spreadsheet package then:
- edit the Pricebook CSV file to match the requirements for a Pricebook CSV file. This requires you to delete some columns and change the column headings. Follow the guidelines in the topic, How to share data between vWork and Xero, above.
- Once you have the file correctly formatted, you need to import it into vWork. The process for importing is explained in the article, How do I create and edit Pricebooks?
Option 2: Copy vWork Pricebooks into Xero Items
When you want to import vWork Pricebooks into Xero as Items you first export the Pricebook into a CSV file then edit the data and format to match the CSV file format required by Xero to import Items.
To copy Pricebooks from vWork into Xero:
- Export the Pricebook from vWork.
- Go to Settings >Finance >Pricebooks and open the Pricebook you want to export.
- Click the Export Pricebook to CSV button at the bottom of the page. The pricebook is exported as a CSV file and saved on your computer.
- Open the Pricebook CSV file in a spreadsheet and create a Xero Items CSV file using the exported Pricebook data. Follow the guidelines given in the topic above to set up the CSV file. This article provides information on how to create a CSV file: Prepare a CSV file to import data into vWork.
Note: At Step 5 of this process you have the option to download a template to use as the foundation to create the Xero Items CSV file. You can create the CSV file now, manually, or at Step 5 using the Xero CSV file template.
- Open Xero and go to Business > Products and Services.
- Click the Import button then select Items.
- (Optional step - see the note at Step 2 above). Click Download Template to download the CSV file Inventory Items template, then save the Pricebook data into the template.
💡Tip: If you want the description to show next to the Item Code in Xero in the Items list (when logged in to Xero go to Business > Products and Services to see this list) you can add the description data from the Pricebook in the Item Name column in addition to the PurchasesDescription column (the same data is entered twice). This makes no change to what you see on the invoice but can make it easier to understand what the items are when viewing them in Xero.
- Upload the Xero Items CSV file. Click Select File and browse your network for the Xero Items CSV file. Click to select it and start the upload.
- The file shows in the Upload Items area when the fields are matched and the data is uploaded and ready for import into Xero. Click Next to begin the data import.
- A message displays asking you to confirm the number of line items that you are importing into Xero from the CSV file. Click Complete Import to import all line items.
- The items are saved in the Products and services list in Xero.