Quickload User Guide Version 1.5 (C) Copyright Sheepware Associates, 1985 All Rights Reserved USER SUPPORTED SOFTWARE You are encouraged to copy and share this program with others. Try Quickload and, if you find it valuable, please send a contribution to: Sheepware Associates PO Box 6133 FDR Station NY, NY 10150 The concept of user supported software is based on the belief that honest people will pay a fair price for software they can get something out of. When we receive your payment with your name and address, we will include you in our base of registered users and send you a registration number. If you have any questions about using Quickload in special situations or need to know something of a technical nature, write us at the above address. Be sure to include your name, address, phone number, and registration number; WE WILL ONLY ANSWER QUERIES WITH A VALID REGISTRATION NUMBER AND WE WILL ADDRESS THE REPLY TO THE INDIVIDUAL WHO REGISTERED. If the inquiry is complicated or if you are local, we may call instead. In addition, there is an enhanced version of Quickload in the works with many new features. Registered users will be notified when the new release is available and will be able to upgrade at a special price. We have no plans to market the enhanced version under the user supported concept. Acknowledgements: 1-2-3 is a trademark of Lotus Development Corp. Symphony is a trademark of Lotus Development Corp. Word is a trademark of Microsoft Corporation dBASEII is a trademark of Ashton Tate Corp. Knowledgeman is a trademark of Micro Data Base Systems Inc. Powerbase is a trademark of Powerbase Systems Inc. PC-File III is a trademark of Buttonware R:BASE is a trademark of Microrim Inc. Page 2 Quickload Table of Contents General Information .................................... 4 Some Definitions ....................................... 5 Getting Started ........................................ 8 Giving Quickload Instructions .......................... 9 Special Features ....................................... 11 Limitations ............................................ 13 Using Quickload with 1-2-3(1) .......................... 14 Using Quickload with Microsoft Word(2) ................. 15 Using Quickload with Other Applications ................ 15 Questions and Answers .................................. 15 Appendix I: Creating an Instruction-file with DOS ...... 17 Appendix II: Some Applications That Can Read CSV Files . 18 ____________________ (1) 1-2-3 is a trademark of Lotus Development Corp. (2) Word is a trademark of Microsoft Inc. Page 3 General Information Quickload is a translator that converts plain text files into a format you can load into spreadsheets, databases, word processors (for mail merge), BASIC programs, and other kinds of applications on your PC. When Quickload is finished translating a plain text file, the result is a file in comma separated variable (CSV) format. Many applications that cannot read information in plain text format but can read and use information in CSV format. Incidentally, the letters CSV refer to the format of the information in the file, not the filename extension. 1-2-3, for example, will only read a CSV file if it has a filename extension of PRN. Quickload is fast, accurate, versatile, and allows you to create and save instruction files that tell it how to convert files for you. These instruciton files can be used over and over again--eliminating the need for you to retype them each time you use Quickload. Page 4 Some Definitions Plain Text Format What is a plain text file? Generally speaking, anything you can list on your screen or print on your printer (where you can read the results) is a text file. If you try to list a file with an extension of COM or EXE to your screen, you will see a lot of unrecognizable characters, the cursor will jump around on the screen, and your computer will beep at you furiously. EXE and COM files are not text files: they are set up for the computer, not people, to read. Text files come from word processors, editors, spreadsheets and application programs that are supposed to generate things you can read. Another common way to get text files that need translation is to transfer information from one computer to another. If you use a timesharing service, dial up a remote database or service, or download information from a mainframe computer to your PC for processing, you probably get the information in text format. Field The term field comes from the world of databases. Suppose you had a file of names and phone numbers like the example below: 0 1 2 3 4 5 12345678901234567890123456789012345678901234567890 Jane Hathaway 656-8894 Mark Vreeland 556-8785 Howard Lakeland 556-0988 Patty West 556-2340 Bob Farrell 776-9090 ..... ..... This file is made up of two fields--one for the name and one for the phone number. As you can see by the 'ruler' above the list, the first field, name, starts in the first column (or print position, if you were to print it) of the file, and the second field, phone number, begins in column 26. From this example, you've probably guessed that a field is just a piece of information that means something by itself. It could be a single number, like 5000.07, or a word, like "Whizbang," or a series of words, like "Jane Hathaway." When translated by Quickload, each Page 5 field in each line becomes a new cell in a spreadsheet or a new field in a database. As another example, the table below is an excerpt from an income statement; the kind you might get by dialing up a financial data base. ..................................................... XYZ Corp - Annual Income Statement ($thousands) 1984 1983 1982 1981 1979 ---- ---- ---- ---- ---- Operating Revenue 705 644 670 632 619 Operating Expense 650 632 628 599 595 Operating Income 55 12 42 33 24 Other Income 6 0 0 0 0 Income Before Taxes 61 12 42 33 24 ..................................................... Each line in the table above can be broken down into pieces called fields. For example, the line for Operating Revenue can be broken into a field for the label 'Operating Revenue' itself, plus five other fields, one for each of the numbers in the columns for 1979 through 1984. Look at the table below with the six fields marked by vertical bars. Field 1------------------Fld2--Fld3--Fld4--Fld5--Fld6-- ....................................................... | | | | | | | | XYZ Corp - Annual Incom|e St|ateme|nt ($|thous|ands)| | | | | | | | | |1984| 1983| 1982| 1981| 1979| | |----| ----| ----| ----| ----| |Operating Revenue | 705| 644| 670| 632| 619| |Operating Expense | 650| 632| 628| 599| 595| |Operating Income | 55| 12| 42| 33| 24| |Other Income | 6| 0| 0| 0| 0| |Income Before Taxes | 61| 12| 42| 33| 24| |........................|....|.....|.....|.....|.....| In the table above, the first field tells what each line item is, the second field is the line item's 1984 result, the third field is the line item's 1983 result, etc. What about the top line - the one beginning with "XYZ Corp?" Quickload is designed to translate information lined up in columns - not free form labels. After translation, the top label would probably be chopped up. If you have a word processor or editor, you can delete these long labels from the from-file before using Quickload. Deleting long labels beforehand is Page 6 not necessary with spreadsheets like 1-2-3 because you can delete them easily when they are in the worksheet. If you can't find a convenient way to delete the labels, try converting them anyway. It can't hurt the computer and you will probably be able to delete them from within the application program. Page 7 Getting Started To invoke Quickload, make sure the program diskette is in your default drive(3) and type in: QL to begin. When Quickload is ready, it will ask you for the name of the from-file, the to-file, and the instruction-file. The from-file is the name of the file containing the plain text you want Quickload to read from. The to-file is the CSV file you want Quickload to write to - this is where the results go. The instruction-file is a file you have created to tell Quickload how you want to process the from-file. It describes each field in the from-file. The next section shows you how to set up an instruction-file. You can also give Quickload the names of the files it will work with on the DOS command line. You do this by typing in the file names when you run Quickload. For example, to designate file1.txt as the from-file, file1.prn as the to-file, and file1.ins as the instruction-file, you would type in: QL file1.txt file1.prn file1.ins and Quickload would take care of the entire translation without further action on your part. If you use Quickload like this, remember to type in QL followed by a space, then the name of the from-file, then the name of the to-file, then the name of the instruction-file. You must use all three filenames and THEY MUST BE IN ORDER. ____________________ (3) Or, if you are using DOS version 2 or higher, you can put Quickload in any directory on your path. Quickload recognizes and uses pathnames. Page 8 Giving Quickload Instructions To process a from-file, Quickload needs to know two things about each field: (1) how long it is and (2) whether it is a number field or a character (alphabetic) field. This is the information you put into the instruction file. You can create an instruction file using a word processor, an editor, or just by using DOS(4). Instruction-files are easy to put together. For example, this is what the instruction-files for the phone number list and income statement we looked at before would look like: Phone List: c24 name c8 number Income Statement: c24 line-item n4 1984-results n4 1983-results n4 1982-results n4 1981-results n4 1979-results An instruction-file has one line in it for each field. The first character in the line is called the field type and is either a 'c', which indicates a character field, or an 'n', which indicates a number field. The field type may be in either upper or lower case. Immediately after that is a number indicating the length of the field. Notice in the first example that we've made the phone number a character field instead of a number field. We had to do this because the dash is not a digit and your applicaiton program would not know what to do with it. There is no problem converting a field with numbers in it to a character field. After that are one or more spaces followed by a notation of what the field represents. The note is optional, but Quickload will create a 1-2-3 column heading with it if you want (see the section called: Tips on Using Quickload With 1-2-3). ____________________ (4) See Appendix 1 for an explanation of how to create a file using DOS. Page 9 If you go back to the income statement example, you will see the line item field is 24 characters long, and each of the number fields are 4 characters long. Fields that you indicate with an 'n' will be translated as number fields. In your spreadsheet, for example, they will appear in their cells as regular numbers and may be used in formulas. Fields that you indicate with a 'c' are entered as labels and therefore cannot be used in formulas. Page 10 Special Features Quickload does more than just separate your fields by commas. It also compresses your files so they take up less space when loaded into your application. Character fields are stripped of trailing blanks and number fields are converted into the smallest number of characters possible. When Quickload translates number fields, it recognizes and properly handles parentheses, trailing credit indicators, dollar signs, fractions, and commas. In addition to the 'n' and 'c' field types, you can also designate fields as 'b' (blank) or 's' (skip). A blank field type will cause Quickload to print out the indicated number of blanks (spaces). A skip field type causes Quickload to skip over or ignore that number of characters. While many other conversion utilities require you to type in your instructions each time you translate a file, Quickload allows you to save your instruction-files. That way, if you have a file you need to translate periodically, you only have to write the instruction-file once. The -t Option The -t option instructs Quickload to prepare titles for the to-file. It reads the titles from your instruction-file and sets them on a separate line in the to-file. For example, suppose you invest in a portfolio of 20 stocks and, on a weekly basis, dial up a stock quote service to get the current stock prices through your modem. When you receive the stock prices, they look something like this: SYMBOL....YIELD.....HIGH......LOW......CLOSE JTT .36 25 23 24 INT .72 26 5/8 24 3/4 25 ... ... (18 other stock quotes)... ... Assume further that your instruction-file looks like this: c10 symbol n10 yield n10 high n10 low n10 close Then with the -t option, Quickload will prepare your file so that the words 'symbol', 'yield', etc appear as column titles in your 1-2-3 worksheet. You use the -t option by entering a command like the one below. Because 1-2-3 likes its titles to be quoted, we use the -tq version of the -t option like this: Page 11 QL -tq from-file to-file instruction-file The quoted version causes Quickload to put all titles in double quotes on the first line of the to-file: just where 1-2-3 needs them. Because some applications prefer to see the titles plain (unquoted), there is also a plain version (you guessed it: the -tp version). Microsoft Word, for example, uses titles to assign field names for merge documents. Word requires plain titles. The -w Option Quickload also uses another option: the -w option causes Quickload to put each field on a separate line by itself and skip an additional line at the end of every record. Some applications require that each field be on a separate line, so using the -w option will provide you with that kind of format. The -w option comes in only one version, and, as with the -t option, you use it when first loading Quickload: QL -w [from-file] [to-file] [instruction-file] The resulting file will be in the -w (one field per line) format. Page 12 Limitations Maximum line size (from-file) .............. 1000 chars Maximum line size (instruction-file) ....... 256 chars Maximum number of fields ................... 100 fields Maximum field length ....................... 1000 chars Page 13 Using Quickload With 1-2-3 1. If you are converting a file for use with 1-2-3, fields that you describe to Quickload as 'character' type will appear as labels while fields that you describe as 'numeric' type will appear as true numbers. Any field, even one containing only digits, can be described to Quickload as a character field. But remember that when character fields are loaded into 1-2-3, they appear as labels and cannot be used in formulas. 2. If you are converting number fields with dollar signs, commas, and other special characters, those characters will be eliminated from the CSV file that 1-2-3 reads. This is because 1-2-3 expects numbers to be in plain vanilla form when it reads in a file. Once the numbers are in the spreadsheet, you can use the R(ange) F(ormat) command to display them with the special characters again. If the numbers will not be used in formulas, you can also describe them to Quickload as character fields. The only characters removed from a character field are trailing blanks. 3. There is a special option you can use with Quickload that will convert the field name notations in the instruction-file to column headings in your worksheet. To use the option, type this in at the DOS prompt: QL -tq [from-file] [to-file] [instruction-file] The 't' is for titles, and causes Quickload to make column titles out of the words you put to the right of the field descriptions in your instruction-file. The specifications of the from, to, and instruction files are optional: if you don't include them on the command line, Quickload will ask you for them later. 4. To load a CSV file into 1-2-3, follow these steps: * Follow the Quickload instructions above for converting the file into CSV format. Be sure to give the to-file (the one in CSV format) an extension of PRN. For example, let's assume you have a file called EMPLOYEE.TXT and you wanted to name the to-file EMPLOYEE.PRN. * Put EMPLOYEE.PRN into the directory where you keep your worksheet files. If you don't use directories, put EMPLOYEE.PRN on the disk where 1-2-3 normally looks for worksheet files. * Load 1-2-3. Page 14 * Move the cursor to the cell that you want to become the upper right corner of the worksheet. * Load the file using the F(ile) I(mport) N(umbers) command. You will see the name of all files that end with PRN appear in 1-2-3's command area; choose EMPLOYEE and press the key. * Once the file has been loaded, treat it as any other worksheet. When you save it using the F(ile) S(ave) command, 1-2-3 will store it in its own special format and give the file an extension of WKS. Thereafter, use the F(ile) R(etrieve) command to load it. You may then delete the .PRN file if you want. Using Quickload with Microsoft Word Quickload prepares files in the format Word requires for merge documents. Prepare the instruction-file as you would for any other kind of application and put the merge field names to the right of the field lengths in the instruciton-file. When you run Quickload, use the -tp option to generate all the field names in the merge file automatically. Using Quickload with Other Applications A great many application packages can read files in CSV format and there are two basic 'styles' of CSV. The first style is the default for Quickload and consists of a single line of output for each line of input. The second style is available using the -w option and consists of one line of output for each input field with an extra blank line at the end of each input line. The first style is generally used with data base packages, spreadsheets, and the like. The second is most commonly used with word processors (the exception is Microsoft Word). Questions and Answers What does Quickload do to the file? It adds two kinds of formatting characters to help applications like BASIC and 1-2-3 interpret the information in the file. It also removes certain characters from the fields to compress the file so it takes up less space in your application program. Quickload removes trailing blanks Page 15 from character fields and commas, dollar signs, parentheses, etc. from number fields. How long will it take to translate a file into CSV format? Quickload is very fast. The most time consuming operations it performs are reading from and writing to your disk - the actual translation takes almost no time at all. To keep you informed, Quickload reports to you after reading the instruction-file and after every 100th line it has written in the to-file. Which applications can read files in the CSV format? Quite a few! Although most application programs use their own special format for efficiency, many can read a CSV file and convert it to their own format. A partial list of applications that read CSV files is in Appendix 2. I use a data base manager that reads text files directly. What do I need Quickload for? Many new applications read text files directly, but they dont do all that Quickload does. For example, some spreadsheets read text files but don't know how to handle fractions--they only read the whole number and disregard the rest. Some data base managers read text files but make no effort to compress character fields to remove trailing blanks like Quickload does. Failure to do so makes your data base files much larger than they should be and can mess things up when you try to work with the character fields. For example, if you ask your data base manager to search for a record with the name 'Smith' in it, will it stop when it finds the word 'Smith ' (with 10 blanks appended to it)? Many won't. Page 16 Appendix 1 - Creating an instruction-file With DOS Creating an instruction-file using DOS is easy. Conceptually, you start by telling DOS you want to create a disk file, then you type in the contents of the file and tell DOS when you are finished so that it can save what you've typed to the disk. For example, to create a file named file1.ins do this: From the A> prompt (or B> or C> depending on which drive you are logged onto) type in: copy con: file1.ins Then press the key. You've just told DOS you want it to copy all the characters from the con: (short for console - that's what DOS calls the keyboard) to a file called file1.ins. The cursor will jump down a line and wait for you to type in the file. If your from-file had three fields like first name, last name, and phone number, you might key in: c10 first name c15 last name c13 phone number After typing in each line, press the key to begin the next line. When you have finished, tell DOS you're through by pressing the F6 key (on the left side of the keyboard) followed by the key. After pressing F6, you will see the characters ^Z appear. When you press the key, the disk drive will begin to spin and DOS will issue the message: 1 File(s) Copied You have now finished creating the file. To check it, key in: type file1.ins and press the key. DOS will display the contents of file1.ins. Page 17 Appendix 2 - Some Applications That Can Read CSV Files Lotus 1-2-3 Microsoft BASIC Lotus Symphony Microsoft Word (merge files) dBASE Powerbase Knowledgeman PC-File III R:BASE 5000 Page 18 Page 19