Sage 50:Extract/Amend/Import Customers
PostTrans imports Customers, 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.
Related Pages
Import Customer 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 Customers (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 Customers
Customer template for importing into Sage 50
Demonstration Video
Sage 50 - Import Customer Data, also Extract and Edit
| |
0:00 |
Video Introduction |
| |
1:10 |
PostTrans Template Introduction |
| |
3:44 |
Extract Sage Line 50 Customers |
| |
6:30 |
Add New Sage Customers |
| |
6:42 |
Add additional fields to allow Price list and Nominal edit |
| |
10:12 |
Filter Extracted Sage Line 50 Customer Data |
| |
12:00 |
How to find more Information and Training |
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.
Template Structure for Static Data
The template must have tag ‘Sage50cust’ in cell A1 to instruct PostTrans that we are interested in
Sage 50 Customers, 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 Customers 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 Customers Data
Having extracted data, the user can now simply edit the Customers in Microsoft Excel, before re-importing it back into
Sage 50.
PostTrans uses the tagged column CuCRC 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 CuCode
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.
Other Useful Topics
Clear Transaction and copy down formula
Not all these tags/columns are needed. The table below lists the required and recommended:
|
Tag Name
|
Data Type
|
Description
|
|
Important Fields:
|
|
Sage50cust
|
Text Req
|
Returns `POSTED` if Trans line has been posted and will be ignored if `POSTED` (required). Also reports errors/warnings in data row
|
|
CuCRC
|
Text Req
|
Contains CRC number to help PostTrans detect changes in current row.
|
|
CuDelete
|
Text 1
|
Delete record if "X"
|
|
CuSearch^
|
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 CustCode
|
|
CuCode
|
Text 8 EOF Req XML
|
Account Reference
|
|
Account Details:
|
|
CuName
|
Text 60 XML
|
Account Name
|
|
CuBalance
|
Currency Read
|
Balance
|
|
CuInactive
|
Yes/No- XML
|
Inactive Flag - yes/No
Sage V21 2015 onwards
|
|
Registered Address:
|
|
CuAdd1
|
Text 60 XML
|
Account Address Line 1
|
|
CuAdd2
|
Text 60 XML
|
Account Address Line 2
|
|
CuAdd3
|
Text 60 XML
|
Account Address Line 3
|
|
CuAdd4
|
Text 60 XML
|
Account Address Line 4
|
|
CuPostcode^
|
Text 60 XML
|
Account Postcode. If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space,Space + Postcode to search for an address.
Postcode Address Lookup
In Cell Searching values in table SimplyPostcode
|
|
CuCountryCode^
|
Text 2 XML
|
Country Code.
In Cell Searching values in table CountryCode
|
|
CuVATRegNo
|
Text 20 XML
|
VAT Registration Number
|
|
Contact Information:
|
|
CuContactName
|
Text 30 XML
|
Contact Name
|
|
CuTradeContact
|
Text 30 XML
|
Name of Trade Contact
|
|
CuPhone
|
Text 30 XML
|
Telephone Number
|
|
CuPhone2
|
Text 30 XML
|
Second Telephone Number
|
|
CuFax
|
Text 30 XML
|
Fax Number
|
|
CuWWW
|
Text 255 XML
|
WWW Address
|
|
e-mail Settings:
|
|
Cue-mail1
|
Text 255 XML
|
EMail Address
|
|
Cue-mail2
|
Text 255 XML
|
EMail Address 2
Sage V19 2013 onwards
|
|
Cue-mail3
|
Text 255 XML
|
EMail Address 3
Sage V19 2013 onwards
|
|
CuSendInvEmail
|
Yes/No- XML
|
Send Invoices To This Account via Email
|
|
CuSendLettersEmail
|
Yes/No- XML
|
Send Letters To This Account via Email
|
|
Defaults - Custom Fields:
|
|
CuAnal1
|
Text 30 XML
|
Analysis 1
|
|
CuAnal2
|
Text 30 XML
|
Analysis 2
|
|
CuAnal3
|
Text 30 XML
|
Analysis 3
|
|
Defaults - Pricing and Discounting:
|
|
CuDiscRate
|
Double XML
|
Discount Rate %
|
|
CuDiscType^
|
Integer XML
|
Discount Type
In Cell Searching values:Discount Type
0, None
1, Invoice Value
2, Band A
3, Band B
4, Band C
5, Band D
6, Band E
|
|
CuPriceList^
|
Text 8 XML
|
Price List Reference
In Cell Searching values in table PriceListCode
|
|
Defaults - Misc Defaults:
|
|
CuNomCode^
|
Text 8 XML
|
Default Nominal Code
In Cell Searching values in table NomCode
|
|
CuOverideProductNom
|
Yes/No- XML
|
Override Product Nominal Code in Invoicing
|
|
CuTaxCode^
|
Integer XML
|
Default Tax Code
In Cell Searching values in table TaxCode
|
|
CuOverideProductTax
|
Yes/No- XML
|
Override the products own Tax Code.
|
|
CuCurrency^
|
Integer XML
|
Currency
In Cell Searching values in table CurrencyCode
|
|
CuDepartment^
|
Text 2 XML
|
Department Number
In Cell Searching values in table DeptCode
|
|
Credit Control - Terms:
|
|
CuCreditLimit
|
Currency XML
|
Credit Limit
|
|
CuSettDays
|
Integer XML
|
Settlement Due Days
|
|
CuSettDiscount
|
Double XML
|
Settlement Discount Rate
|
|
CuPaymentDueDays
|
Integer XML
|
Payment Due Days
|
|
CuAvgPayDays
|
Integer XML
|
Average Time To Pay
|
|
CuTermsTxt
|
Text 30 XML
|
Terms
|
|
CuCreditRef
|
Text 60 XML
|
Credit Bureau Reference
|
|
CuBureau^
|
Integer XML
|
Credit Bureau Used
In Cell Searching values in table BureauCode
|
|
CuPriorityCust
|
Yes/No- XML
|
Priority Customer
Sage V18 2012 onwards
|
|
CuCreditPosition^
|
Integer XML
|
Credit Position
In Cell Searching values in table CreditPosition
|
|
CuStatus^
|
Integer XML
|
Account Status
In Cell Searching values in table StatusCode
|
|
Credit Control - Restrictions:
|
|
CuApplyCharges
|
Yes/No- XML
|
Can Charge Credit
|
|
CuTermsAgreed
|
Yes/No- XML
|
Terms Agreed Flag
|
|
CuHoldMail
|
Yes/No- XML
|
Restrict Mail
|
|
CuOnHold
|
Yes/No- XML
|
Account On Hold
|
|
Credit Control - Credit Review:
|
|
cuDateOpened
|
Date XML
|
Date Account Opened
|
|
CuLastCreditReview
|
Date XML
|
Date Last Credit Review
|
|
CuNextCreditReview
|
Date XML
|
Date Next Credit Review
|
|
CuApplicationDate
|
Date XML
|
Date Credit Applied For
|
|
CuCreditAppRecived
|
Date XML
|
Date Credit Application Recieved
|
|
CuMemo
|
Memo XML
|
Customer Notes
|
|
Sales:
|
|
CuFirstINV
|
Date Read XML
|
First Invoice Date
|
|
CuLastInvDate
|
Date Read XML
|
Last Invoice Date
|
|
CuLastReceipt
|
Date Read XML
|
Last Payment Date
|
|
CuYTD
|
Currency Read XML
|
Turnover Year Date
|
|
CuLYTD
|
Currency Read XML
|
Prior YTD
|
|
Bank:
|
|
CuBankName
|
Text 60 XML
|
Bank Name
|
|
CuBankAdd1
|
Text 60 XML
|
Bank Address Line 1
|
|
CuBankAdd2
|
Text 60 XML
|
Bank Address Line 2
|
|
CuBankAdd3
|
Text 60 XML
|
Bank Address Line 3
|
|
CuBankAdd4
|
Text 60 XML
|
Bank Address Line 4
|
|
CuBankPostcode^
|
Text 60 XML
|
Bank Address Postcode. If Postcode Search is enabled in ‘Setup >> Lookup >> Address Lookup’ then enter Space,Space + Postcode to search for an address.
Postcode Address Lookup
In Cell Searching values in table SimplyPostcode
|
|
CuBankAccName
|
Text 60 XML
|
Bank Account Name
|
|
CuBankSort
|
Text 10 XML
|
Bank Sort Code
|
|
CuBankNumber
|
Text 60 XML
|
Bank Account Number
|
|
Payment Details.:
|
|
CuBacRef
|
Text 60 XML
|
Reference for BACS Transfer
|
|
CuBankIBAN
|
Text 60 XML
|
Bank IBAN Number
|
|
CuBankSwift
|
Text 60 XML
|
Bank BIC/Swift
|
|
CuBankRollNo
|
Text 60 XML
|
Bank Roll Number
|
|
CuBankAddRef1
|
Text 60 XML
|
Bank Additional Ref 1
|
|
CuBankAddRef2
|
Text 60 XML
|
Bank Additional Ref 2
|
|
CuBankAddRef3
|
Text 60 XML
|
Bank Additional Ref 3
|
|
CuPaymentMethod^
|
Text 2 XML
|
Payment Type. If BACS then sets CuOnlineReceipts=YES, other wise need to set manually
Sage V21 2015 onwards
In Cell Searching values:Payment Method
0, None
1, BACS/SEPA
2, Cash
3, Cheque
4, Credit Card
5, DD/SO
6, Other
7, User1
8, User2
9, User3
10, User4
11, User5
|
|
CuOnlineReceipts
|
Yes/No- XML
|
BACS Online Receipts Flag. If CuPaymentMethod=BACS set other wise needs setting manuyally
|
|
Payment Details:
|
|
CuManager
|
Text 60 XML
|
Name of Account Manager
|
|
CuType^
|
Integer XML
|
Account Type
In Cell Searching values:Account Type
0, Normal
|
|
Delivery Address:
|
|
CuDelName
|
Text 60 XML
|
Delivery Name
|
|
CuDelAdd1
|
Text 60 XML
|
Delivery Address Line 1
|
|
CuDelAdd2
|
Text 60 XML
|
Delivery Address Line 2
|
|
CuDelAdd3
|
Text 60 XML
|
Delivery Address Line 3
|
|
CuDelAdd4
|
Text 60 XML
|
Delivery Address Line 4
|
|
CuDelPostcode^
|
Text 60 XML
|
Delivery Address Postcode
Postcode Address Lookup
In Cell Searching values in table SimplyPostcode
|
|
CuDelContactName
|
Text 30 XML
|
Delivery Contact Name
|
|
CuDelFax
|
Text 30 XML
|
Delivery Fax Number
|
|
CuDelPhone
|
Text 30 XML
|
Delivery Telephone Number
|
|
Building Society:
|
|
CuBankBuildSocRef
|
Text 60 XML
|
Reference for Building Society Transfer
|
|
CuBankUseBuildingSoc
|
Yes/No- XML
|
Account uses Building Society
|
|
Credit Card:
|
|
CuCreditAppRecieved
|
Yes/No- XML
|
True if Credit Application Received
|
|
CuCCNumber
|
Text 30 XML
|
Credit Card Number
|
|
Charity Donor:
|
|
CuDonTitle
|
Text 4 XML
|
Charity Donor Title
Sage V20 2014 onwards
|
|
CuDonForname
|
Text 35 XML
|
Charity Donor Forename
Sage V20 2014 onwards
|
|
CuDonSurname
|
Text 35 XML
|
Charity Donor Surname
Sage V20 2014 onwards
|
|
CuDonFund^
|
Integer XML
|
Charity Donation Fund Number
In Cell Searching values in table CharityCode
|
|
CuDonGiftAid
|
Yes/No- XML
|
Charity Donor Gift Aid is enabled
|
|
CuDonDecFrom
|
Date XML
|
Donor Declaration Valid From
|
|
CuDonTyDonor
|
Yes/No- XML
|
Charity Donor Type Donor Flag
Sage V18 2012 onwards
|
|
CuDonTyMember
|
Yes/No- XML
|
Charity Donor Type Member Flag
Sage V18 2012 onwards
|
|
CuDonTyCust
|
Yes/No- XML
|
Charity Donor Type Customer Flag
Sage V18 2012 onwards
|
|
Charity Membership Details:
|
|
CuDonMemberSince
|
Date XML
|
Member Since
|
|
CuDonReminderDate
|
Date XML
|
Member Renewal Date
|
|
CuDonReminderDays
|
Integer XML
|
Member Reminder Date (days)
|
|
Other:
|
|
CuSalesRep
|
Text 60 XML
|
Name of Sales Representative
|
|
CuEcCode
|
Text 3 Read XML
|
EC Member Code
|
|
CuDUNSno
|
Text 9 XML
|
DUNS Number
|
Req = Field is required EOF = End of file, Stop when no more values
XML = Un-attended PostTrans XML API for Sage 200
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.
|
|
|
|
Test DB Sheet
|
Sheet:''
DO NOT DELETE - Used for testing, or shen setup is pressed without PostTrans Sheet
Help article for Test DB Sheet
|
|
|
*** Test Sage Connection Template ***
|
Sheet:'Test Sheet'
DO NOT DELETE - Used for testing, or when setup is pressed without PostTrans Sheet
Help article for *** Test Sage Connection Template ***
|
Static Data - Customer:
|
|
|
|
|
Customer Records
|
Sheet:'Customer Rec'
Allow Extract/Amend/Import of Customer records
Main page for Customer records Import
Help article for Customer records Import
|
|
|
Customer Price Lists
|
Sheet:'Customer Price List'
Extract, Add and Amend the Price List at top level
Main page for Price List Update
Help article for Price List Update
|
|
|
Customer Price List/Qty Assignment
|
Sheet:'Customer Price List Assignment'
Allow assignement of Customer Qty breaks and Price list
Main page for Customer records Import
Help article for Customer records Import
|
|
|
Product Customer Price Structure
|
Sheet:'Customer Prices Structure'
Extract, Add and Amend Customer Prices
Main page for Customer Prices Update
Help article for Customer Prices Update
|
|
|
Product Customer Special Prices
|
Sheet:'Special Prices'
Extract, Add and Amend Customer Special Prices
Main page for Customer Prices Update
|