Menu

Transactions

Xero:Import Transactions

This type of PostTrans import, imports Sales/Purchase Transactions, which can be input manually, data from an ODBC connection, copy and paste or imported from CSV file.

Page Contents

Use PostTrans to enable Microsoft Excel to Extract and Import transactions, Contacts, Items and Account records into Xero visually from within Excel. The data can be brought in from manual input, ODBC connection, copy and paste or

imported from CSV file.

You also have the power of Microsoft Excel to manipulate the data by adding formula, macros or VBA to further enhance data before import to Xero.

See below for more information on specific ares, all with demo videos:

Xero: Import Sales/Purchase Quotes, Invoices and Purchase OrdersNominal JournalXero Sales Recipts and allocate to Invoices in one or more companies

Xero: Import Xero Contact, Account and Items into one or many companies.

Online Training fro Xero: For Xero we have a series of comprehensive PostTrans Training Videos for Xero

Trial: PostTrans can be easily evaluated with Xero Demonstration data. Download for Xero

Prices: Prices for Xero

Common Features

In-Cell Searching

Any Tag ending with ^ will 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 SetupIn-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.

Cursor Path

The path the cursor takes through the sheet, when entering data manually, can also be easily defined in Setup.  See Set Cursor Path

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 TLDesc 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.

Validation

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

POSTED Document Ref

After Posting a transaction, PostTrans will write "POSTED:" and the transaction reference number in column A (tag TLPosted).  Lines marked as "POSTED" are then ignored by PostTrans when re-posting. So, transactions cannot easily be posted twice. So the transaction cannot easily be posted twice.   The user would have to remove "POSTED" from column A in order to post the transaction a second time.  The user would have to remove "POSTED" from column A, to post the transaction a second time.

Confirmation before Posting Transaction

Just before posting the validated transaction, PostTrans will optionally show a transaction summary for the user to validate.   This can be turned off in Setup window.

File Import Button

This can be enabled in Setup to easily import CSV, 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.

Clear Button

The Clear button will clear all values on the sheet, according to the row just above the tag row, and copy down any formula.  Clear Transaction and copy down formula

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. 
A subscription be purchased, and cancel on-line at any time very easily.   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.

PostTrans Tags which map to Xero

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

Tag Name

Data Type

Description

Important Fields:

TLPosted

Text Req

Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row (Double Click to view Xero Transactions)

TLCRC

Text

When posting multiples a code is written to this column, it this then used to detect changes

Lines:

TLItem^

Text 100

Item/Product code. (Double Click to view Product Item in Xero)
    In Cell Searching values in table ITEMNODESC

TLDesc

Text 3000 EOF

Product Description. PostTrans stops importing after finding 5 consecutive blank cells.

TLQty^

Double

Quantity, if Service Transaction then can be omitted, and lines with TLNett will have Qty 1. PRO – For Product Transactions - If switch `ignore ZERO qty` ignores any lines with zero qty. This allows a long product list to be listed, and user simply edit a qty to book IN/OUT
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLUnitPrice^

Currency

Unit Price for each item for Exclusive of Tax. . If cell is blank then PostTrans will lookup customer price on change of Qty, or adding a product. If option is on to override the price, in System setup >> Lookups >> Switches, then the customers price will overwrite any value already in cell, on change of Qty. Price will be after customer discount. PRO - If multi-currency is ON will be in account currency.
Unit Price
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Tax:

TLTaxCode^

Text 100

Tax Code. Will fill in default if blank. Can be Xero Code or Tax name e.g. OUTPUT2 or 20% (VAT on Income)
Default Tax System Exclusive/Inclusive
    In Cell Searching values in table TAXCODE-TRANS_TLTAXCODE

TLTaxManAmt

Currency

Tax Amount, per item, if calculated manually. EXCLUSIVE of Tax only. Use with Caution as you are responsible for calculating Tax correctly.
Default Tax System Exclusive/Inclusive

TLTotIncluOfTax

Currency

If set Tax Type to INCLUSIVE, Line Total inclusive of Tax. PostTrans will calculate Tax and Unit Price back from Inclusive Tax Total for Line given Qty and overcome rounding problems. Best choice when importing data as reduces rounding problems Line Total inclusive of Tax. PostTrans will calculate Tax and Unit Price back from Inclusive Tax Total for Line given Qty and overcome rounding problems. Use THNetDiscount to account for discount. Best choice when importing data as reduces rounding problems

Financial Analysis:

TLAccCode^

Text 10

Account/Nominal Code, if not specified then uses default (Double Click to view Xero Chart of Accounts)
    In Cell Searching values in table ACCCODE-TRANS_TLACCCODE

TLTracking1^

Text 100

