Customer Upload

This feature allows for the upload of customer standing data and opening balances using a csv file.

Upload Mode

When uploading data using csv files an "Upload Mode" must be selected:

Add new data :- (**Currently only available on the Donor Upload)

When this mode is selected the upload file must contain only new items. If any item in the upload file matches name of an existing item, the entire upload will be rejected and you will see a notification of the first item which caused the failure. Typically the issue is resolved by either removing the item from the upload if it is a duplicate of an existing item, or by adding a middle name to distinguish between two people who happen to have the same name.

Delete all prior upload data and add new data :-

When this mode is selected the application will try to delete all previously uploaded data before adding data from the new file. If previously uploaded data has been edited, or has additional related data that has been added since the upload, the item will not be deleted and will not be replaced by any new data in the upload file.

Replace any matching prior upload data and add new data :-

When this mode is selected the application will try to add all the data in the new file using the donor_code as the unique identifier defining which record to update. If any item in the new upload matches an existing record the existing record will be replaced unless the previously uploaded data has been edited, or has additional related data that has been added since the upload.

When a file has been successfully uploaded, the data is displayed in a screen based table that highlights any inconsistencies, duplicates or missing items. Amendments may be made. Where necessary it is also possible to map incoming field names to system required names.

Technical Specifications

File Format

The upload requires that data is presented in a standard csv file. Please note the following points carefully and take care to ensure that your file is properly formatted.

When using MS Excel to generate csv files you are advised to select the full area of the dataset and format the cells as "Text" before saving as a csv file. This will help to ensure that the file is consistently formatted.

The order of the fields is not significant but the order of the data fields must match the order of the field names in the first line.
Where a text field contains a comma the field should be quoted using double quotes - e.g. "office,Products Ltd".
Numeric fields should NOT include comma formatting of thousands etc.
Numeric fields may include a decimal point as appropriate.
Numeric fields may include a - sign as a prefix if negative - e.g. -9999.99
Date fields must contain 4 digit year and be in the following format: dd/MM/yyyy eg 25/1/2006 if no date format is provided in the import file.
A date format pattern may be specified on each line of the import file if desired; define a field "date_format" and include a date pattern using the standard date patterns as specified for the java language SimpleDateFormat class.
If the file is generated from a software package such as your accounting package or a spreadsheet you should review the file using a simple text editor before uploading it to ensure it is properly formatted.

Liberty Accounts reserves the right to charge for any support required following a failure to adhere to the guidelines in this specification.

Field Names

The first line of the file should contain valid field names from the list below. Note the underscore character in place of spaces. It is not necessary to include every field in your upload file, just include those for which you have data. Note that where field data exceeds the maximum permitted size it will be truncated. Required fields are indicated with .

customer_name or supplier_name
title
first_name
last_name
address_line1
address_line2
address_line3
town
county
postcode
iso_country_code
iso_currency_code - customer upload only
email
phone
fax
vat_number
trading_terms_code
customer_code or supplier_code
bank_sort_code
bank_account_number
bank_account_name
balance
balance_date
is_member - customer upload only
membership_start - customer upload only
membership_end - customer upload only
membership_memo - customer upload only
date_format

Right click link to save CSV starter file containing all headings for customer uploads

Right click link to save CSV starter file containing all headings for supplier uploads

display_name or customer_name or supplier_name (text: max 60 characters) - Required

This field should contain the name of the business.

title (text: max 15 characters) - Optional

This field should contain a valid title from the list below. If the title supplied is not valid it will be set to null on the record.

Mr
Mrs
Miss
Ms
Cllr
Dame
Dr
Rev
Sir
Lady
The Hon
The Rt Hon

first_name (text: max 30 characters) - Optional

The first name of a contact in the business.

last_name (text: max 30 characters) - Optional

The last name of a contact in the business.

address_line1 (text: max 40 characters) - Optional

First line of business address.

address_line2 (text: max 40 characters) - Optional

Second line of business address.

address_line3 (text: max 40 characters) - Optional

Third line of business address.

town (text: max 25 characters) - Optional

Town of business address.

county (text: max 25 characters) - Optional

County of business address.

postcode (text: max 25 characters) - Optional

Postcode of business address.

iso_country_code (text: max 2 characters) - Required

This field should contain a valid ISO country code. This field is important because it is used in Liberty Accounts to assist in handling of VAT transactions. If the field is not defined the record will default to GB. If an invalid ISO code is presented the country will be set to "Not Specified".

