Importing External Files

Go Up to Using the EXTERNAL FILE Option

The following steps describe how to import an external file into an InterBase table:

1. Create an InterBase table that allows you to view the external data. Declare all columns as CHAR. The text file containing the data must be on the server. In the following example, the external file exists on a UNIX system, so the EOL character is one byte. If the example file was on a Windows platform, you would need two characters for NEW_LINE.

   CREATE TABLE EXT_TBL EXTERNAL FILE 'file.txt'
   (FNAME CHAR(10),
    LNAME CHAR(20),
    HDATE CHAR(8),
    NEWLINE CHAR(1) );
   COMMIT;
   

2. Create another InterBase table that will eventually be your working table. If you expect to export data from the internal table back to an external file at a later time, be sure to create a column to hold the newline. Otherwise, you do not need to leave room for the newline character(s). In the following example, a column for the newline is provided:

   CREATE TABLE PEOPLE
   (FIRST_NAME CHAR(10),
    LAST_NAME CHAR(20),
    HIRE_DATE CHAR(8),
    NEW_LINE CHAR(1));
   COMMIT;
   

3. Create and populate the external file. You can create the file with a text editor, or you can create an appropriate file with an application such as ­Paradox for Windows or dBASE for Windows. If you create the file with a text editor, make each record the same length, pad the unused characters with blanks, and insert the EOL character(s) at the end of each record. The number of characters in the EOL is platform-specific. You need to know how many characters are contained in the EOL of your platform (typically one or two) in order to correctly format the columns of the tables and the corresponding records in the external file. In the following example, the record length is 36 characters. “b” represents a blank space, and “n” represents the EOL: When exporting data to or from an external file, the file must already exist before you begin the operation. Also, you must specify a directory path whenever you reference the external file.
4. At this point, when you do a SELECT statement from table EXT_TBL, you will see the records from the external file:

   SELECT FNAME, LNAME, HDATE FROM EXT_TBL;

   FNAME     LNAME             HDATE
   ========  ================= ===========
   Robert    Brickman          12-JUN-1992
   Sam       Jones             13-DEC-1993
   

5. Insert the data into the destination table.

   INSERT INTO PEOPLE SELECT FNAME, LNAME, CAST(HDATE AS DATE),
    NEWLINE FROM EXT_TBL;
   

   Now if you SELECT from PEOPLE, the data from your external table will be there.

   SELECT FIRST_NAME, LAST_NAME, HIRE_DATE FROM PEOPLE;

   FIRST_NAME LAST_NAME           HIRE_DATE
   ========== =================== ===========
   Robert     Brickman            12-JUN-1992
   Sam        Jones               13-DEC-1993
   

   Note: InterBase allows you to store the date as an integer by converting from a CHAR(8) to DATE using the CAST() function.