Sage 50:Extract/Amend/Import Nominal Accounts

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

Import Nominal Account Features in Sage 50

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

Advantages

Highlighted Validation of codes and data
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

Examples of importing Sage 50 Nominal Accounts

Nominal Account template for importing into Sage 50

Demonstration Video

Sage 50 - Posting a Nominal Journal with VAT PostTrans

	0:00	Video Introduction
	1:50	Opening Nominal Journal Example
	2:30	Posting a Normal Nominal Journal into Sage 50
	3:25	Posting a Nominal Journal with Contra Nominal line
	4:00	Using In cell searching to enter nominal lines
	6:10	Posting a VAT Nominal Journal into Sage 50
	7:20	How to find more information and Training

Template Structure for Static Data

The template must have tag ‘Sage50nom’ in cell A1 to instruct PostTrans that we are interested in Sage 50 Nominal Accounts, 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 Sage 50 mapping. Table of Nominal Accounts 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.

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 Nominal Accounts Data

Having extracted data, the user can now simply edit the Nominal Accounts in Microsoft Excel, before re-importing it back into Sage 50.

PostTrans uses the tagged column NoCRC 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 Sage 50. PostTrans will read the Header cells and then process the rows of data until NoCode column contains no data. The Sage 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 Sage Demo data without a license. A license is required to import into any other Sage Company. Pricing

What else can I do?

Other Sage 50 Extract/Import types

Frequently Asked Questions?

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

Tags which map to Sage 50

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

