Inserting Data From an External File

Note This section covers an optional topic and may be skipped without losing any continuity. However, it is an important topic not covered in detail elsewhere in the documentation.

An external table is a special kind of table that stores its data in an ASCII file separate from the database. It may occasionally want to import data from an ASCII file into a database (for example, if the data was originally entered in another application or at a remote location).

You can populate a table with data from a formatted ASCII file by following these steps:

  1. Create the table you want to populate. Often this table will already exist in the database.
  2. Create an ASCII file on the server containing the data, formatted strictly to conform to the column definitions of the table in step one. Depending on how the file originated (for example, from a desktop application), you may have to edit the file manually with a text editor to ensure that it is formatted correctly.
  3. Create a temporary external table that has all the columns that will get data from the external file. It is usually easiest to create all the fields as CHAR(w), even if they will contain numeric data. The table must also have a CHAR(1) column (usually called EOL) to take the end-of-line character.
  4. Insert the data into the destination table using INSERT with a SELECT clause. InterBase's automatic type conversion feature will ensure that the data in each column is automatically converted from CHAR to the appropriate data type.

For example, suppose a salesman on the road has been keeping his sales records on his laptop computer in a spreadsheet application. When he gets back to the office, one way he could enter these records into the SALES table would be to export the information to a text file and then import the data into the database through an external table. So, in this example, you do not need to perform step one, because the SALES table already exists in the database.

The next step is to create the data file. The sales data is in the file, SALES.DAT, in the EXAMPLES\TUTORIAL directory. View this file now with the Notepad editor. It looks something like this:

V92E0340 V92J1003 V93J2004

1004 1010 1010

11 61 118

shipped shipped shipped

15-OCT-1992 26-JUL-1992 30-OCT-1993

16-OCT-1992 4-AUG-19 92 2-DEC-1993

17-OCT-1992 y 15-SEP-1992 y 15-NOV-1993 y

The lines are too wide to display above, so only the leftmost portion of each line is shown. You can scroll the Notepad window to the right to see the remainder of each line.

Each line in this file corresponds to a row of data (record) in the SALES table, and each item of text on a line is a value to be inserted into a field in the row. The text is padded with spaces where necessary to make each field have the specified number of characters, even at the end of each line. The first item in each line (for example "V92E0340") is a value for the PONUMBER column, the second (for example "1004") is a value for the CUST_NO column, and so on. It is crucial that the items on each line always are in the same order.

For the server to be able to access this file, you must copy it to the server platform (to a disk to which the server has direct access). Use the standard FTP utility or operating system copy command to copy SALES.DAT to the directory on the server where your database resides. That completes step two of the process.

The next step is to create a temporary external table in the database called SALES_EXT. Look at the file, SALES_XT.SQL. It contains the following CREATE TABLE statement:

CREATE TABLE SALES_EXT EXTERNAL "/PATH/SALES.DAT"

(PO_NUMBER CHAR(10), CUST_NO CHAR(12), SALES_REP CHAR(10), ORDER_STATUS CHAR(13), ORDER_DATE CHAR(12), SHIP_DATE CHAR(12), DATE_NEEDED CHAR(12), PAID CHAR(7), QTY_ORDERED CHAR(12), TOTAL_VALUE CHAR(12), DISCOUNT CHAR(16), ITEM_TYPE CHAR(8), EOL CHAR(1));

Notice the keyword EXTERNAL at the top, followed by a file path in quotes. You must edit this path to specify the location on the server to which you copied SALES.DAT in the previous step. All the columns in SALES_EXT are defined as CHAR (character) values. Notice also the EOL column. This is a dummy column to contain the carriage return at the end of each line of data in SALES.DAT.

gpg^ Input this definition by choosing File I Run an ISQL Script... and selecting SALES_XT.SQL in the EXAMPLES\TUTORIAL directory. At this point, you have an external table which has data stored in a file on the server. You can query data from this table as if it were an ordinary table, but you cannot modify the data, because it does not actually reside in the database, but in the file. Enter the following statement:

SELECT * FROM SALES_EXT;

You will see the data from the data file in the ISQL Output area. Now you have completed step three of the procedure.

In the final step, you will migrate the data from the external table into the real [¡g§p SALES table. Look at the file, MIGRATE.SQL. It contains the following INSERT statement:

INSERT INTO SALES

(PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE,

SELECT

PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, FROM SALES_EXT;

This statement selects values from the SALES_EXT table (excluding the EOL delimiter) and inserts them into rows in the SALES table, migrating the data from the file to the SALES table.

Edit the CONNECT statement at the beginning of this file and specify the server and database you are using. Then input this statement by choosing File I Run

ORDER_DATE, SHIP_DATE, DISCOUNT, ITEM_TYPE)

ORDER_DATE, SHIP_DATE, DISCOUNT, ITEM_TYPE

ISQL Script... and choosing MIGRATE.SQL from the EXAMPLES\TUTORIAL directory.

Now enter:

SELECT * FROM SALES;

and you will see the data that was in the SALES.DAT file has been inserted into the SALES table. Notice that the non-character columns have been converted to the appropriate data type automatically.

Was this article helpful?

0 0

Post a comment