Tracking code on the Line.
    In Cell Searching values in table TRACKING1

TLTracking2^

Text 100

Tracking code on the Line.
    In Cell Searching values in table TRACKING2

Discount:

TLDiscValue^

Double

Sales:Additional Discount Value to be applied to TLUnitPrice,
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

TLDiscPerc^

Double

Sales:Additional Discount Percent to be applied to TLUnitPrice
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Line Special Functions:

TLSkipLine

Yes/No

If YES then skip this line

TLForceNewTrans

Yes/NoTF

If YES then force a new transaction

TLForceBlankDesc

Yes/NoTF

If YES then force Blank Description

Analysis:

Transaction Header:

THTransType^

Text 2 Head Chg

Transaction Type. SI,PI,SQ,PO,SC,PC etc. (Double Click to view Xero Transactions)
        In Cell Searching values:Transaction Type
                  SQ, Sales Quote
                  SI, Sales Invoice
                  SC, Sales Credit
                  PO, Purchase Order
                  PI, Purchase Invoice
                  PC, Purchase Credit

THContact^

Text 250 Head Chg

Used to Identify Contact. When in cell searching can search by Account number (or name prefixed with SPACE) or Name (see System Setup >> 'Defaults'). But will also match Contact given Account Number, Xero Contact ID, Contact Number or Contact Name, but if omitted will match by CoID, CoContactNum, CoAccountNum or CoName
    In Cell Searching values in table CONTACTALL

THTransDate

Date Head Chg

Transaction Date

THDueDate

Date Head

Due Date for Inv, Delivery Date on Order, Expire Date on Quote. SO/PO only

THExpctDate

Date Head

Expected Payment Date

THTransRef

Text 255 Head Chg

System assigned OR if specified must be unique(sales) and non-unique(Purchase), blank to have system assign. If transaction already exists it will be overwritten

THRef

Text 255 Head Chg

Reference number

THDefTaxCode^

Text 60 HeadOnly

Set all lines to this Tax Code if not overridden on line, or on contact. Else use Nominal Account default. Needed if Contact creation allowed, as we don't know default
    In Cell Searching values in table TAXCODE-TRANS_THDEFTAXCODE

THDefSalAcc^

Text 60 Req, HeadOnly

Set Default Sales Nominal code if not specified in transactions or on Contact defaults
    In Cell Searching values in table ACCCODEREVENUE

THDefPurAcc^

Text 60 Req, HeadOnly

Set Default Purchase Nominal code if not specified in transactions or on Contact defaults
    In Cell Searching values in table ACCCODEEXPENSE

THStatus^

Text 60 Req, HeadOnly

Import Transaction as DRAFT,SUBMITTED,AUTHORISED. If with payment must be AUTHORISED (Quotes can also be SENT,DECLINED,ACCEPTED)

Currency:

THCurCode^

Text 3 Head Pro

Currency code, will only show currencies defined as Bank accounts
    In Cell Searching values in table CURRENCY

THExchRate

Double Head Pro

Will use system rate or override using this tag

Discount on Header:

THNetDiscount

Currency Head

Net Discount Value applied to whole transaction

Other:

THDocTheme^

Text 60 Head

Theme/Branding of documents
    In Cell Searching values in table Branding

Purchase Order:

THPoAttenTo

Text 255 Head

Delivery Attention To

THPoInst

Text 255 Head

Delivery instructions

THPoPhone

Text 255 Head

PO Phone number

THPoAdd

Text 255 Head

PO Delivery Address

Header Payment:

THPayBank^

Text 10 Head

Bank Account for Payments
    In Cell Searching values in table ACCCODEBANK

THPayAmt

Double Head

Amount Paid - Best to use INCLUSIVE TAX when posting payment to make sure the totals agree, else rounding problems could be encountered. Switch in Setup adds these values up or use the last value in column

THPayRef

Text 60 Head

Payment Ref. If specified without THPayAmt, then takes invoice amount

THPayIsRec

Yes/NoTF Head

Payment is Reconciled.

Quote:

THQuTitle

Double Head

Quote Title

THQuSummary

Text 3000 Head

Quote Summary

THQuTerms

Text 4000 Head

Quote Terms

Attachments:

THAttch1

Text 255 Head XML

File attachement1

THAttch2

Text 255 Head XML

File attachement2

THAttch3

Text 255 Head XML

File attachement3

THAttch4

Text 255 Head XML

File attachement4

Other xxxx:

THSendEmail

Yes/NoTF Head

YES to send by e-mail (Sales Invoice only - STATUS SUMBITTED,AUTHORISED or PAID)

THSent

Yes/NoTF Head

Mark as Sent to customer if YES

Header:Add Message:

