HiFinance
5
SECTION
3.5.6.I.1.4 – FUEL BUY PRICES MASTER-FILE ASCII IMPORT
USAGE: To import a file
containing, all or a subset, of new or changed information to be posted to the Fuel
Buy Prices master-file. The input file is required to be in a very strictly
stylised format so that HiFinance can allocate data to the correct fields on
the correct Inventory records.
DISCUSSION: Make sure that you have a good backup of your data
and that no-one is using HiFinance during the update. HiFinance may not update
the files as you expected and the only completely safe way to correct the
mistake is to restore your data from backup.
Typical file
TGP,27/06/2020,,,,,,,,,,
Supplier,Depot,SULP,SDIS,PLUS,SPRE98,SPRE,PREDIS,ADBLue,LPG,P85,Ethanol
TGP,SYDNEY,,,,,,,,,,
TGP,MELBOURNE,95.77,95.15,95.98,118.08,108.49,,,,,
LIST,HOBART,113.24,115.47,114.72,126.02,,,,,,
TGP,HOBART(TGP),101.5,100.19,105.41,116.82,110.4,,,,,
LIST,ADELAIDE,101.35,100.67,101.8,113.7,107.49,,,,,
TGP,ADELAIDE(TGP),96.48,95.41,96.93,119.15,103.89,,,,,
TGP,COOGEE,95.05,94.71,,118.43,103.36,95.71,,,,
LIST,COOGEE(LIST),93.16,93.57,,116.42,109.76,94.57,,,,
LIST,DARWIN,107.14,104.56,107.76,,116.15,,,,,
LIST,BRISBANE,,,,,,,,106.98,,
???????????????????? not written yet.
The import file required must be called stkmf.csv, although case is not important in Windows. This
file must reside in your data directory, eg \HF5DATA\COMP1. Also, the file must not be in use. This is
important because if you have created the file from a spreadsheet and have
saved it but not closed it, the file will still be locked by the spreadsheet
program and HiFinance will not be able to open it.
The input file must be in CSV format. This is a
comma-delimited file (without quotes) with one record per line.
Exporting from Excel, choosing the CVS format, will produce the correct format
automatically. Each alphanumeric field must be left justified. HiFinance will
make no attempt to remove leading spaces (for names, etc.). Numeric fields
should not have any dollar signs or commas. Do not use commas in any
alpha fields. These will be treated as a field break and HiFinance will
not import the row correctly. A blank cell will be treated as a valid entry,
thus a blank alpha cell will clear the corresponding field in the master-file
and a blank numeric cell will reset the corresponding field in the master-file
to zero. Thus if you do not wish to change a field, either delete the row,
delete the column or make sure that the cell contains the same information as
the field in the master-file currently has.
The first line of the input file must contain only
field names. These fields are mostly identical to those exported using INVENTORY
ASCII EXPORT (see SECTION 3.5.4.1). In fact
the easiest way to create the initial file is to export it first using this
program with the headings option turned on. This also has the advantage of
accurately exporting the current information. There are some fields available
in the Import program that are not created in the Export program. The full list
is detailed below. Note the use of the underscore character instead of spaces.
This format must be copied exactly.
CODE
RENAME_TO
COPY_TO
BARCODE
DESCRIPTION_LINE_1
DESCRIPTION_LINE_2
UNIT_OF_MEASURE
PRICE_1
PRICE_2
PRICE_3
PRICE_4
PRICE_5
PRICE_6
PRICE_7
PRICE_8
STANDARD_PRICE
LAST_PRICE
TAX_CODE
INVENTORY_GROUP
OTHER_KEY1
OTHER_KEY2
OTHER_KEY3
RE-ORDER_POINT
MAX-STOCK_POINT
LEAD_TIME
QTY_DISCOUNT_UNITS
QTY_DISCOUNT_%
UNIT_WEIGHT_(KG)
UNIT HEIGHT_(MM)
UNIT WIDTH__(MM)
UNIT DEPTH__(MM)
CUBIC_METRES_-_INNER
CUBIC_METRES_-_OUTER
CARTON_QTY_-_INNER
CARTON_QTY_-_OUTER
BARCODE_-_INNER
BARCODE_-_OUTER
NUMBER_OF_LABELS
QTY_ON_ROW
QTY_ON_PALLET
DISCOUNT_TYPE
SERIAL_NUMBER
TARRIF_ITEM
FOREIGN_CURRENCY
CUSTOMS_RATE%
FREIGHT_FACTOR%
PAGE#
LAST_PURCH_PRICE_FC
OBSOLETE
USE_CURR_PRICES
NON-DEC.STOCK
NON-DEC. G/L ACCOUNT
Notes:
1)
The order of the columns is not important but they must not appear more
than once.
2)
CODE must appear, as it is used to link the row
to the Inventory Code as it currently exists on the Inventory master-file.
3)
Apart from CODE, you must have at least one more column.
4)
RENAME_TO is only required if you wish HiFinance to change
the current Inventory code to another code. The new code must not exist and the
old code will not exist after the import program has run. If you wish to switch
two codes, it is possible to do this by entering three lines. The first row
will change one of the codes to a dummy value, the second row will rename the
second inventory code to the first code and the third row will rename the first
code to the second, eg ONE ®
XXX; TWO ®
ONE; XXX ®
TWO.
5)
COPY_TO is used when you wish to duplicate Inventory items.
Sometimes you have a group of codes that you wish to enter again, eg for
export. This option can have other fields changed at the same time but it will
change the original code’s fields, so normally this option is only used by
itself.
6)
RENAME_TO and COPY_TO can be used together, but
care should be taken to achieve the results you expect.
7)
OTHER_KEY’s are replaced by whatever you have defined it as
in SYSTEM DEFAULTS.
8)
DISCOUNT_TYPE and SERIAL_NUMBER are mutually exclusive, the
former is available if you are using the Discount Table option.
9)
Some fields are only relevant to specific installations.
10)
If you try to import the raw file form the ASCII EXPORT program,
it will not read in successfully unless you choose to export with comma
delimiters between fields and without quotes around fields (numeric and
alphanumeric). This can be set in the options part of the export program.
Normally this is not the way this function is used, the purpose of this
function being to manipulate the file in a spreadsheet program and importing
the result.
11)
Most fields are not verified on import. You must enter valid information.
For the field that are validated, invalid data will be ignored.
12)
NON-DEC.STOCK and NON-DEC. G/L ACCOUNT are normally used in a pair.
It is acceptable to leave out NON-DEC. G/L ACCOUNT if you are only setting the flag to N. If you have both fields
and you set the flag to N, the account must be zero or blank. If the flag is set to Y, there must be a valid
General Ledger account entered and it should be a posting-type account.
When the program starts, it will firstly check
whether the import file exists and is accessible. Having established this,
HiFinance will read the first line of the file and check for its correctness.
If no obvious errors are found, you will be asked to confirm that you have made
a backup and then you will be asked to confirm that you wish to proceed.
HiFinance will then read import lines one at a time
confirming firstly that the CODE field corresponds to an Inventory code and then
renaming, copying or just replacing the fields and records, as defined by the
columns you have chosen. If an invalid or blank row is encountered, it will be
skipped. HiFinance will continue processing until it reaches the end of the
import program. Finally the import file will be renamed to stkmf.old (to stop you from
accidentally rerunning the program with used data) before returning you the MAIN
MENU.
