Example: Loading Data into a Table

You can use SQL Developer to load data into a table. You can load data from an .xls file or a .csv file into the table.

In this example, you load data into the PURCHASE_ORDERS table that you created in "Example: Creating a Table". For simplicity, this example loads only three rows.

To prepare for this example, you must create a text file named load.csv on the file system of the database host computer or on the file system of your local computer. The contents of the file should be as follows:

1,Office Equipment,25-MAY-2012,1201,13-JUN-2012
2,Computer System,18-JUN-2012,1201,27-JUN-2012
3,Travel Expense,26-JUN-2012,1340,11-JUL-2012

Note:

This example assumes that the columns in the PURCHASE_ORDERS table are the following: PO_NUMBER, PO_DESCRIPTION, PO_DATE, PO_VENDOR, and PO_DATE_RECEIVED. If your PURCHASE_ORDERS table does not have all these columns (or has additional columns), then modify the data in the text file accordingly.

To load data into the PURCHASE_ORDERS table:

  1. In SQL Developer, navigate to the PURCHASE_ORDERS table in the HR schema, following the instructions in "Viewing Tables".
  2. Right-click the PURCHASE_ORDERS table and select Import Data.

    The Open dialog box appears.

  3. Navigate to and select the load.csv file that includes the data you want to import into the table, and then click Open.

    The Data Import Wizard appears, with the Data Preview page displayed.

  4. Make sure that Header is deselected, Format is set to csv, Line Terminator is set to environment default, and that Left Enclosure and Right Enclosure are set to none. Then click Next.
  5. On this page, select:

    • Insert in the Import Method field.

    • PURCHASE_ORDERS in the Table Name field.

    • A value in the Import Row Limit field that is greater than the number of rows in your .csv file.

  6. Click Next.

    The Choose Columns page appears.

  7. Move the columns that you want to import into the Selected Columns list, and arrange them in the order you want.
  8. Click Next.

    The Column Definition page appears.

  9. Map the source data from the .csv file to the existing table. For each source data column on the left, select the column on the right to import this data into.

    For example, in the Source Data Columns table on the left, select the first column, and then in the Target Table Columns table on the right, select in the Name field the name of the column in the database table that will store that data. Map the data for each of the columns in the Source Data Columns table to the appropriate column in the Target Table Columns table.

    The figure shows the data in the last column in the Source Table Columns table being mapped to the last column (PO_DATE_RECEIVED) in the Target Table Columns table. At this point, the previous four columns in the Source Table Columns table have already been mapped to the appropriate columns in the Target Table Columns table.
    Description of the illustration sqld_data_import_new.gif follows
    Description of the illustration sqld_data_import_new.gif
  10. Click Next.

    The Finish page appears.

  11. Click Finish.

    The data is imported into the table.

See Also:

" About Tables"