COMMA.APP -- File Import COMMA-Delimited COMMA.APP is a Symphony (TM) Add-In application that augments Symphony's standard File Import capabilities. It adds the option "Comma" to the normal choices of "Text" and "Structured" on the File Import menu. COMMA imports structured ASCII files onto a Symphony worksheet. It has the same general functions as File-Import-Structured. However, COMMA is often more directly applicable to importing data records than F-I-S, because of the following features: 1. Input files can have lines longer than 240 characters. 2. Commas which separate input fields are significant. In F-I-Structured, two or three commas between fields is treated exactly the same as a single comma, but COMMA obeys multiple commas as an indication to skip cells on the worksheet. This makes COMMA suitable for importing data from many BASIC or disk filing programs. 3. Unlike F-I-Structured, text does not have to be quoted for COMMA to import it as label cells onto the worksheet. 4. Input text can contain imbedded double-quote marks. 5. Label cells can be right-aligned or centered if desired. 6. Formulas can be entered, just as if they were typed in SHEET mode. In fact, COMMA can import any legal cell contents; repeating labels, DOC format lines, etc. 7. Cells can be formatted as they are being input. 8. Column widths can be set appropriately for the input data. 9. Cells can be entered in an order other than strict left-to-right and top-to-bottom. To make the facilities of the COMMA Add-In available to Symphony, use the Application Attach command to attach COMMA.APP at the beginning of the session. When COMMA is no longer needed, Application Detach can be used to remove it from Symphony's memory area. However, it may be convenient to keep COMMA attached while its functions are still needed. After COMMA.APP is attached to Symphony, it can be activated from the File Import menu by selecting [SERVICES] File Import Wide. You will be instructed to identify a file to be read, and then directed to indicate a target range for the import operation. The default file extension for the input file is presumed to be PRN, but the standard Symphony filename specification procedures can be used to identify a file in any directory and with any desired extension. The target import range can be specified by pointing with an anchored or unanchored cursor, by typing a cell address or pair of cell coordinates, or by providing a range name. If the target is specified as an anchored range, i.e. with upper-left and lower-right coordinates, then the import operation will be constrained to that range; no cells outside that range will be affected. If an unanchored range is specified, i.e. by giving the upper-left cell address only, then cells to the right and below the target address may be replaced by data from the input file. If global protection is enabled, however, then any attempt to overwrite protected cells will immediately terminate the import operation. COMMA will read files with any desired record length. The data from each record (line) is placed in a separate row on the Symphony worksheet. Each record is terminated by an ASCII Carriage Return (CR = 0Dh) and an optional Line Feed (LF = 0Ah) character. End of file can be indicated either by an ASCII End-Of-File (EOF = 1Ah) character or by DOS knowing exactly how many bytes long the file is. Each line of the input file can contain many fields. These fields have either cell data (text, numbers, or formulas) or else contain special instructions to COMMA which control the import process. Each data field in a record is placed in a separate column on the Symphony worksheet, unless a control command in the input stream directs COMMA to move the logical cursor to another column or row. Fields in the input file are separated by commas. Any number of blank spaces before or after field values are ignored. Data fields can be constructed as follows: 1. Text is optionally enclosed in matching double-quote (") characters. A text string must be quoted if it needs to contain an imbedded comma or leading or trailing blanks. A text field can be up to 240 characters long between the quotes or commas which delimit it. Each text field is placed into a Symphony cell as a left-aligned label. If a double-quote mark is needed as an imbedded character in a quoted text string, it should be indicated by a pair of double-quotes (""); unquoted text (which is delimited only by commas) can contain a double-quote character without any special preparation. Any tab characters (ASCII 09h) in the text will be expanded to blank spaces with tab settings assumed at every eight (8) positions. 2. Numbers are entered without any delimiter characters. A numeric field consists of a string of digits, with an optional leading dollar sign, an optional plus or minus sign, an optional imbedded decimal point, and optionally trailed by either a percent sign or an exponent. The standard Symphony rules for typing numbers into SHEET cells are applied. 3. Formulas are enclosed in matching square bracket ( [ ] ) characters. A formula field can be up to 240 characters long between the brackets, and two right-brackets ( ]] ) can signify a required one in the formula string. Each formula field is placed into a cell exactly as if it were typed into a Symphony SHEET cell. Imported formulas can refer to range names and cell addresses as usual. All references to cells by cell coordinates (A1, B2, ...) are treated as references relative to the upper-left corner of the target import range. If the contents of a bracketed string are not acceptable to Symphony as a formula (or a label cell or number cell) then the formula string is imported as a label cell prefaced by the string ERR> and left on the worksheet for subsequent editing. Note that brackets can be used to import label cells with leading left- (') right- (") or center-alignment (^) prefix characters. This allows more specific control over cell alignment than importing quoted text strings, which are always loaded as left-aligned labels. Also, formula brackets can be used to import repeating labels (with the \ prefix character) and special (prefixed by the | character) label cells. 4. If any input field is longer than 240 characters, then it is truncated at the 240-th character position without any warning message. If the final delimiter for the text (") or formula (]) field is not found before the end of the input line, then the entire field is ignored and the corresponding cell on the worksheet is not filled. In addition to the data values that COMMA can import, there are special command fields that can be used to control the import process. These fields are enclosed in curly brace ( { } ) characters. They provide the ability to specify the cell format of imported fields, to set the column width of columns in the import range, and to import the fields in orders other than strict row-per-line and column-per-field sequence. If an unknown command type is found between curly braces, or the input line ends (CR) before the right-brace (}) character is found to complete the command, then the command string is ignored in the input stream. In particular, note that an unquoted string which begins with a left-brace -- but is not intended to be used as a command -- may have its opening character(s) stripped by COMMA, which would mistake the unquoted string for a special control command. The following control strings are accepted, and must be enclosed in curly braces: COMMAND STRING PURPOSE ------- -------- ---------------------------------------- Home H Resets the import cursor to point to the upper-left corner of the import range. Right R Moves the import cursor one (or more) R ddd cells to the right, skipping over any data already on the sheet. Left L Moves the import cursor one (or more) L ddd cells to the left, skipping over any data already on the sheet. Down D Moves the import cursor one (or more) D dddd rows down, skipping over any data already on the sheet. Up U Moves the import cursor one (or more) U dddd rows up, skipping over any data already on the sheet. Width W ddd Sets the column width on the SHEET of the column targeted for the next field. Format F fdd A Sets the cell display format for cells imported after this command field. In the above list the notation dd, ddd, or dddd stands for a string of up to 2, 3, or 4 decimal digits used in the command. Any blank spaces between the command identifying letter and the parameters of the command are optional. Command letters may be in either upper or lower case (H or h, R or r, ...) but must immediately follow the left curly brace ({) character which begins the command field. The following are examples of valid command fields: {H} Moves the import cursor to the beginning of the range for the File Import COMMA operation. {R} Skips the cursor one cell to the right. {L 12} Moves 12 cloumns to the left. {D123 } Moves 123 rows down, skipping lines. {U 8193} Moves the cursor to the top line of the import range (see note below about large numbers). {W 15} Sets the column width to 15 characters. As indicated in the above example of the Up command, COMMA limits actions of the special commands to reasonable numbers. A Left operation can not back up to a column before the selected import range, and an Up can not move above the top import line. A value greater than 8192 for the Down command is treated as a move to row 8192, and a value above 256 for the Right command is treated as a move to column 256, but such locations are subject to being cut off because they are likely outside of the import range initially specified. Similarly, a column Width more than 240 will be ignored by Symphony. Aside from direct cursor movements ordered by the commands, they do not affect the current import cursor position. A command does not have to be followed by a comma, so the cell pointer will not necessarily be updated by the existence of a command in the input stream. This is especially useful so that the Width command and the Format command can be included with the data that they modify, in which case no comma should be placed between the command's closing right-brace character and the beginning of the data field value. The Format command field affects all data cells imported until the next Format command. The initial cell display format is Reset (with Prevent- Changes for protected cells). The Format command uses specifiers like those shown in the Symphony control panel for SHEET cells: e.g. C2 is Currency with 2 decimal places, T1 is a Time format, F0A indicates Fixed with zero decimals and Allow-Changes when global protection is on, etc. The following Format types are recognized: CODE EXAMPLE Symphony FORMAT ------- -------- ---------------------------------------- F0..15 1234.56 Fixed, with 0-15 decimal places S0..15 1.23E+03 Scientific, with 0-15 decimal places C0..15 $123.45 Currency, with 0-15 decimal places P0..15 1,234.56 Punctuated, with 0-15 decimal places %0..15 12.34% Percent (%), with 0-15 decimal places G 1234.56 General, like Fixed or Scientific D1..5 05-Sep Date, in 5 Symphony formats T1..4 10:36 AM Time, in 4 Symphony formats B ++++ Bar-Graph (+), horizontal graph L +A1-B2 Literal, formula text displayed H Hidden, no cell value displayed R 1234.563 Reset, cell display in default format The Format command begins with a left-brace and an F (or f), and then it contains one of the format type specifiers and an optional protection override flag. Spaces can be used to separate the format type -- one or two characters -- from the F command letter for clarity, but a two-character type specifier (e.g. C2) can not have imbedded spaces. In order to set the Allow-Changes flag for a cell, the Format command is specified with an "A" at the end of the format code (optionally preceded by spaces for clarity). If the "A" is not given, the default protection status (Prevent-Changes) is incorporated into the cell format. The following are examples of Format commands: {F F2} Fixed, with 2 decimal places. {FS07 } Scientific, 7 decimal places {F C0 A} Currency, no decimals, Allow-Changes {FP12} Punctuated, with 12 decimal places {F %0} Percent, no decimal places {FG} General format {F D1 } Date format 1 (DD-MMM-YY) {FT2} Time format 2 (HH:MM:SS AM/PM) {F BA } Bar-Graph, Allow-Changes {F L } Literal, formula text instead of value {FH} Hidden, no cell value displayed {F R} Reset, back to default cell format {FRA} Reset, but with Allow_changes flag All of the format codes may be specified in either upper case or lower case alphabetic characters. If an unknown format code is specified, the Format command is ignored. If no Format command is given, or if the Reset code is specified, then subsequent imported cells adopt the default format of the Symphony SHEET window currently active. The Format command does not affect the default format status of Symphony. The following example illustrates several of the features of File Import COMMA. If the ASCII file shown is imported to a range beginning at E21, the Symphony SHEET window will appear as indicated. INPUT (In this representation of the ASCII import file, CRLF indicates the end of an input line, i.e. Carriage-Return Line-Feed, and EOF indicates the End-Of-File mark.) {W18}Region, {W12} ["Income], ["Expense]{w 12} , {W12 }["Profit]CRLF [\-],,,[\-],{F p2} CRLF West Coast, 12345.67, 11e3, [+b3-c3]CRLF """Newest"" States" ,,50000,[+b4-c4] CRLF "East Coast" {r} 98765, 12345%, [+B5-C5] CRLF CRLF [^TOTAL]{r 3}{f C2}[@sum(D3..d5)] EOF OUTPUT |-------------E--------------F-----------G-----------H---------I----| 21 Region Income Expense Profit | 22 ------------------ ------------ | 23 West Coast 12,345.67 11,000.00 1,345.67 | 24 "Newest" States 50,000.00 (50,000.00) | 25 East Coast 98,765.00 123.45 98,641.55 | 26 | 27 TOTAL $49,987.22 | 28 | |===================================================================|