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 one of two "Upload Modes" must Be selected:

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

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

Right click link to save CSV starter file containing all headings

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.

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

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 third party.

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

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

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_memo (text: max 200 characters) - Optional (customers only)

Notes relating to membership or subscription as required.

date_format (text: no max specified) - Optional

If using date formats different from the expected default a pattern must be supplied using the date format patterns as specified for Java SimpleDateFormat class.
d - day
M - month - N.B. Capital M for month (small m for minutes)
y - year

e.g.
ddMMyyyy - 25022007
d/M/yyyy e.g. 25/1/2007


Related Topics