"SBase Personal - 21. IMPORT" 21 IMPORT Overview When to Import dBase Files Uses of the Import command Import File Types ASCII Files ASCII Delimited Files ASCII Delimited File Separators Quotation Marks ASCII Fixed Length Files Converting Files Converting ASCII Delimited Files Converting Database Files Converting Spreadsheet Files Merging Files General Considerations Merging ASCII Files Using Process Import 21 IMPORT Overview There are only two ways in which data can be entered into SBase's own format. The first way is manually through the keyboard. The second way is automatically, through use of the Import command. Import is selected from the Process menu and it is also available as a DML command in SBase Professional 4. As computers become more and more a part of business and professional life, so the amount and variety of data stored on computers keeps on growing. Much of this data is readily accessible to you via the Import command. Data files can be downloaded from telecommunications-based information services, transferred directly via modem from another computer, or via floppy disk or magtape. Most information providers are now offering services based on at least one of these methods. Data can also be captured automatically from documents using an optical reader or scanner combined with character recognition software. This technique, called OCR, typically converts bit-mapped input into ASCII characters. Using the Import facilities offered by SBase 4, data obtained in any one of these ways can normally be converted into the SBase 4 format. You can then use SBase 4 to carry out searches and selections on it, reformat all or part of it, integrate it with other data and use it in your own database application. When to Import dBase Files In addition to its own format, SBase 4 can view and report directly on data maintained externally in dBase II or dBase III format. SBase 4 cannot update data in dBase format, only data stored in its own format. This safeguard is necessary in order to prevent SBase 4 users from inadvertently violating data integrity or contention checks programmed into dBase applications. SBase 4 does however provide powerful facilities both for importing and for exporting files in dBase II or dBase III format. If your application calls for the updating of data stored externally in dBase format, you should first import the data, then carry out the required updates, then re-export the data back into its original dBase format using the Export command (see Chapter 22 Export). Uses of the Import command In addition to dBase format files, the Import command also includes options for importing many other popular file formats. The Import and Export commands both support the same range of file formats, facilitating the ready transfer of data in either direction between SBase 4 and other applications. The Import command is used in three distinct ways: - As a file conversion utility. In one step it converts a file or a selected part of a file created by another application into a new SBase 4 file, complete with its associated file definition and index. Import can convert files in many of the popular database and spreadsheet formats. It can also convert files in ASCII delimited format. - As a merge utility to merge data stored in fixed length or delimited ASCII format into a pre-existing SBase 4 file. Almost all applications are capable of producing ASCII files as output. You can accordingly use this method for transferring data between applications where it cannot be directly converted by the first method. - As a merge utility to merge two SBase or Superbase files. Import File Types SBase 4 can convert files of the following types into new SBase 4 files: Application ASCII delimited dBase II, III, III+ Enable Lotus 1-2-3 Microsoft Excel Grafox Logistix VisiCalc and others Superplan Note The character set used by DIF files is assumed to be the IBM character set, unless the ANSI conversion option has been selected or the header of the DIF file has the title `Excel'. SBase 4 can merge files of the following types using an existing SBase file definition: Application ASCII fixed length ASCII delimited Superbase ASCII Files If you intend to use the Import command on an ASCII file, you should first ensure that it is indeed an ASCII file, and not one containing embedded nulls or control characters. The existence of such characters will cause Import to malfunction. It is not sufficient to use the Type command or the Text Editor to read the file. These utilities will ignore control characters and nulls in displaying the file contents. If in doubt, you can view the file using the Shell `Type' commands. In SBase Professional 4, Non-ASCII files can often be merged into SBase files under DML control of SBase Professional 4 using the OPEN FOR INPUT and INPUT commands. If you attempt this, bear in mind that SBase treats a null (ASCII code 00) as a string terminator. Once you are sure that the file you are dealing with is an ASCII file, you need then to find out whether it is a delimited or fixed length file, and if delimited the values of the field and record delimiters. ASCII Delimited Files In ASCII delimited format, the data is stored as ASCII characters with further special characters to mark the end of each data item. These are called delimiters or separators. The data should be organized sequentially into records, with a delimiter separating each field in the record including blank fields, and a different delimiter separating each record. Part of a typical ASCII delimited file might look like this when viewed by the Type utility or the Text Editor: Mr.,John,Smith,Carpenter,415 345 6789 Mrs.,Susan,Jones,Cook,408 546 3456 ,Julian,Doe,,415 465 6781 This section contains three records, each of which holds five fields: a title, first name, surname, profession and a telephone number. Notice how the last of the three records has a blank field for the title and further blank field for the profession. These fields, though blank, are still terminated by separators. Caution ASCII delimited files must contain the right number of separators for each record, or the data will be imported into the wrong fields. ASCII Delimited File Separators In the above example the field separator is the comma (ASCII code 44), and the record separator the carriage return character followed by the line feed character (ASCII codes 13 and 10). These are actually the default separators assumed by SBase. If the separators in your ASCII file differ from these, select System Options on the Set menu to alter the default separator values accordingly. Up to two characters may be used for each kind of separator. Characters are specified by typing in the ASCII code equivalent to the character you want to use. A list of ASCII codes is provided in Appendix D. To change the default values, move the pointer to the relevant box and click in it. This will produce the insertion point and you can then type in the new ASCII code. To enter a two character separator, type in a comma between the ASCII codes for the two characters. Caution The same characters should not be used for both field and record separators. Quotation Marks ASCII files may well contain as data one or more of the characters normally used as separators, for example names and addresses typically include commas. For this reason SBase provides an option during Export to enclose each field in double quotation marks: "Mr.","John","Smith","345, Express Highway","415 345 6789" "Mrs.","Susan","Jones","45, Juniper Drive","408 546 3456" "","Julian","Doe","","415 465 6781" This option can be set using the System Options command on the Set menu. In the System Options requester, click the check box next to the Use Quotes option. SBase will automatically discard double quotation marks around fields when it finds them during an Import. ASCII Fixed Length Files In ASCII fixed length files, each field or record occupies a fixed amount of space on disk. Each record contains the same number of characters or bytes, and a given field contains the same number of characters for each record. The data is stored as a continuous string of ASCII characters including spaces. Some ASCII data files consist of fixed length records separated by carriage returns (mainframe software often produces this kind of output). Converting Files Converting ASCII Delimited Files ASCII fixed length files can only be imported by merging with an existing file definition. This operation is covered below. SBase can however convert ASCII delimited files into a new SBase file, using the field delimiters as a basis for the file definition it creates. Data types are assumed as Text, and Field names given arbitrary values by the system of the form A,B..,AA,BB..etc. Following completion of the Import process, SBase 4 invokes the File Modify requester, displaying the arbitrary field names and other file definition parameters for amendment. Converting Database Files SBase converts files in DBF format into new SBase 4 files. Following selection of this file type, SBase will ask you to select a file from a requester displaying all the filenames in the current directory with the DBF extension. It will then automatically create the SBF file and SBD file definition, taking the SBD file definition field names and types from the DBF file header. Finally SBase 4 displays the New Index requester and requests selection of at least one field for indexing purposes. For each field selected it creates an index to the new file. SBase 4 is designed to access records in index sequence. Individual records in dBase filing systems are often identified by record number. If you wish to continue identifying the records in your imported file in this way, you should ensure before importing that the dBase file includes a field containing this record number, since SBase can only create an index on an existing field. Converting Spreadsheet Files SBase 4 can convert data in various spreadsheet formats into new SBase 4 files. Supported formats include WKS, WK1, XLS, LGX, SPP and DIF. When importing spreadsheet data, each row is taken as a record, and each cell as a field in the record. Columns that are completely empty in the spreadsheet are ignored. The spreadsheet data should accordingly be structured as a series of repeated rows. SBase 4 provides the option to select a rectangular block of cells to import from the spreadsheet by specifying the range from row/column to row/column. It is also possible to import the SBase 4 field names from the spreadsheet file by specifying on which row number in the spreadsheet these labels appear. If a labels row number is not specified, each field name is made up from the spreadsheet's column letter followed by an underscore character, for example `A_'. Merging Files General Considerations SBase 4 can merge data from an ASCII delimited or fixed length file or from a SBase 4 file. It always merges to an existing SBase 4 file. The `merge to' or destination file may or may not contain pre-existing data, but must have a valid SBD file definition. The Import process will always attempt to create new records in the destination file rather than update existing ones. Import assumes that the destination file is the current open file. If the fields in the source file are not in the same sequence as the fields in the destination file, or if there are more fields in the destination file than there are in the source file, you should make a Field Selection on the current open file, selecting the necessary fields in the sequence that corresponds to the source file. If you are Importing into a Field Selection, then SBase 4 will create new records with the fields not selected left blank. Such fields should not be indexed, allowing only unique keys, as otherwise the Import will fail when SBase 4 attempts to create the second blank index key. SBase 4 will apply to the incoming data whatever data typing, conversion, validation and calculation rules are specified in the destination file definition. If any one of these rules does not apply to the incoming data it should be removed or relaxed using Project Modify File before the Import takes place. Otherwise SBase 4 will not be able to successfully complete the Import. For example: - Required fields must contain data. If an incoming record has no value for a required field, SBase 4 will signal an error. - Validations in the current file definition will be carried out before the record is stored, whether the field value has been imported or not. If a validation does not yield the correct result, SBase 4 will signal an error. - Calculated, Constant and Virtual fields are derived from the current file definition rather than input. Values in corresponding fields in incoming records will thus be disregarded. - If a text field definition in the destination file specifies upper case only for example, the incoming data will be converted accordingly. The data types of fields in the source and destination files should always correspond. SBase 4 will convert incoming ASCII text strings containing numeric or date information into SBase 4 numeric or date fields, on condition that the same values would have been accepted during manual data entry. Merging ASCII Files If you are importing an ASCII file into an existing SBase 4 file, you can set a filter to determine which records are to be imported. The filter is specified using the destination field list. See Chapter 9 Retrieving Data Interactively for a detailed explanation on how filters work. The delimiters in an ASCII delimited file must correspond to the values set in the System Options requester. See above for more information about ASCII delimited files, separators and quotation marks. With ASCII fixed length source files, the field lengths during import are assumed to be the lengths set in the destination file definition. If in doubt, inspect the source file using Type or the Text Editor in order to determine the field lengths, then use File Modify on the destination file to set its field lengths accordingly. Before importing a fixed length ASCII file that includes record separators, turn on the Fixed Length Separators check box on the System Options requester, and specify the record separator in the normal way. See above for more information about types of ASCII file and ASCII fixed length files. Using Process Import Before operating the Import command you should know the format of the file you are importing from, and whether you are going to be creating a new file or merging imported data into an existing file. You should then read the relevant sections above for detailed instructions about this type of import operation. If merging, make the destination file the current file and open a Field Selection list, if appropriate. 1. Select Import from the Process menu. SBase displays the following requester: *** See Fig 21-1 *** 2. The upper part of the requester shows the three merge options. If you are merging, select one of these. Otherwise select the type of file you wish to import to create a new file. Click on the appropriate button. 3. If you have specified one of the spreadsheet types, you now have the option of select one of these. Otherwise select the type of file you wish to import to create a new file. Click on the appropriate button. 4. If you have specified one of the spreadsheet types, you now have the option of specifying the range of cells to be imported and the number of the spreadsheet row containing the labels to be used for field names. 5. Once you are satisfied with your selections at steps 2 and 3 click OK. Otherwise click Cancel to return to the main menu. 6. If you are merging an imported ASCII file you now have the option of specifying a filter on the file. Formulate the filter and click OK or click Cancel to return to the Import requester. - The source file must be opened first to ensure that field references are understood. 7. You will now see a file selection requester. Go to the directory containing the source file. If you have specified ASCII format, the requester lists all files in the current directory. Otherwise it shows only those files with the corresponding extension, for example DBF. Select the file to be imported and click OK, or click Cancel to return to the Import requester. 8. Once the processing of the source file is complete, if the Import process creates a new file, you will now see a New Index requester. Select the field or fields on which the new file is to be indexed. 9. If you have created a new file from an ASCII delimited source file, you will now see the File Modify requester for the new file. Here you can replace the arbitrarily assigned field names with meaningful field names of your own.