Data Import Wizard (Load Data)

Use this wizard to import data into a table. For example, if you right-click the Tables node or a table name in the Connections navigator and select Import Data, you can specify the .source file (such as a spreadsheet or a delimited file) from which to import data. You create a table and import data into it, or import data into an existing table.

Data Preview

You can specify preferences that affect the preview display of data to be imported. Several default values are determined by the Database: Utilities: Import user preferences.

Header: If this option is enabled, a header row (not data to be imported) is assumed to start before (Before Skip) or after (After Skip) the number of rows for Skip Rows.

Skip Rows: The number of rows at the start to skip (that is, not be considered as data to be imported). Thus, the combination of Header and Skip Rows determines the total number of rows at the start that are not considered data to be imported.

Format: Format of the file containing data to be imported. For example: .xls (Microsoft Excel), .csv (comma separated value), .dsv (delimiter separated value), or .tsv (tab separated value).

Preview Limit: The maximum number of rows of data to be displayed in the preview pane in the lower part of the box. When the wizard creates a new table, the preview data is used to calculate the size of the columns; therefore, ensure that the preview is a good sample of the data. The displayed data is affected by the Preview Limit and by the Database: Utilities: Import user preference for Preview File Read Maximum, which limits the total number of bytes read from the file.

Encoding: Character set used for encoding of the data to be imported.

Delimiter, Line Terminator, Left Enclosure, Right Enclosure: Select or type the character in the input file that is used for each of these.

Import Method

Import Method: One of the following methods for loading the table definitions and data:

The number of remaining fields on this page and their availability depend on the load method and whether or not you invoked the wizard on a specific table.

Table Name: Target table in which to import the data.

Staging Table Name: External table to be used for loading the target table.

Commit and Drop Staging Table: If this option is enabled, the staging table is committed and dropped after the import operation. If this option is not enabled, the staging table is not committed or dropped.

Send Create Script to Worksheet: If this option is enabled, after you click Finish SQL Developer does not immediately perform the import operation, but instead opens a SQL Worksheet with statements that will be used if you click the Run Script icon in the worksheet.

Choose Columns

Available Columns: Lists the columns from which you can select for import into columns in the table. To select one or more columns, use the arrow buttons to move columns from Available to Selected.

Selected Columns: Lists the columns whose data is to be imported into columns in the database table. To change the order of a selected column in the list for the import operation, select it and use the up and down arrow buttons.

Column Definition

Enables you to specify information about the columns in database table into which to import the data.

Match By: The kind of automatic matching from source to target columns: by Name, by Position, or None (use the default). The default for existing tables is by name if a header is present in the file, and by position if no header is present.

Source Data Columns and Target Table Columns: You can select a source file data column to display its target (Oracle) column properties. For Data Type, select one of the supported types for this import operation. For a VARCHAR2 or NUMBER column, you must specify an appropriate Size/Precision value. You can specify whether the column value can be null (Nullable?), and you can specify a default value (Default).

Finish

Verify Parameters Before Import: You are encouraged to verify the import parameters. If any test fails, the Information column contains a brief explanation, and you can go back and fix any errors before you can click Finish.

To perform the import operation, click Finish. The action taken will depend on the load method selected and on whether you specified to send the script statements to a SQL Worksheet.

Related Topics

Database Objects

SQL Developer User Interface

SQL Developer Dialog Boxes and Wizards