| Article #51 |
| Importing Lots From Excel |
Requirements of import file:
* You can use Microsoft Excel or any other spreadsheet program that can
Generate XLS format or Comma Delimited CSV files
* It is recommended that sellers is created prior to this import
* Each row in the Excel sheet is a Lot
* The import is always done from the sheet named "Sheet1"
* SORT your Lots in Excel before importing
* The headings should be in caps
* Do not use any quotes (single-quote, apostrophe or double-quote) in your values:
O'Brian should be O Brian or OBrian
* The first 8 rows are used to determine the type of a column (text or numeric):
* When mixing text and numeric values, prefix it all values with an apostrophe, E.g. Lot 1A: '1A
* If a numeric value is zero, specify zero, do not leave it blank
* ENSURE YOUR LOT NRs ARE UNIQUE.
NOTE: Ensure when saving your spreadsheet in Office 2007 or higher that you save the file as *.xls
Select the event and right-click on the vendueroll and click on
Advanced > Import Lots From XLS>
Right-Click
Select XLS file - Select an Excel format file
Select CSV file - select a CSV format file
Customers - Open customer maintenance form
Item - Open item maintenance form
Close XLS - Close the link to the Excel file
Column Headings
LOT
The Lot number,
If not specified, will run in sequence from 1
Do not mix letters and digits, always use numbers here
To sort correctly rather use digits e.g. 1A, 1B and 1C should be 1.1, 1.2 and 1.3
SELLCODE
Seller Customer Number for this lot.
You will be prompt to link lot to a customer
(If the customer does not exist, you will fist need to create it)
You can use the name for this column
Use either SELLER or SELLCODE, never both
SELLCODE is preferred to SELLER
The system will stop to notify you what the code is not linked to a customer yet,
And prompt you with the Customer Lookup Form Here you can select the customer
(or create a new customer before selecting the customer)
SELLER
Seller Customer Number for this lot.
You will be prompt to create customer if not on the system
Use the Customer Code or abbreviation in the sheet, not the name,
E.g. "WK1" for Wihan Kotze, "JW2" for John Walters
If not specified, "SELF" is assumed
Use either SELLER or SELLCODE, never both
The system will stop to notify you what it cannot find the code in the customer database.
Once you click OK the system will automatically take you to the
"Add new customer" form. Here you can capture the customer info.
ITEM
Item Code for this lot.
If not specified, default item is assumed
QTY
Lot Quantity.
If not specified, "1" is assumed
Take note that quantity is multiplied by amount to get lot total.
Make sure if the lot is sold as ONE item, or multiple quantities.
A gap will be left for each lot where quantity is more than one.
This is to ensure that there is a space, if there are multiple buyers,
And we want to split the lots later,
E.g. Lot 100 to 100A, 100B and 100C
RESERVE
Reserve price for this lot.
If not specified, "0.00" is assumed
TYPE
Lot description
E.g. table, chair, Bonsmara, Toyota Corolla 160GL 1998.
ID
Lot identification, Registration nr, serial nr, etc.
E.g. ABC123GP, SN478393.
Additional columns:
VAT
Vat Status of the lot
If "Y" then Item LIV will be forced.
If "N" then Item LIZ will be forced.
ITEMVAT
VAT% for the item.
If not specified, the system default rate is assumed.
Help to auto-create an item vat status on the first row.
NOTE
Additional info to print on invoice for this lot.
COMMB
Lot Buyer Commission%.
If not specified, Default "EVENT COMM% BUYERS" assumed
COMMS
Lot Seller Commission%.
If not specified, Default "EVENT COMM% SELLER" assumed
BAM
Buyer Animal Mark.
QTY1
Lot Offspring Quantity. E.g. 1 cow and 1 calf.
If not specified, "0" is assumed
MASS
Lot Mass.
If not specified, "0" is assumed
BUYER
Buyer Customer Number for this lot.
You will be prompt to create customer if not on the system
Use the Customer Code or abbreviation in the sheet, not the name,
E.g. "WK1" for Wihan Kotze, "JW2" for John Walters
If not specified, "NS" (No Sale) is assumed
PRICE
Selling price for this lot
If not specified, "0.00" is assumed
Take note that quantity is multiplied by amount to get lot total.
REF
External Reference for this lot
COST
Cost price for this lot
START
Bidding Start price for this lot
NOTE
Additional info
TYPE1, TYPE2, TYPE3
Append to TYPE
ID1, ID2, ID3
Append to ID
GRV
Goods Received Voucher
VNOTE
Vendueroll note
STC1
The STC code
Max gaps per Lot - This will leave a few spaces for each lot if there are multiple buyers on that lot
Zero pad Lot - will prefix the lot with zeros
NOTES ON VAT:
ITEMS:
Each lot is linked to an item:
If a lot is a zero-rate item NO VAT is payable to the seller
If a lot is a zero-rate item NO VAT is paid by the buyer
LIZ is a built-in zero-rate item
LIV is a built-in standard-rate item
SELLERS:
A seller will only be paid VAT if that seller is registered
AND the item is standard-rate
BUYERS:
*Global Setting: SELECT YES/NO
Buyer will pay VAT if YES and the item is standard-rate
*Global Setting: SELLER STATUS
Buyer will pay VAT if seller is registered and the item is standard-rate
*Global Setting: ALWAYS
Buyer will always pay VAT, except if item is zero-rate.
FOR VEHICLES USE:
LOT,
SELLCODE, (SELLER)
RESERVE,
ITEM (MAKE/USE STOCK ITEM "V")
TRADE (fill in 0 if zero)
RETAIL (fill in 0 if zero)
MAKE
MODEL
REGNO
VIN
COLOR
KP (Keys/papers)
COND (Condition)
ODO (odometer in km)
EXTRA
ANOTES (Addiotional notes)
INCOMPLETE DATA - CALL WIHAN
