WIDE.APP -- File Import Structured WIDE WIDE.APP is a Symphony (TM) Add-In application that augments Symphony's standard File Import capabilities. It adds the option "Wide" to the normal choices of "Text" and "Structured" on the File Import menu. WIDE imports structured ASCII files onto a Symphony worksheet. It has the same general functions as File-Import-Structured. In addition, WIDE provides the following features: 1. Input files can have lines longer than 240 characters. 2. Formulas can be entered, just as if they were typed in SHEET mode. 3. Input text can contain imbedded double-quote marks. 4. Label cells can be right-aligned or centered if desired. 5. Cells can be formatted as they are being input. 6. Column widths can be set appropriate for the input data. 7. Cells can be entered in an order other than strict left-to-right top-to-bottom. To make the facilities of the WIDE Add-In available to Symphony, use the Application Attach command to attach WIDE.APP at the beginning of the session. When WIDE is no longer needed, Application Detach can be used to remove it from Symphony's memory area. However, it may be convenient to keep WIDE attached while its functions may still be needed. After WIDE.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. WIDE 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 WIDE 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 WIDE to move the logical cursor to another column or row. Fields in the input file are separated by any number of spaces, commas, or almost any other delimiter characters. Data fields can be constructed as follows: 1. Text is enclosed in matching double-quote (") characters. A text field can be up to 240 characters long between the quotes. Each text field is placed into a cell as a left- aligned label. If a double-quote mark is needed as an imbedded in the text string, it should be indicated by a pair of double-quote characters (""). 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 minus sign, an optional 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. 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 characters 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. 4. If any input field is longer than 240 characters, then it is truncated at the 240-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 WIDE 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 column-per-field sequence. 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 right curly brace must be found on the same input line or the command will be ignored. The following are examples of valid command fields, for all except the Format command which is explained later: {H} Moves the import cursor to the beginning of the range for the File Import Wide operation. {H } Same as above (extraneous blanks ignored). {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 for the next targeted SHEET column to 15 characters. As indicated in the above example of the Up command, WIDE 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. 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 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. 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 imported cells will be assigned 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 WIDE. If the ASCII file shown is imported to a range beginning at E1, 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] {W12},["Expense] , {W12} ["Profit] CRLF [\-]{R}{r}[\-]{F p2}CRLF "West Coast",12345.67 11e3 [+b3-c3] CRLF """Newest"" States" {R}50000[+b4-c4]CRLF "East Coast", 98765, 12345%, [+B5-C5] CRLF CRLF [^TOTAL] {r 2} {f C2},[@sum(D3..d5)] EOF OUTPUT |-------------E--------------F-----------G-----------H---------I----| 1 Region Income Expense Profit | 2 ------------------ ------------ | 3 West Coast 12,345.67 11,000.00 1,345.67 | 4 "Newest" States 50,000.00 (50,000.00) | 5 East Coast 98,765.00 123.45 98,641.55 | 6 | 7 TOTAL $49,987.22 | | | |===================================================================|