Hier
auch in deutscher Sprache
| C64 datatool -
Commodore 64 Spreadsheet
|
| Program: |  |
|
| Manual: |
Check http://members.chello.at/wiener.freiheit/datatool.htm for the latest version, if you use any other source. There is no version number, but a revision date. Latest date now is June 2003 (06/03).
The C64 is a very nice computer for solving trivial problems concerning calculation by yourself. Simply turn on the machine and write a little BASIC program for your needs. But if you want to provide some comfortable data editing options, the programming effort will grow considerably, and C64 BASIC does not support such a task very well. This was, why I started thinking about a program written in machine language for data management, in combination with free programmable BASIC functions for data processing. I searched for existing solutions, but couldnīt find anything of this kind.
As a hobby programmer, I write programs mainly for my own use. But this one could be useful for other C64 users too. Well, there are not much around, and few of them will have a field of application for such a program. Anyway, it is here, it is free, and if you see a benefit, then download and use it!
C64 Datatool is proper for inputting, editing and printing any kind of data in tabular form. In the simplest way of use, the program will store lists of words or numbers. Data processing (e.g. spreadsheet analysis, statistics or data conversion) can be programmed in BASIC. In the most advanced way of use, Datatool is nothing but a particular environment for your own BASIC programs.
For manuals and other docs concerning C64 BASIC please consult Project 64 Home page or iDOC= .
Finished, tested and frequently used, without observing serious faults. Many thanks to Tom Butz for improving the program text. Any corrections, remarks or suggestions are welcome, please mail to kottira@chello.at .
Changes / improvements in the new revision:
There are four files on disk:
All these program files can be loaded with or without secondary address. The loader autoboots if a secondary address is used, but however, all files are executable from BASIC memory with the command RUN .
The files are stored in a disk-image "datatool.d64"
and zipped, download datatool.zip
or datatool.d64.gz (16 kB)
If you want to store this document "datatool.htm" as
well, download datatool+m.zip
(110 kB) and extract it to a folder
The program is pretty much self-explaining. There is a main menu shown at the top. Select an item, either by using the cursor keys and RETURN, or by using the f-keys. You then will see a table-like user interface. Enter a cell by pushing RETURN. In order to store an entry, push RETURN again. In almost any case the CTRL-key calls a topical help screen, showing present options and keyboard commands. To step back one level, e.g. to cancel an input, use the BACK-ARROW-key at the top left of the keyboard. If you want to stop a running process, try the STOP-key. This will suspend BASIC execution, device access, printing and some time-consuming tasks. If you suspect the program to hang, you can use the combination STOP + RESTORE. This resets the program usually. During operations that require a rebuilding of the worksheet structure, the reset is locked because a disruption of this process would leave an invalid constitution. You may overcome this lock by hitting RESTORE three or more times - on the risk, to lose all your data.
View and Edit the WorksheetAt the top the worksheet there are a few items to adjust:
Example:
Letīs suppose, you want to enter a few numeric data in column A,
in order to calculate the square root of these data in column B.
First, set both columns to numeric type and enter the data in
column A. Then assign a variable name to column A, e.g. variable x, and another one to column B, e.g. y. Then you enter the BASIC statement y=sqr(x). When running the program code,
the content of the data in Column A will be delivered to variable x, the BASIC command will be executed, and
the content of variable y will be
stored into column B. This happens row by row. Afterwards the
worksheet looks like this:
At the left of each row thereīs a number, ranging from 001 to 999. Due to the memory restriction you will not be able to fill all 26 columns and 999 rows with data, but e.g. a worksheet with 3 numeric columns and 999 rows would be possible with reduced BASIC memory.
Next to the row number there is a cell that allows you to change the row type.
Example: in
addition to the example above, we want to calculate the sum and
the average of the data in column A and B. Because the variables x and y are
used to read and to store the data, we need different variables
to collect the sum, e.g. xs and ys. And, because BASIC has no direct
access to the number of rows in the worksheet, we need a counter
for that, letīs call it variable n.
The summation has to be done each data-row, therefore we add this
task to the column BASIC, which is executed row by row. In order
to show the sum, we have to transfer it to the assigned column
variables, x and
y in this case, and to provide a result-row. To calculate
and show the average, we furthermore have to divide by the value
of the counter n. Here is the result
after running:
Please also take note of the remarks on running the worksheet, describing what actually happens during a run.
Generally, there are two different modes: if you enter the worksheet through the main menu, the cursor moves from one cell to another, and the whole cell flashes. Letīs call it outline-mode. If you enter a cell by pressing RETURN, the cursor moves from one character to another, and only the character in cursor position flashes. Letīs call it input-mode.
Inputting and editing text in input-mode is
very similar to the way, the C64 screen editor works, except that
the repeat funktion is switched on for all keys. Additional there
is a little character buffer. If you DELete a character, which moves the cursor
backwards, the deleted character is saved to a buffer; also a
number of character (up to 250) will be saved, as long as they
are deleted continuously. To insert the deleted characters press C= DEL. This allows an undo of the last
deletion. Continuously means, that you are not allowed to use any
other key then DEL. Example: If you
delete the charakters "abc", move the cursor and delete
"xyz" on another place, then "abc" are lost,
and the buffer contains only "xyz".
If you want to copy some text part into the character buffer
without deleting it, use C= HOME, which
similar as the DEL-key moves the cursor
backwards - you have to start at the end of the part you want to
copy. And in case, you want to delete some text without
destroying the content of the buffer, use CLR
(SHIFT HOME), which acts like the DEL-key
usually does, but without feeding the buffer.
INST (SHIFT DEL) will insert a single space, which has no
effect on the character buffer.
In outline-mode there is a another buffer
available: It contains whole cells, columns or rows. If you want
to delete a single cell, move the cursor to it and press DEL. This will clear the cell, and save the
entry to the cellbuffer. Similar as described
above, C= DEL will insert an entry from
buffer, C= HOME will copy without
deleting, and CLR will delete without
copying. In the same way you can delete, copy and clear a whole
row or a column, if you move the cursor to the cell which
contains the column letter respectively the row number. If you
delete a column or a row, the following columns or rows will
move, in order to close the blank. In the opposite, you can
insert empty columns or rows using INST.
Please note, that DEL in outline-mode
affects the column or row in which the cursor is situated, which
is slightly different from the way it works in input-mode. And
please take into consideration, that buffer operations in
outline-mode imply a few restrictions. First, the buffer can hold
only one item, e.g. a single cell, a row or a column, and he can
only copy into the worksheet, if the destination has the same
format. Example: Itīs not possible to copy a numeric data-cell
via buffer to a text data-cell. And if you want to copy a numeric
column into an empty column, you must set the type of the empty
column to number before copying. Secound, copying rows or columns
will overwrite the destination cells only in the case that the
source cells in the buffer are filled. Consequently you can merge
partly filled rows or columns. If this effect is undesired, and
if the destination isnīt empty anyway, a row or column should be
cleared before pasting. Finally, be prepared that operationes
concerning columns or rows may require pretty much time, if the
worksheet is large.
There are a few useful key combinations: C= CRSR right, C= CRSR down, and C= RETURN. The topical help pages of the program, called by pressing CTRL, will tell more about it. The usage differs in outline-mode and in input-mode.
Run the WorksheetRunning a worksheet means, to execute all the BASIC commands you have stored in the worksheet. There are a few items to set first:
If you push RETURN at the Okay cell, the run will start. The system clears the screen and prints the number of the present pass. If you want more interaction during the run, you can prepare your column or row BASIC code and print any kind of information to the screen. Even input from screen respectively keyboard will work, if needed.
For writing proper BASIC instructions in your worksheet, it might be essential to understand how the system works, and how BASIC is executed. Here a brief description:
Finally, one remarkable difference exists, compared to usual BASIC programs: There is no superior stop-command, because the BASIC-sequences in the worksheet are run one after another, and if one is passed - either by end or by stop or by reaching the last BASIC-line of a sequence - the system continues running and calls the next sequence, until it passed the last row of the last pass, or an error occurs. If you want to interrupt the run on a certain condition, you will have to cause an error like: if [condition] then crash , which will stop with an error message. Another possibility is setting the I/O status different from 0, like if [condition] then poke 144,1 , which will stop without error message. Apart from that, the run can be manually stopped with the STOP key.
Search for TextThe search will be done in the worksheet, including all datas, text and settings, but not in any other item of the main menu. For example: you will be able to search for a BASIC text sequence stored somewhere in the worksheet, but not for a text sequence stored in the loading section, or somewhere else.
The instruction for searching text is limited to 80
characters; pass over will be ignored without error message.
There is no particular search for numbers and ranges, but you can
search for a number or a part of a number as well. Donīt search
for a certain layout, because the search inquires numbers in the
way they are shown in input-mode, without care about layout
setting.
No wildcard character is available. You only can search for the
(part) text you specify. Lower and higher case letters are
discriminated.
When Search is on the run, you move from one found subject to another using CRSR left/right. If you have choosen the Replace option, movement is the same, but in this case you may press RETURN in order to replace the text found by the text you set as replacement, and to move on forward. If the replacement text is empty, the text found will be deleted.
You always have the option to exit the search and to stay in the present cell by pressing the BACK-ARROW key.
Sort DataFirst, you have to choose between sorting from lower to higher value (ascend) or from higher to lover value (descent). Secound, you choose the column, that will be decisive, no matter whether Number or Text type. Then you may start.
The system will sort data-rows. If there are any other rows (e.g. BASIC, Res, Label, Pause) in between, the sorting process will not disturb this arrangement, but the data-rows will be sorted blockwise. Empty rows are sorted last, no matter which sort order is set. But if a row contains something, an empty decisive column will be counted as the very lowest value.
If you want to sort more then one level (representeted by several columns), you have to sort the lowest level first. The sorting routine will keep the rank as fare as the decisive column holds an equivalent value.
Be warned, that sorting a very large worksheet will take a good while. You can interrupt the process using the STOP key, without endangering the data.
Load FilesAn important restriction has to been told first: Datatool is unable to access tape, because memory locations for tape access are occupied by the system itself.
There are two different ways of inputting data from disk. The first one is to Load a native file, in case it was stored as a Datatool file before. You just have to key in the filename. In case the filename consists of spaces at the start or at the end, the length of the filename can be fixed with quotation marks, otherwise overhanging spaces will be cut. (If you donīt remember the filename exactly, press f8, view the directory and copy the filename using the buffers.) Then press RETURN at the Okay cell to load a worksheet, including all settings in all menu items. The system will check if the file is valid, and inform you if it isnīt.
Aside from loading a native file, you can Import data from any source on disk. This option simply consists of three BASIC code sequences. They will be executed in order to open a file, to input row data, and to close the file. Similar to running the worksheet, the opening code is executed once, the import row code is executed in any data-row or result-row from 1 to 999, unless the I/O status changes because the end of the file is reached or an error occurs. Finally the code for closing the file is executed.
| Expample: You want to
import the directory of your game disks, in order to
print the content somehow nice, to sort files by name, to
build up a database, or anything of that kind.
|
In the sequence for
opening a file, you open a channel for reading the directory "$" from disk, and fetch the
first two bytes of the loading address without using it.
The main task, for importing row data, uses
goto-statements, therefore you have to number the lines.
The lines numbered from 2 to 8 concern the way already existing
data in the worksheet are treated. If you want to collect more
then one directory in the worksheet, you have to consider, that
the code for importing rows is called from row number 1 to the
end of file. BASIC line 2 observes the present load of the
worksheet and counts the highest stored disk-/file number in
variable h. If a row is empty,
variable n will contain zero, and the
process of inputting the directory can start. Otherwise the code
sequence is aborted in line 4. Line 6 sets the new disk number to
the highest number counted until now, plus 1 for the new disk.
The lines of the directory, respectively the file numbers on one
single disk, are counted in variable f,
added in the two decimal places behind the point of variable n. At the first line of the directory,
containing the disk name, f will be
zero. The following directory entrys will be counted up in BASIC
line 8.
BASIC lines 10 to 90 are based on the program example in the 1541
floppy users guide. Line 10 passes two bytes, line 20 fetches two
bytes containing file length. Line 40 builds a numeric value
using the ASC-function and a zero
code in variable c$ to prevent an
error. Line 50 checks if the end of the directory is reached. If
yes, the disk-/file counter is cleared to the pure disk number,
the string "blocks free" is added, and the sequence is
ended. (You donīt have to bother about later, because the
changed I/O status will stop the repetition of the code
automatically.) Line 60 waits for the opening quotation mark,
line 70 collects the filename in variable
na$ until the closing quotation mark appears. Line 80
passes spaces, line 90 collects the file type in variable ty$.
This code sequence will be repeated while reading entry by entry
from the directory, and any entry is passed on to the worksheet
in the form of the variables n, na$, bl and ty$, that are stored to a new row each
time. If the I/O status changes, because the entire directory is
read, the BASIC-cell for closing the file is called. Well, this
one is easy: just close the open file.
 After importing a few directorys,
