HiFinance
5
APPENDIX B - FORMATTING
ASCII EXPORT FIELDS
Virtually all files in HiFinance can be exported to
ASCII. This is a standard format understood by all utility programs, such as
Spreadsheets, Databases and Word-processors. This gives you the flexibility to
format reports, etc. in ways not otherwise available in HiFinance.
ASCII files are defined as flat files containing
only uncompressed, alphanumeric data, one record per line with <CR> or <CR> <LF> at the end of each line. Within this structure it
is still possible to choose the format of each field. Each program within
HiFinance allows you to access a screen, which sets the defaults for these
options. The screen is always asked for and can be returned to by pressing <F4>.
It is advised that you give the export file a name
ending in .CSV to make programs such as
Excel automatically understand the format. It is possible to call them what you
like but you have to go through extra steps to import them into programs like
Excel. The default name is normally the data file name with .CSV replacing .DAT
and the directory initially defaults to your normal data directory. You can
change it from this, as required.
When using DOS based installations (including
MS-Windows) below Win98, you should limit the names of directories to 8
characters without any imbedded spaces. This is a limitation in some versions
of Windows but is not a limitation in Unix. Unix and WindowsXP, and above can
handle long file names. If HiFinance finds that the operating system rejects
the name, an error will be displayed and you will need to start the export
procedure again. In either case, do not imbed spaces. HiFinance will truncate
any characters after the first space it finds as spaces confuse most operating
systems - even the ones that purport to work correctly with long file names
(including Windows and Unix). Also, there are some
restrictions on special characters. These include ()\&”’. etc.
The exact list depends on your operating system.
It is not advisable to save the export into the root
directory. This can clutter the file system and cause errors in other programs.
Create a new directory (folder) and save the export to that. Eg a valid but
poor name would be c:\EXPORT.csv a better name would be c:\dload\EXPORT.csv. Remember to create c:\dload first.
Typically the screen will appear as follows:
The list of options is as follows:
FILE NAME This field contains the name of the file you wish to
create. The default name is #<FILENAME>.CSV (where # is replaced by the data directory name, eg. H:\HF5DATA\COMP1\ and <FILENAME> is normally the source
filename as it exists in the data directory.). However, this can be changed to
any valid DOS (or UNIX) path name. Be very careful if you enter a name suffixed
by DAT, KEY, BAT, EXE or COM as it is possible to overwrite normal files used by HiFinance. In a
DOS environment, it is also possible to choose a name such as PRN or COM1 so that the export will be
sent directly to a printer or a communications port. HiFinance will
automatically truncate the name from the first space encountered, so do not
leave embedded spaces or leading spaces in the name. HiFinance will warn you if
the file you are about to create already exists.
If you have entered an
impossible name, HiFinance will display an error message. You should go back
and check that the path you have entered actually exists. Make sure that the
drive name exists. If you have entered a relative path, you should be careful
that it points to a real directory. It is best to enter absolute path name (ie the full path name, starting from the drive letter, in
Windows) as then there is no confusion. Furthermore, the file you are trying to
create must not be in use by a different program or user. A common mistake is
to open a CSV file in Excel and forget to close it before trying to re-export.
In this case, Excel will lock the file and HiFinance, being unable to write to
it, will display an error. Windows cannot distinguish between when a file is
impossible or locked. Therefore the same error message is displayed by
HiFinance for each of these conditions vis: ILLEGAL ASCII FILE-NAME OR DIRECTORY-NAME, OR THE
FILE IS OPEN ELSEWHERE - PRESS <ESC>.
SURROUND ALPHA FIELDS
WITH QUOTATION MARKS Some applications require
quotation marks " around alpha-numeric
fields, eg. names. To force HiFinance to do this,
enter a Y here.
SURROUND NUMERIC
FIELDS WITH QUOTATION MARKS Some applications require
quotation marks " around numeric fields, eg. dollar values. To force HiFinance to do this, enter a Y.
SEPARATE FIELDS
WITH A SPACE If it is necessary to leave
a space between fields, enter Y here, otherwise HiFinance
will not leave spaces between export fields. This function is sometimes
referred to as space delimited. It is
rarely used by itself.
SEPARATE FIELDS
WITH A COMMA Most word-processor and
spreadsheet programs require a comma between each successive field. This is
referred to as comma delimited. If
your application does not require a comma, enter N here.
REMOVE REDUNDANT
SPACES FROM ALPHA FIELDS Word-processors normally
accept alphanumeric fields blindly. This can leave unnecessary spaces in a mail
merge. To avoid this, enter Y here. HiFinance will then
remove all leading and trailing spaces from alphanumeric fields when they are
export. This procedure is more time consuming and should not be used if it is
not required. Furthermore, some older spreadsheet programs, eg. LOTUS V2.2 become confused by varying field lengths. For these
programs, you must always answer N.
REMOVE REDUNDANT
SPACES FROM NUMERIC FIELDS This question is similar to
the previous one but relates to numeric fields only, eg. dollar
values. As with the previous question, you should normally answer Y for word-processors and N for spreadsheets.
SHOW ALL DIGITS IN
DATE FIELDS Certain spreadsheet programs, eg
Excel V7 and above, have a bug that causes them to read 1/01/2001 as an alpha-field rather than as a date. To work
around this bug, HiFinance can be told to export the field with all leading
digits exposed, ie 01/01/2001.
If you experience this problem, answer Y in for this option,
otherwise it is preferable to leave it as N.
INCLUDE HEADING IN
FIRST LINE OF EXPORT If you intend to use the
output for mail-merge purposes, it is preferable to head each output with the
name of the field. HiFinance will export a unique, and consistent, name for you
to use, if you enter a Y here. For spreadsheet
programs, the headings do not usually get in the way.
EXPORT LONG NAMES
IN HEADINGS This option is only relevant
if you have answered Y to the previous field. The
screen that follows this one in the export program is a list of all possible
fields. These appear as short (maximum 20 characters) name, which is the normal
name, and a sometimes much longer, more descriptive name. For instance, period
dates may have LAST YEAR BALANCE 5 as the short name and BALANCE NOVEMBER
OF LAST-YEAR as the long name. Typically you would use the short name in exports
because the heading will not change from export to export. If you wish to
export the long name, answer Y here. Note that if you
intend to use the ASCII IMPORT function, you should use
the short name option.
Note that the options you choose are specific to the
file you are exporting, and your USERCODE. You can thus choose specific
export options for, say General Ledger Transactions
and different options for Creditor Transactions. Also, the options you choose
(eg file name) are independent of anyone else logging in with another USERCODE. It should also be noted that
your choices are saved and become the initial choices (for you) next time you
wish to export the same file.
Hint: Normally, HiFinance displays and prints
negative numbers with the sign trailing. This is the standard accounting
convention (other than putting negatives in brackets). The purpose of training signes is to a) avoid clashing with the dollar sign and b)
to make them more visible when printed in columns. Unfortunately, Excel gets
totally confused when presented with this format. Thus, HiFinance will export
numeric fields with the sign leading (ie on the left
of the number).
Hint: Some fields are poorly handled by Excel. This
specifically relates to long numbers and numbers starting with a zero.
Unfortunately, there are several fields that do this. They included: phone
numbers, barcodes, etc. These fields will have the leading zeros removed and
might be transformed into scientific notation. (Probably not
the desired result.) If you intend to export these fields, it is best to
use a suffix other than .CSV. Choose .TXT or .DAT, etc. This will force Excel
to ask you how to handle the fields. You can then force Excel to import these
columns as text fields, thus avoiding the issue,