Tag Name	Data Type	Description
Important Fields:
Sage50nom	Text Req	Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
NoCRC	Text Req	Contains CRC number to help PostTrans detect changes in current row.
NoCode	Text 8 EOF Req	Department Code
NoName	Text 60 Req	Department Name
NoType^	Integer	Account Type Code In Cell Searching values:Nominal Code 1, Normal 2, Bank 4, Control
NoInactive	Yes/No-	Is Inactive Sage V24 2018 onwards
NoAuditRef	Text 20	Incomplete Records Reference
NoBal	Double	Balance
Balance:
NoBalBF	Double	Balance Brought Forward
NoBalMon1	Double	Balance Month 1
NoBalMon2	Double	Balance Month 2
NoBalMon3	Double	Balance Month 3
NoBalMon4	Double	Balance Month 4
NoBalMon5	Double	Balance Month 5
NoBalMon6	Double	Balance Month 6
NoBalMon7	Double	Balance Month 7
NoBalMon8	Double	Balance Month 8
NoBalMon9	Double	Balance Month 9
NoBalMon10	Double	Balance Month 10
NoBalMon11	Double	Balance Month 11
NoBalMon12	Double	Balance Month 12
NoBalFut	Double	Balance Future
Budget:
NoBudgetMon1	Double	Budget Month 1
NoBudgetMon2	Double	Budget Month 2
NoBudgetMon3	Double	Budget Month 3
NoBudgetMon4	Double	Budget Month 4
NoBudgetMon5	Double	Budget Month 5
NoBudgetMon6	Double	Budget Month 6
NoBudgetMon7	Double	Budget Month 7
NoBudgetMon8	Double	Budget Month 8
NoBudgetMon9	Double	Budget Month 9
NoBudgetMon10	Double	Budget Month 10
NoBudgetMon11	Double	Budget Month 11
NoBudgetMon12	Double	Budget Month 12
Credit:
NoCreditBF	Double	Credit Brought Forward
NoCreditMon1	Double	Credit Month 1
NoCreditMon2	Double	Credit Month 2
NoCreditMon3	Double	Credit Month 3
NoCreditMon4	Double	Credit Month 4
NoCreditMon5	Double	Credit Month 5
NoCreditMon6	Double	Credit Month 6
NoCreditMon7	Double	Credit Month 7
NoCreditMon8	Double	Credit Month 8
NoCreditMon9	Double	Credit Month 9
NoCreditMon10	Double	Credit Month 10
NoCreditMon11	Double	Credit Month 11
NoCreditMon12	Double	Credit Month 12
NoCreditFut	Double	Credit for Future
Debit:
NoDebitBF	Double	Debit Brought Forward
NoDebitMon1	Double	Debit Month 1
NoDebitMon2	Double	Debit Month 2
NoDebitMon3	Double	Debit Month 3
NoDebitMon4	Double	Debit Month 4
NoDebitMon5	Double	Debit Month 5
NoDebitMon6	Double	Debit Month 6
NoDebitMon7	Double	Debit Month 7
NoDebitMon8	Double	Debit Month 8
NoDebitMon9	Double	Debit Month 9
NoDebitMon10	Double	Debit Month 10
NoDebitMon11	Double	Debit Month 11
NoDebitMon12	Double	Debit Month 12
NoDebitFut	Double	Debit for Future
Prior Years:
NoPriYrMon1	Double	Prior Year 1 Month 1
NoPriYrMon2	Double	Prior Year 1 Month 2
NoPriYrMon3	Double	Prior Year 1 Month 3
NoPriYrMon4	Double	Prior Year 1 Month 4
NoPriYrMon5	Double	Prior Year 1 Month 5
NoPriYrMon6	Double	Prior Year 1 Month 6
NoPriYrMon7	Double	Prior Year 1 Month 7
NoPriYrMon8	Double	Prior Year 1 Month 8
NoPriYrMon9	Double	Prior Year 1 Month 9
NoPriYrMon10	Double	Prior Year 1 Month 10
NoPriYrMon11	Double	Prior Year 1 Month 11
NoPriYrMon12	Double	Prior Year 1 Month 12
NoPriYr2Mon1	Double	Prior Year 2 Month 1
NoPriYr2Mon2	Double	Prior Year 2 Month 2
NoPriYr2Mon3	Double	Prior Year 2 Month 3
NoPriYr2Mon4	Double	Prior Year 2 Month 4
NoPriYr2Mon5	Double	Prior Year 2 Month 5
NoPriYr2Mon6	Double	Prior Year 2 Month 6
NoPriYr2Mon7	Double	Prior Year 2 Month 7
NoPriYr2Mon8	Double	Prior Year 2 Month 8
NoPriYr2Mon9	Double	Prior Year 2 Month 9
NoPriYr2Mon10	Double	Prior Year 2 Month 10
NoPriYr2Mon11	Double	Prior Year 2 Month 11
NoPriYr2Mon12	Double	Prior Year 2 Month 12
NoPriYr3Mon1	Double	Prior Year 3 Month 1
NoPriYr3Mon2	Double	Prior Year 3 Month 2
NoPriYr3Mon3	Double	Prior Year 3 Month 3
NoPriYr3Mon4	Double	Prior Year 3 Month 4
NoPriYr3Mon5	Double	Prior Year 3 Month 5
NoPriYr3Mon6	Double	Prior Year 3 Month 6
NoPriYr3Mon7	Double	Prior Year 3 Month 7
NoPriYr3Mon8	Double	Prior Year 3 Month 8
NoPriYr3Mon9	Double	Prior Year 3 Month 9
NoPriYr3Mon10	Double	Prior Year 3 Month 10
NoPriYr3Mon11	Double	Prior Year 3 Month 11
NoPriYr3Mon12	Double	Prior Year 3 Month 12
NoPriYr4Mon1	Double	Prior Year 4 Month 1
NoPriYr4Mon2	Double	Prior Year 4 Month 2
NoPriYr4Mon3	Double	Prior Year 4 Month 3
NoPriYr4Mon4	Double	Prior Year 4 Month 4
NoPriYr4Mon5	Double	Prior Year 4 Month 5
NoPriYr4Mon6	Double	Prior Year 4 Month 6
NoPriYr4Mon7	Double	Prior Year 4 Month 7
NoPriYr4Mon8	Double	Prior Year 4 Month 8
NoPriYr4Mon9	Double	Prior Year 4 Month 9
NoPriYr4Mon10	Double	Prior Year 4 Month 10
NoPriYr4Mon11	Double	Prior Year 4 Month 11
NoPriYr4Mon12	Double	Prior Year 4 Month 12
NoPriYr5Mon1	Double	Prior Year 5 Month 1
NoPriYr5Mon2	Double	Prior Year 5 Month 2
NoPriYr5Mon3	Double	Prior Year 5 Month 3
NoPriYr5Mon4	Double	Prior Year 5 Month 4
NoPriYr5Mon5	Double	Prior Year 5 Month 5
NoPriYr5Mon6	Double	Prior Year 5 Month 6
NoPriYr5Mon7	Double	Prior Year 5 Month 7
NoPriYr5Mon8	Double	Prior Year 5 Month 8
NoPriYr5Mon9	Double	Prior Year 5 Month 9
NoPriYr5Mon10	Double	Prior Year 5 Month 10
NoPriYr5Mon11	Double	Prior Year 5 Month 11
NoPriYr5Mon12	Double	Prior Year 5 Month 12
Other:
NoQuickRatioFlg	Yes/No-	Quick Ratio Account Flag
Notes:
Memo	Memo	Nominal Notes

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.

Sage 50:Extract/Amend/Import Nominal Accounts

Import Nominal Account Features in Sage 50

Advantages

Examples of importing Sage 50 Nominal Accounts

Demonstration Video

Template Structure for Static Data

Common Features

In-Cell Searching

Extracting the Data

Amending Nominal Accounts Data

Importing the Data

File Import Button

Validation

Evaluation and License

What else can I do?

Frequently Asked Questions?

Other Useful Topics

Tags which map to Sage 50

Related Example Sheets

Transaction Data - Nominal Journals:

Sage50TransNom.xlsx

Inside this workbook

Sage50trans.xlsx

Inside this workbook

Sage50transNom.xlsx

Inside this workbook