THAddNote

Text 2500 HeadOnly

Add Note to Transaction

THMessTop

Text HeadOnly

Add message at top of transaction
Creating Message Top/Bottom

THMessBot1

Text HeadOnly

Add message at bottom of transaction
Message Top/Bottom

THMessBot2

Text HeadOnly

Add message at bottom of transaction
Message Top/Bottom

Analysis xxxx:

THDefTrack1^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING2

THDefTrack2^

Text 100 HeadOnly

Set Tracking for whole transaction unless overridden in Lines
    In Cell Searching values in table TRACKING2

Transaction Update:

Lookup Item Values:

PrQtyOnHand

Double Read

Item/Product Qty free, updated when stock code entered

PrPrice^

Currency Read

Price in currency
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

PrSuppAccount

Text 8 Read

Supplier`s Account Reference Lookup

Header:Before Posting:

THBefSWCompany^

Text 6 Head Chg Pro

This will cause PostTrans to change company before posting if different from current Sage Line 50 Company. Thus can be user to post transactions into different companies, thus intercompany transactions are possible. It uses a 6 letter company code, which found by pressing the Select Company button in system setup. Must be specified in each row.
Posting to multiple companies
    In Cell Searching values in table CompanySelect

THBefDisplayMess

Text 60 HeadOnly

If contains value, then this text is displayed, and import cannot take place until this text if clear. Thus you can your own formula, macro or VBA code to stop import until a certain condition.
Validating Transaction before posting

Header - After Posting:

THBefDisplayMessYN

Text 60 HeadOnly

If contains value, then this text is displayed. Asks Yes/No to continue
Validating Transaction before posting

THAftWriteRef

Text 60 HeadOnly Read

After posting write back transaction reference to this cell

THAftWriteAlocRef

Text 60 HeadOnly Read

SOP After posting write back stock allocation status, Full, Part

THAftWritePayRef

Text 60 HeadOnly Read

Writes back the reference of payment transaction, if posting a SA on Sales Order

THAftProcessSheet

Text 60 HeadOnly Read

After posting this sheet, switch to work book with this name, and post that. Allows back to back purchase orders, or multiple company transfers. PRO
Posting into multiple Xero Companies

THAftSwitchSheet

Text 60 HeadOnly Read

After posting this sheet, switch to work book with this name PRO
Posting into multiple Xero Companies

Header - After Save:

THAftSavePATH

Text 60 HeadOnly Read

If THAftSavePATH and THAftSaveFilename specified, will save as PATH + ‘\YYYMM\’ + Filename + "_0000.xls". YYYMM can be overridden with value in THSaveSubDirectory cell.
Saving template after posting

THAftSaveSubDir

Text 60 HeadOnly Read

See THAftSavePATH
more about saving

THAftSaveFilename

Text 60 HeadOnly Read

See THAftSavePATH. Example ‘ABAP01’ will saveas ABAP01_0001.xls.
more about saving

Import File:

THImportFullName

Text 500 HeadOnly

Full PATH and Filename of the file just imported using the Import button. This is required if you set the directory to move file after posting transaction.
File Import instructions

THImportFileName

Text 500 HeadOnly

Filename of the file just imported using the Import button
File Import instructions

After Account Selection:

CoID

Text 60 Rel

Xero Contact ID. System Setup >> 'Defaults' allows Contact creation. If Single Transactions then this is used to store the Contact ID for subsequent contact updates

CoName

Text 60 Rel

Xero Contact Name. System Setup >> 'Defaults' allows Contact creation/update.

CoAccountNum

Text 100 Rel

Account Code for Contact. If importing mutiple Transactions this will be used to identify accounts, if no Xero Contact ID given

CoContactNum

Text 100 Rel

Contact Number used by external systems. Shown at the bottom of Xero UI. If importing mutiple Transactions this will be used to identify accounts, if no Xero Contact ID given

Financial:

CoOutstanding^

Currency Read Rel

Outstanding over 30 days.
PRO Double clicking on this cell will drill down to Ledger.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

CoOverdue^

Currency Read Rel

Available Credit.
PRO Double clicking on this cell will drill down to Ledger.
    Changes in this cell will recalc other cells. Delete ^ to disable this calculation.

Registered Address:

CoAddressType

Text 100 Rel

AddressType

CoAttentionTo

Text 100 Rel

AttentionTo

CoAdd1

Text 100 Rel

AddressLine1

CoAdd2

Text 100 Rel

AddressLine2

CoAdd3

Text 100 Rel

AddressLine3

CoAdd4

Text 100 Rel

AddressLine4

CoCity

Text 100 Rel

City

CoRegion

Text 100 Rel

Region

CoPostalCode^

Text 100 Rel

PostalCode
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CoCountry

Text 100 Rel

Country

Street Address:

CoStAddressType

Text 100 Rel

StAddressType

CoStAttentionTo

Text 100 Rel

StAttentionTo

CoStAdd1

Text 100 Rel

StAddressLine1

CoStAdd2

Text 100 Rel

StAddressLine2

CoStAdd3

Text 100 Rel

StAddressLine3

CoStAdd4

Text 100 Rel

StAddressLine4

CoStCity

Text 100 Rel

StCity

CoStRegion

Text 100 Rel

StRegion

CoStPostalCode^

Text 100 Rel

StPostalCode
More about Postcode Address Lookup
    In Cell Searching values in table SimplyPostcode

CoStCountry

Text 100 Rel

StCountry

Primary Conctact:

CoFirstName

Text 100 Rel

First Name

CoLastName

Text 100 Rel

Last Name

CoEmailAddress

Text 255 Rel

e-mail1

Other Contacts:

CoFirstName1

Text 100 Rel

First Name

CoLastName1

Text 100 Rel

Last Name

CoEmailAddress1

Text 255 Rel

e-mail1

CoEmailInc1

Yes/NoTF Rel

Include in Mail

CoFirstName2

Text 100 Rel

First Name

CoLastName2

Text 100 Rel

Last Name

CoEmailAddress2

Text 255 Rel

e-mail1

CoEmailInc2

Yes/NoTF Rel

Include in Mail

CoFirstName3

Text 100 Rel

First Name

CoLastName3

Text 100 Rel

Last Name

CoEmailAddress3

Text 255 Rel

e-mail1

CoEmailInc3

Yes/NoTF Rel

Include in Mail

CoFirstName4

Text 100 Rel

First Name

CoLastName4

Text 100 Rel

Last Name

CoEmailAddress4

Text 255 Rel

e-mail1

CoEmailInc4

Yes/NoTF Rel

Include in Mail

CoFirstName5

Text 100 Rel

First Name

CoLastName5

Text 100 Rel

Last Name

CoEmailAddress5

Text 255 Rel

e-mail1

CoEmailInc5

Yes/NoTF Rel

Include in Mail

Other Contact xxx:

CoSkypeUserName

Text 100 Rel

Skype

Defaults:

CoSalesTaxType

Text 100 Read Rel

SalesTaxType

CoPurchTaxType

Text 100 Read Rel

Phone

CoTaxType

Text 100 Read Rel

Tax Type

CoCurrency^

Text 3 Read Rel

Currency
    In Cell Searching values in table CurrencyCode

Phone:

CoPhoneContry

Text 100 Rel

Country

CoPhoneArea

Text 100 Rel

Area

CoPhonePhone

Text 100 Rel

Number

CoFaxContry

Text 100 Rel

Fax Country

CoFaxArea

Text 100 Rel

Fax Area

CoFaxPhone

Text 100 Rel

Fax Number

CoMobContry

Text 100 Rel

Mob Country

CoMobArea

Text 100 Rel

Mob Area

CoMobPhone

Text 100 Rel

Mob Number

CoDDContry

Text 100 Rel

DD Country

CoDDArea

Text 100 Rel

DD Area

CoDDPhone

Text 100 Rel

DD Number

Contact Groups:

CoGrps

Text 100 Read Rel

Contact Groups

CuDiscRate

Double Rel

Discount Rate %

CoSalesAcc^

Text 100 Read Rel

Sales Default Nominal Code
    In Cell Searching values in table NomCode

CoPurchAcc^

Text 100 Read Rel

Purchase Default Nominal Code
    In Cell Searching values in table NomCode

CoAccCode^

Text 100 Read Rel

Default Nominal Code which will be Sales or Purchase based on transaction type
    In Cell Searching values in table NomCode


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


Rel = Used to get associated data in Transaction Header

XML = Un-attended PostTrans XML API for Sage 200

Head  A header tag can be used in header section, before row 30, of the transaction template.  Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

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.

Single Transaction:

 

Single Transaction

 

OrderPad with Price Bands

 

OrderPad

Multiple Transactions:

 

Inc of Tax with Pay and creates/updates Contacts

 

Inc of Tax with Pay, Product Item and creates/updates Contacts from file

 

Import Transactions from File BLANK TEMPLATE

 

Exclusive of Tax

 

Transactions to Multiple Companies

Multiple Transactions:

 

Single Nominal Journal

 

Single Nominal Journal with Contra Line

 

Multiple Nominal Journals to Multiple Companies

 

Multiple Nominal Journals

 

Import and Match Sales Receipts/Payments

 

Import and Match Sales Receipts/Payments from CSV file