DBFACC - Data Base File ACCess The DBFACC program translates between AnalytiCalc save files (called .PCC files in the program), DIF (Data Interchange Format) files, and special database files and allows merger of multiple PCC or DIF files (or combinations) into database files or extraction of PCC files, with possible reorganization, from database files. Database files are sequential files with the following contents, all characters for simpler processing by other programs: 8 digits - ID key of file. This is a cell ID, unique in each spreadsheet file. When merging multiple files, a base may be added to the ID calculated for each saved sheet to form this number, so this field can be made unique for all saved sheets. 4 digits - Column. This field holds the column number (1 to a maximum of 999 handled by this program) in the original spread sheet. 4 digits - Row. This field holds the row number (1 to 999 legal) in the original spreadsheet. 8 characters - Alternate Key. This field holds the alternate key that DBFACC asks the user for when converting each DIF or PCC file into a database file. Its contents are arbitrary but the user's field will be there as text, left justified in the field. 109 characters - Formula or number. This area holds either the original spreadsheet formula or value. If a value, it is present as a printable value in E format, with a decimal point and normally some leading spaces. The number will appear approximately as 0.34560000000000E+02 for 34.56, and similarly for other numbers. A leading - sign may be present if the number is negative. 9 characters - Display format. This will be the FORTRAN display format used to display the value in the cell, with no enclosing parentheses, left justified and space filled. A common value for this field will be F9.2 with trailing spaces to fill out 9 characters. 1 character - Encoded FVLD and ITYPE codes. The character is computed by the formula CHR=32+(ITYPE*4)+FVLD where ITYPE=2 for floating point number =4 for integer number and FVLD = -3 for numeric-only text = -2 ditto = -1 for alphanumeric text = 0 for empty cells (never will be seen) = +1 for alphanumeric formulas = +2 for numeric formulas already calculated = +3 for numeric formulas, not evaluated yet These fields can be used as needed. The program will ask whether to store values or formulas if reading PCC files. The selection will determine whether the 109-character "Formula" field contains values of cells, or the textual formulas from the cells. In the case of DIF files, the DIF format already allows only one or the other, and whatever is found will be used there. In the case of DIF files, also, since no display format is available, the user will be asked to enter one by DBFACC. It is suggested that a legal Fortran format like F9.2 be used. The D10.4 format is also usable and can display any computable number, so it will also do as a reply there. The following describes the program's other functions. DIF Read / Write / DB ccess The DBFACC program is able to convert DIF files to AnalytiCalc (PortaCalc) save files, or to convert AnalytiCalc save files to DIF files. Conventionally, AnalytiCalc save files are named file.PCC and DIF files are named file.DIF. The program's prompts use this convention. The program can also generate database files usable by Datatrieve (or other databases) in a sequential database file from either PCC or DIF files. See the section on DATABASE FILES below for discussion. The program is also capable of extracting PCC files from databases. See the Extract section below. To run the program, just RUN DBFACC on VAX or PDP11; on PC, just type DBFACC. The progam asks whether you want to Read DIF files to PCC files or Write DIF files from PCC files. Reply R or W to select one. For database files it allows two more options, D and P to write databases from DIF or PCC files respectively. It will ask more questions if these options are chosen. You may also reply X to extract a PCC file from a database file. If you chose R or W, the program then asks for the DIF filename and the PCC (AnalytiCalc) filename to use. Enter each in response to the prompt. If you said you wanted to Write DIF files, the program asks whether to emit Values or Formulas. DIF is less general than the normal AnalytiCalc save output, and the program can either select the numerical value of each cell for the DIF file (useful for graphing programs and the like) or the cell formula (useful for moving to other spreadsheet type programs or for archival purposes). Select F or V. In a sense, the formula save is more general since it can be made again into a spreadsheet and once computable cells are made numeric again, the values can be recalculated. However, both are available. Use whichever you need. If you said you wanted to Read DIF files (and write a file for AnalytiCalc), the DIF format does not preserve the formatting information for cells. The program therefore asks for a default format to use. This can be any 9 characters of Fortran format you like. Use the F9.2 format (i.e., reply "F9.2", without the quotes) if you are uncertain of what to use. This format will be placed in each cell of the AnalytiCalc saved sheet. Some processing done by AnalytiCalc to flag numeric-only cells is not done here, so spreadsheets loaded by this utility will compute more slowly than native AnalytiCalc ones will. Dummy EDits in AnalytiCalc to any cells that are pure numeric will reset the flags so that speedups can be realized. It should be further noted that the DIF format is rather inefficient in that it assumes that the area to be saved is a complete rectangular region. DBFACC will fill in any cells that are not really there with the NA numeric code, and on loading back to an AnalytiCalc file, will discard all NA cells. The result is that DIF saved sheets can be considerably larger than AnalytiCalc saved files. All cells are treated equally here and the resulting DIF files contain either number values or string values. This limitation of DIF format is the reason why you must be asked whether you want formulas or values from your AnalytiCalc saved sheet. The two cannot be piggybacked. Nevertheless, this utility provides a fairly easy to use way of converting AnalytiCalc data for other programs, or of getting data from other utilities in a form that AnalytiCalc can load. One final note: AnalytiCalc V18-04F is the first version of AnalytiCalc that will work with this utility in converting AnalytiCalc files to DIF files. If you have old .PCC files, reload them and save them with the new AnalytiCalc to get this to work. The cells used to be saved down columns first; now they are saved across rows first. This has no effect on AnalytiCalc since each record has its address, but makes conversion to DIF of the old files quite difficult, since DIF files are specified to be saved across rows. Glenn Everhart 8/9/85 DATABASE FILES The DBFACC program, for VAX,PDP11, and PC, allows you to create files suitable for use as a database domain. You enter this mode by replying D or P to the question about reading or writing DIF files. A reply of D means read a DIF file onto a database, and a reply of P means read a PCC (PortaCalc) file onto a database. When you tell the program you want one of these options, it first enters a customization section for some extra data. The belief is that you may want to combine data from many saved spreadsheets into a domain. Since this data will have to be distinguishable somehow for you to keep track of it, you are asked to enter an auxiliary key of up to 8 characters. You can use numeric characters here (and in a DBMS) and treat this field as numeric, or any alphas and treat it as an alphanumeric key in a DBMS. The program will accept any 8 characters and make them a secondary key in addition to the ones it has already. The program computes a primary index from row and column numbers by the formula Index=(row number-1)*1024 + column number and uses that as the database file's primary index. This index may not have duplicates. To allow multiple sheets to be combined, the program asks for a cell ID base to use. This is read with a format permitting up to 8 digits and should be large enough to ensure cell IDs from the new sheet don't conflict with those from the old ones in your file if any. You may reply 0. If your reply is nonzero and below 65536 in absolute value, the program will warn you that conflicts could occur and give you a chance to confirm or deny you want the value you picked. If you reply N, the program exits and you can run it again without damaging your files. You must be sure there will be no conflicts. The program then asks for the filename of the input file and the output file. Once you enter the name of the database output file, the program will ask you to tell whether it's a new or old file. If it's an old file, an existing database file will be opened for update and added to. The program will then ask for the additional information needed for complete information in the file. It will ask whether to write values or formulas if a PCC file, or for a default display format if a DIF file. Then it will perform the desired file write and exit. On VMS, console reads are from SYS$INPUT and writes are to SYS$OUTPUT to allow batch or command file control. On the PC, reads and writes are to CON, the MSDOS Console device. Database Extraction (X option) If you said you wanted option X, the program asks you for the names of your PCC and database files. Because you may want to extract a PCC file only from one of the files that may have been merged into a data file for a DBMS, the program asks for an auxiliary key as it did when creating the database file. Only records in the data file matching that key are written to the output. However, if you reply with a * to that question, all records in the database file will be selected. The program next asks you for a title line for the save (PCC) file. Just enter any desired title. It then extracts the data and creates (recreates) the .PCC file from the data file. Note the type of file recreated is similar to the result of an AnalytiCalc PPF or PPN command, depending on whether Formulas or Values were selected in the pass that initially created the database file. If in the X option you entered a * as the auxiliary key, all auxiliary keys are selected. If however you enter a % or a %* you enter the "Squash PCC File" section of the program. The idea is that if you have 40 spreadsheets that have 50 numbers each that you saved and you want to be able to summarize them more simply, you might want to reshape the storage so that you have a sheet of 50 columns and 40 rows, with each row a complete spreadsheet save and each column one of the numbers. Then you can use graphics tools and the like to display variations of the numbers with respect to one another. When you enter %*, the key is replaced by just * to select all auxiliary keys. If you enter anything other than * after the %, the program asks you again for an auxiliary key. Then the program asks for the dimensions of the area of the spreadsheet to be created. (In the example, we'd say 50 columns and 40 rows.) You enter the numbers; the program will be happy with any so long as they are greater than zero. Now, as in the normal dump, DBFACC reads the database file in the order of cell IDs and writes a PCC file. However, instead of using the original row and column numbers, it makes new ones starting at A1, B1, C1, D1, ..., A2, B2, C2, D2, ... going across for as many columns as you specified and down as many rows as you specified (or less if it runs out of data first). The original cell coordinates are ignored. (This is likely to be useless for formulas of course, but where the data is just values, it can be handy.) To ensure that this produces the result spoken of, it would only be necessary to ensure that the second sheet's cell IDs were larger than any in the first, the third's larger than any in the second, and so on. This is done by specifying a large ID base to add. If there are 100 rows or columns in the sheets, one might add 1000000 for the first sheet, 2000000 for the second, and so on by specifying those as the ID bases to use. The IDs should normally not overlap since a real DBMS will want some unique key to use. However, when you specify an Old file for a database file to DBFACC, it will just append new data to the end of the old file, and later read sequentially, so keeping cell IDs unique is not really needed here. It is a good idea to do it anyway though, since that will allow the file to be used in other database management packages without requiring the DBMS to be sophisticated enough to allow duplicate keys. Since the ID field occurs first, a simple sort (using the MSDOS utility or the DECUS C sort utility for example) can be used most effectively if the ID fields do not overlap from one sheet saved to another.