Quickload User Guide Version 1.6 (C) Copyright Sheepware Associates, 1985, 1986 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 and would like to become a registered user, please send a $20 contribution to: George Roukas Sheepware Associates PO Box 6133 FDR Station NY, NY 10150 Please make checks payable to George Roukas. 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 reduced price. 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 Notice of changes from version 1.5 to 1.6: 1. Version 1.5 contained a bug that caused it to exit with an error when converting fractions into decimals. This has been fixed in version 1.6. 2. In keeping with the desire to use the minimum space for the CSV file, fractions that are converted to decimal numbers now have all trailing zeros removed. For example, 15 1/2 previously converted to 15.500000; now it appears as 15.5. 3. Version 1.5 scanned the From-File for tab characters and exited with a message if it found them. It did so under the assumption that tabs would play havoc with the format of the data. Version 1.6 converts tab stops internally: it expands them to standard 8 position tab stops on the fly. 4. There is a new command line option (-l) to set the line length when reading From-Files that have no carriage return/line feeds at the end of each line. When you give Quickload the -l option, it knows that all lines are the same size and that there will be no carriage returns or linefeeds in the From-File. This usually occurs when you are getting data from a mini or mainframe computer that uses the same size line for all records. For example, to use the new option with a file having a fixed record length of 90 characters you would enter: ql -l90 From-File To-File Instruction-File This causes Quickload to read the From-File in 90 character chunks--assuming, again, that there are no carriage returns or line feeds in the From-File. Page 3 Quickload Table of Contents General Information ..................................... 5 Some Definitions ........................................ 6 Getting Started ......................................... 9 Giving Quickload Instructions ........................... 10 Special Features ........................................ 12 Limitations ............................................. 15 Using Quickload with 1-2-3(1) ........................... 16 Using Quickload with Microsoft Word(2) .................. 17 Using Quickload with Other Applications ................. 17 Questions and Answers ................................... 18 Appendix I: Creating an Instruction-File ................ 20 Appendix II: Some Applications That Can Read CSV Files .. 22 Appendix III: Quickload Error Messages .................. 23 ____________________ (1) 1-2-3 is a trademark of Lotus Development Corp. (2) Word is a trademark of Microsoft Inc. Page 4 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 5 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. Page 6 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 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 Page 7 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 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 8 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 9 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: For the Phone List: c24 name c8 number For the 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 number 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. ____________________ (4) See Appendix 1 for an explanation of how to create a file using DOS. Page 10 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). 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 11 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 Page 12 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: 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's -w option causes it 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 you invoke Quickload: QL -w [From-File] [To-File] [Instruction-File] The resulting file will be in the -w (one field per line) format. The -l Option The -l option is for processing files with fixed length records that have no end-of-line markers (carriage return- line feed) to separate each record. Use it like this: QL -lnnn From-File To-File Instruction-File Where the -l option causes QL to look for fixed length records, and nnn is the number of characters in each record. Example QL -l80 infile.txt outfile.prn insfile.fmt Page 13 This causes QL to read infile.txt 80 characters at a time. Each 80 character chunk is treated as a single line and divided into fields as if there were a carriage return/line feed delimiter at the 81st position. When using the -l option, be sure that there are no carriage returns or linefeeds in the From-File. QL collects a line of information by reading until it gets nnn characters OR it finds a CR/LF delimiter, whichever comes first. Some methods of preparing fixed length data files include a header with the file. The header is just a small amount of information that appears at the start of the file; it contains data on the structure of the file. Because it has no information about the format or size of the header, the Quickload -l option assumes the From-File consists of data only--there should be no header on it. Page 14 Limitations Here are the practical maximums that Quickload can handle. We hope this is liberal enough to provide for most needs but, if not, please let us know. 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 15 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 Page 16 directories, put EMPLOYEE.PRN on the disk where 1-2-3 normally looks for worksheet files. * Load 1-2-3. * 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). Page 17 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 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. You may notice that Quickload requires a little more time if you are converting a file with fractions--the arithmetic is all done with 15 digits of accuracy and it does take a little longer than straight translation. 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. Or they exit with an error. 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 Page 18 'Smith ' (with 10 blanks appended to it)? Many won't. Quickload removes the blanks so you load only the significant information. Page 19 Appendix 1 - Creating an Instruction-File There are several ways to create Instruction-Files, the simplest is by using a word processor (in a non-document mode) or a text editor. If you do not have a word processor or editor, two other ways to make an Instruction-File are by using DOS directly or by using 1-2-3, if you have a copy. The main points to remember when making an Instruction-File are that there should be one line per field in the From-File and that there should be no margin of blank spaces to the left of each line in the Instruction-File. Using 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. Page 20 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. Using 1-2-3 Creating an Instruction-File with 1-2-3 is simple. Follow these steps: 1. Load 1-2-3 and bring up an empty worksheet. 2. Beginning in column A, enter the field types and lengths, one cell for each field. In column B, you may optionally enter column titles. It might look something like this: (Column A) (Column B) c10 first name c15 last name c13 phone number 3. Print the worksheet to disk using the P(rint) F(ile) command. While in the P(rint) menu, be sure to set the O(ption) M(argin) L(eft) to zero and use O(ption) O(ther) U(nformatted) so 1-2-3 doesn't include any headers or page breaks in the Instruction-File. Page 21 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 22 Appendix 3 - Quickload Error Messages 1. Can't open from-file: [filename] - translation ended Quickload can't find or open the from-file you specified. Make sure the file is on the disk and has the exact name you specified. If you gave Quickload a path name, be sure it is a valid one. 2. Can't open to-file: [filename] - translation ended Quickload can't find or open the to-file you specified. Make sure the file is on the disk and has the exact name you specified. If you gave Quickload a path name, be sure it is a valid one. 3. Can't open instruction-file: [filename] - translation ended Quickload can't find or open the instruction-file you specified. Make sure the file is on the disk and has the exact name you specified. If you gave Quickload a path name, be sure it is a valid one. 4. Bad field TYPE in instruction-file - must be N, B, S, or C Check the field type you used in the instruction-file. Make sure it is one of the above. 5. Bad field LENGTH in instruction-file - must be a number. Quickload could not interpret your field length specification as a valid number. Check the instruction-file again and make sure the field lengths are composed of digits only. 6. Line length from instruction-file is too big: [size] - exceeds maximum of [max] characters. The total of all field lengths in the instruction-file added to a line length greater than Quickload's maximum. The [max] number in the error message indicates the maximum line size available in your version of Quickload. Page 23 7. Too many fields per line: [num] - exceeds maximum of [max]. The total number of fields in the instruction-file was greater than the maximum Quickload allows (the [max] number in the message.) 8. Ran out of memory - translation ended. This indicates that Quickload found enough available memory to begin the conversion but not enough to finish. To conserve memory, Quickload starts with the mimimum it can use; if it needs more memory, it will ask DOS for it. This error occurs when DOS cannot find enough unused memory to give to Quickload. If this message appears, try to de-install any memory resident programs you may be using (like a spooler or ram disk) and run Quickload again. If you do not have any memory resident utilities installed, you will have to use a machine with more memory to use Quickload. 9. Found tabs in from-file - translation ended. This message appears only in verions prior to 1.6. It indicates that tabs were found in the from-file and they must be expanded to blank spaces before conversion. 10. Error writing to [filename] This error indicates that DOS would not let Quickload write to [filename]. The reasons vary, but it might be that the disk is full, the drive door is open, or that the disk is damaged. Page 24