the worksheet may look like the screenshot on the left.
You are free now to process the data in any way you like.
(Hopefully your game disks are not too much, because at
about roughly 50 disks the memory will be filled.)This
example was quite elaborating, but it gives an idea of
the versatility of the input-option. You can import data
from a wordprocessor, for example, or you can set up
temporary files for exchanging data between worksheets,
using the export option described next. |
Save FilesSimilar to loading, you have the option to save a worksheet
including all settings as a Datatool program file, or to export
data to any destination, by using your own BASIC code. To Save
a worksheet, just type in the filename (you can use quotation
marks to fix the length of the filename) and press RETURN in the cell of your choice: save,
verify, or both. To overwrite an existing file, enter @: before the actual filename. The system
will then scratch the file on disk before saving (it will not use
the buggy floppy command to overwrite).
Worksheet files are stored as a "prg"-file, and you can
actually run them: If you start a session on the C64, you can
load a worksheet file and run it, as
well as you can load
"datatool",8,1 first, and the file afterwards.
The Export option is the counterpart to the import option described above. The opening code is executed once, the export row code is executed for every filled data-row or result-row in the worksheet (label-, pause or BASIC-rows are ignored), and finally the BASIC sequence for closing the file is called. You may use this option for transferring data to another program, e.g. a word processor, or for exchanging data between worksheets, but keep a few rules in mind:
The BASIC statements print#, get#, and input#, are quite comfortable for exchanging data, but a little bit tricky also. Here an overview:
| Strings | Numbers | |
| Send | print#2,x$ sends the content of x$ and a "carriage return" (CR = ASCII 13) afterwards, which tells the end of the variable. (You can supress the CR by adding a semicolon: print#2,x$; ) If the variable is supposed to be received with input# , it should not contain a comma, and it should not be empty (check empty string before: if x$="" then x$=" " ) | print#2,x sends
a number - not in any special numeric format, but it
converts it into a string of digits, ended with a CR. For sending single Bytes (0-255) you can convert the Byte in a single character and send the ASCII-code: print#2,chr$(x) |
| The print# statement can be used with several variables strung together, separated by semicolons or commas. The effect is that all variables will be sent without recognizable separation, loosing their allocation. This only might be useful in the case that variables consists of one single byte by agreement. Otherwise a single print# statement should be used for each variable. | ||
| Receive | get#2,x$ will fetch one single byte or character. The only exception is, that if the byte is zero, x$ wonīt contain a zero code, but will have zero length. If you want to do the ASC-function, you must calculate it as x=asc(x$+chr$(0)) in order to avoid a BASIC error. | get#2,x expects a single digit coded in ASCII. Any aberration will be prompted with an error message. Therefore this variation is seldom. In order to get a singe byte, use get#2,x$ and the ASC-funktion instead. |
| input#2,x$ will collect characters until a comma (ASCII 44) or a CR (ASCII 13) tells the end of the variable. The received string is not allowed to have zero length, and the maximum length is limited to 80 characters. | input#2,x acts like with string variables, but converts the collected string into a numeric variable. If the received characters donīt fit, it causes an error message. | |
| The get# and input# statements can be used with several variables strung together, separated by commas. This is no counterpart to the similar looking print# statement. The varaibles strung together will be received in the same way, as it would be with several single statements concerning one variable each. | ||
.
Print the WorksheetThe program offers no drivers for different sorts of printers, but still it has a few features for controlling the output. Anyway, the output consists of worksheet headline, comment, column title and a range of worksheet rows, except rows containing BASIC. Row number and row setting also will not be printed.
Print from / to row is updated by the system itself, but you can change it, of course.
Same with Print from / to column: The range
of printed columns is proposed by the system, but it can be
changed. Beside that, specific columns can be set not printable
in the worksheet setting.
You will have to watch the length of a line on the printer
output. To the width of every printable column one character has
to be added for the grid line (or space) between rows, plus one
character for the grid line at the very left. If your printer
allows it, the system will be able to output 160 characters each
line.
In the cell Column grid line a single character in ASCII code can be specified, that will be printed between the columns. If no value is set, a space will be printed instead.
In the cell Row grid line a character can be specified, that will be printed between the rows for all row length. If space (ASCII 32) is set, an empty row will be printed. If no value is set, there will be no extra line between the rows.
In the cell Intersection another character can be defined, that will be printed wherever the grid line of column and row intersect.
Perform / Reset headline allows to set a code (or a code sequence) that will be sent to the printer before and after printing the worksheet headline. If more then one ASCII code is needed, the codes have to be separated using SHIFT RETURN . If no value or zero is set, the cell has no function.
Similar Perform / Reset col. titles can be used to control the appearance of the column titles in the same way.
The cell Define print codes can be used to
assign a specific ASCII code to any character. There are a lot of
printers around, requirering different ASCII codes for non
standard characters. E.g. you can specify the actual printer code
for the special characters éäöüßÄÖÜ here. Please note,
that the shifted space is internally processed as ASCII 224, you
maybe have to set the common ASCII 160 here.
The input form requires, that you type in the referred character
first, and (with or without space) the ASCII code in numbers
afterwards. You are free to write a comment after the digits. You
must use a new line for each character and code number
using SHIFT RETURN . The system will
pass by failed syntax without error message.
If ASCII set IBM is selected, the characters a...z will be converted to ASCII code 97...122, and A...Z to 65...90, instead of the CBM standard in lower case, where a...z are represented by code 65...90 and A...Z by 193...218.
The cells BASIC open printer and BASIC close printer must be filled with an executable BASIC code. Printers have different needs and opportunities, concerning the opening procedure. Maybe you have to open several files in order to adjust your printer, and maybe you have to send a few commands first. In case you open more then one file, the output of the actual worksheet data will be sent to the file number that was opened or used last. The output itself canīt be controlled by BASIC, because it is a copy of the screen display. But there are several options anyway: You may use the export data option for sending data to a printer, as well as you may print to a file if you open a disk file instead of a printer.
System OptionsThis part of the main menu offers miscellaneous features. Run some by simply pushing RETURN in the left part of the screen, and others by providing input into the right part of the screen.
Error MessagesErrors come in several flavours: errors issued by the BASIC interpreter, drive errors and lastly system errors and warning messages.
System warning messages:
System error messages:
| Franz Kottira | kottira@chello.at | Frankieīs C64 Seite |
top
First, you
prepare the worksheet. You designate a sequence number
for disk and file in column A, assigned to variable n, numeric type, 6 characters
width, 2 fixed decimal places. It will hold the disk
number in the integer part, and the file number in the
decimal places. Furthermore variable
na$, which stores disk- and filename in column B;
variable bl containing the
number of blocks in column C; and finally variable ty$ for showing the file type in
Column D.