C64 datatool - Commodore 64 Spreadsheet

Hier auch in deutscher Sprache


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


General

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

top

Program Status

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:

top

Download

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


top

Manual

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.

top

View and Edit the Worksheet

Column settings:

At 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:

Row settings:

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.

Editing the worksheet - buffer operations:

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.

top

Run the Worksheet

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

top

Search for Text

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

top

Sort Data

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

top

Load Files

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

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.
After the design of the worksheet is settled, you turn towards the code for inputting data from disk.


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.
Please keep in mind that importing data, unlike loading a native file, will not change any existing data in the worksheet, until the variable assigned to a specific column is changed by your import-row-code. You can add data to an existing worksheet by input from disk any time, but you have to be careful concerning the handling of variables assigned to columns.

top

Save Files

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

.

top

Print the Worksheet

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

top

System Options

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

top

Error Messages

Errors 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:

top


Franz Kottira kottira@chello.at Frankieīs C64 Seite