Items

Xero:Extract/Amend/Import Product Items

PostTrans imports Items, which could be manually entered with the assistance of in-cell searching, using copy and paste, imported from CSV file or extracted from Xero using the Extract button.

Page Contents

Import Product Item Features in Xero

Simply add new lines to the spreadsheet from row 4 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Xero Product Item (Examples shown below).

Advantages

• Xero Product Items Import Example
• Demo Videos
• Template Structure in detail
• Common Features
• Template Tags
• Related Example Templates included with PostTrans

Related Pages

• Posting a Manual Journal
• Posting a Sales/Purchase Transactions
• Sync Product Items to Multiple Companies

Import Product Items Features in Xero

Simply add new lines to the spreadsheet from row 4 on-wards. Then press Import button, on the PostTrans Toolbar, to import single or multiple Product Items into Xero(Examples shown below).

Advantages

• Visual Validation of data as processed highlighting an error
• Multi Company updates to sync Multi Company Chart of accounts
• Search for Account, Stock, Project, Nominal, using our In-Cell Searching
• Add formula, vba or macros to manipulate the imported data
• Press Extract to populate the sheet with existing data, amend and Import
• Easy importation from CSV file
• Double click on Product Items code to view Item in Xero
• Extract Product Items given code, or those change in the last x days

Examples of Importing Xero Product Items

• Visual Validation of data as processed highlighting an error
• Multi Company updates to sync Multi Company Chart of accounts
• Search for Account, Stock, Project, Nominal, using our In-Cell Searching
• Add formula, vba or macros to manipulate the imported data
• Press Extract to populate the sheet with existing data, amend and Import
• Easy importation from CSV file
• Double click on Account code to view Chart of accounts in Xero

Examples of Importing Xero Product Items

Demonstration Video

Template Structure for Static Data

The template must have tag ‘xeroItem’ in cell A1 to instruct PostTrans that we are interested in Xero Items, and that the sheet is a PostTrans Template.  Pressing Import or Extract on a blank sheet will either open an existing example or create basic template for you to customise.

Row 1 contains a series of tags, which denote to PostTrans the data to be read/written in each column. These tags, and data columns can be rearranged to suit your needs, and the tags are added using the Tags button. See on-line Training   Deleting the Tag name, in the cell, from this row removes the Xero mapping.   Table of Items Tags

Hovering over these tags, in row 1, will show a comment which explains the tags purpose, data type and any other useful details.

Common Features

In-Cell Searching

Any Tag ending with ^ with have a in-cell search facility to look up associated values.  Use SPACE + TAB to see all values, or SPACE and TEXT to perform a text search on description. These are returned to the cell as "CODE, DESCRIPTION".  PostTrans only uses the CODE and ignores the comma and anything after it, thus data from a field would only need the CODE part of the data if from an external imported file.  

In-cell Searching can be turned off by removing the ^ from the end of the tag name, or name in first line of comment (Header cells) to disable in-cell searching for a single column, or header cell.  All in-cell searching can be turned off in Setup.  In-cell searching.

Xero Data Cache

All associated Account, Tax, Currency, Contact and Product Item data needed to validate, or provide In-Cell Searching, is cached locally on you PC to enable PostTrans to deliver Optimal performance. This cache can be tailored to you own particular needs using the Cache button the tool bar.

Extracting the Data

The data extracted using the Extract button can be filtered, by entering criteria in row 2. Hover over cell A2 for instructions. Extracting Data

Amending Items Data

Having extracted data, the user can now simply edit the Items in Microsoft Excel, before re-importing it back into Xero. 

PostTrans uses the tagged column ItCRC to detect changes in rows.  Therefore, only records/rows which have changed since extraction are updated.  If you add or delete a tagged column, PostTrans will treat ALL rows as having changed, and thus ALL records will be updated on next Import.

Importing the Data

Simply press the Import button to validate and import the data into Xero.  PostTrans will read the Header cells and then process the rows of data until ItCode column contains no data. The Xero Company imported into can be set in the Setup window.

PostTrans ONLY edits those fields listed on the sheet when importing, thus any existing values, on a record, are unaffected.   New records may have default values for certain fields.

File Import Button

This can be enabled in Setup to easily import cvs, or fixed length files, into sheet for importation.   Formula can also be added, or macros/VBA to manipulate this data before importation.   Import from CSV file.

Validation

Any values and associated codes will be invalid during import.   The cursor will be moved to highlight any problems in data.

Evaluation and License

This functionality will work with Xero Demo data without a license. A license is required to import into any other Xero Company.  Pricing

What else can I do?

Other Xero Extract/Import types

Frequently Asked Questions?

Got another question? It may well be covered on our Frequently Asked Questions page.

Other Useful Topics

Clear Transaction and copy down formula

PostTrans Tags which map to Xero

Not all these tags/columns are needed. The table below lists the required and recommended:

Tag Name	Data Type	Description
Important Fields:
xeroItem	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
ItCRC	Text Req	Contains CRC number to help PostTrans detect changes in current row.
Special:
ItDelete	Text 1	Delete record if "X"
ItFindCode^	Text 8	Search for record by entering code or pasting list, at top of column, and pressing Extract button     In Cell Searching values in table ITEM
Account Details:
ItCode	Text 30 EOF Req	Stock/Product Item code (Double Click to view Product Item in Xero)
ItName	Text 50 Req	Name
ItTrackItem	Yes/NoTF Req	YES Track Item - Hold stock Quanities
Purchase Details:
ItPurThis	Yes/NoTF Req	YES if we Purchase this- Set if ItPuUnitPrice,ItPuAccNo,ItPuCOGSAccNo,ItPuTaxCode
ItPurUnitPrice	Double Req	Purchase Unit Price
ItPurAccNo^	Text 10 Req	Nominal Acocunt For Purchase/Cost of Goods Sold     In Cell Searching values in table ACCCODEEXPENSE
ItInventoryAccNo^	Text 10 Req	Nominal Acocunt For Purchase     In Cell Searching values in table ACCCODE, INVENTORY
ItPurTaxCode^	Text 100 Req	Purchase Tax Code. Tax Code can be Xero Code or Tax name e.g. INPUT2 or 20% (VAT on Expenses)     In Cell Searching values in table TAXCODEPURCH
ItPurDesc	Text 4000 Req	Account Address Line 2
Sales Details:
ItSalThis	Yes/NoTF Req	YES if we Sell this - Set if ItSaUnitPrice,ItSaAccNo,ItSaTaxCode,ItSaDesc
ItSalUnitPrice	Text 60 Req	Sales Unit Price
ItSalAccNo^	Text 10 Req	Sales nominal Accoutn code     In Cell Searching values in table ACCCODEREVENUE
ItSalTaxCode^	Text 100 Req	Sales Tax Code can be Xero Code or Tax name e.g. OUTPUT2 or 20% (VAT on Income)     In Cell Searching values in table TAXCODESALES
ItSalDesc	Text 4000 Req	Account Address Line 2

Req = Field is required    EOF = End of file, Stop when no more values    

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.

To use the template, with Sage 50, install PostTrans. Download
Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price
Watch the On-line Training course on how to use and customise the templates.

Download xeroStatic.xlsx
	Static Data - Product Items