Common ISO Country and Currency Codes
Country	Country Code	Currency Code
UK	GB	GBP
USA	US	USD
Belgium	BE	EUR
China	CN	CNY
France	FR	EUR/td>
Germany	DE	EUR/td>
Netherlands	NL	EUR

A full list of country codes is available from www.nationsonline.org/oneworld/countrycodes.htm

iso_currency_code (text: max 3 characters) - Required

This field is only relevant for customers where they are invoiced in a non-sterling currency. If present this field should contain a valid ISO currency code. If the field is not defined in the upload or if an invalid ISO code is presented the value will default to GBP. Suppliers are always set to GBP.

A full list of country currency codes is available from www.nationsonline.org/oneworld/currencies.htm

email (text: max 50 characters) - Optional

An email address.

phone (text: max 20 characters) - Optional

A phone number.

fax (text: max 20 characters) - Optional

A fax number.

vat_number (text: max 15 characters) - Optional

A VAT number.

trading_terms_code (text: max 3 characters) - Optional

This field will set default trading terms specific to the business. If a value is presented it should be one of the following 3 character codes where the numeric value indicates the terms in days. If an invalid code is presented the default terms will be set to null

000 (Due On Receipt)
005 (5 Days Net)
007 (7 Days Net)
010 (10 Days Net)
014 (14 Days Net)
015 (15 Days Net)
020 (20 Days Net)
021 (21 Days Net)
025 (25 Days Net)
028 (28 Days Net)
030 (30 Days Net)
035 (35 Days Net)
040 (40 Days Net)
045 (45 Days Net)
050 (50 Days Net)
055 (55 Days Net)
060 (60 Days Net)
090 (90 Days Net)
120 (120 Days Net)
150 (150 Days Net)
180 (180 Days Net)
998 (Last Day of Month)
999 (Last Day of Next Month)

user_defined_code or customer_code or supplier_code (text: max 20 characters) - Optional

A unique code that may be used to refer to the business within your organisation. Where you are importing sales documents via Bill or Invoice Upload this is code is used in your import to associate documents with the counterparty.

The code is normally issued by an external system that will later provide transactions relating to the customer or supplier for upload with the code being used to identify the customer or supplier in Liberty during the upload.

In some circumstances you may want Liberty to generate a code during the upload of a new customer or supplier. This can be achieved by entering a "+" in this field and Liberty will present the record ID as the code.

bank_sort_code (text: max 6 characters) - Optional

Should only contain the numeric sort code characters. If the uploaded data is more than 6 characters it will be truncated. Typically used sort code separators ("/","\","-") will be removed.

bank_account_number (text: max 8 characters) - Optional

Should only contain numeric characters. BACS files require 8 characters, so account numbers with less than 8 characters should have leading zeros e.g. 00123456. For accounts with more than 8 characters surplus characters should be removed from the left of the account number. If the uploaded data is more than 8 characters it will be truncated. If the uploaded data is less than 8 characters it will be left-padded with zeros. Typically used separators ("/","\","-") will be removed.

bank_account_name (text: max 18 characters) - Optional

If the uploaded data is more than 18 characters it will be truncated.

balance (numeric) - Optional

If a non-zero balance is presented an opening balance transaction will be created for the business.
If a non-zero balance is presented and the business is a cash vat business the opening balance transaction amount will be split into net/vat amounts based on the standard VAT rate.
The date of any opening balance transaction can be controlled using the "balance_date" field - see below.
Numeric values must not include comma formatting. Negative values are indicated with a "-" prefix.

balance_date (date formatted text) - Optional

If a non-zero balance value is presented this field will control the date of the opening balance transaction. If no balance_date is presented, or the value is invalid, the date of the upload will be used.
The default format for the date is: d/M/yyyy e.g. 25/1/2007
If an alternative date format is used the date_format pattern should be included in every line of the import file - see below.

is_member (text: max 5 characters) - Optional (customers only)

Should only contain either 'True','False','T','F','yes','no', 'y', 'n'.

Used to indicate a status of membership where relevant for Gift Aid purposes. Defaults to false if not provided.

membership_start (date formatted text) - Optional (customers only)

A date from which current membership or subscription starts

The default format for the date is: d/M/yyyy e.g. 25/1/2007
If an alternative date format is used the date_format pattern should be included in every line of the import file - see below.

membership_end (date formatted text) - Optional (customers only)

A date from which current membership or subscription is valid to