Loading Data From Local Files

To load data from local files into your Oracle Autonomous Database, on the Data Load page, select LOAD DATA and LOCAL FILE.

Drag one or more files from your local file system navigator and drop them in the Data Load Cart. You can also click Select Files or the Select Files icon, select one or more files from the file system navigator, and then click Open.

You can add files in these file formats: AVRO, CSV, JSON, GeoJSON, TSV, delimited TXT, XLS, XLSX, XML, PNG, JPEG and PDF. For information on supported file formats, see Format Specifications for JSON, AVRO, and XML Files.

An item for each file appears in the cart. For an XLS or XLSX spreadsheet, the worksheets of the spreadsheet appear as individual items. The item shows the name of the source file or worksheet and its size, and the name of the table that is the target for the data load. The Data Load tool supports loading tables from only the first worksheet of a multi-worksheet XLSX file when the file is in an object store.

You can add more files to the cart by clicking the Select Files icon. You can add any number of files to the cart and load data from all of them in a single data load job.

To remove a source file from the Data Load Cart, click the Remove (trash can) icon for the source item. To remove all source files from the cart, click the Remove All (trash can) icon in the Data Load Cart menu bar.

To return to the Data Load page, click Data Load above the Data Load Cart menu bar.

Specify Processing Options

To specify settings for the data load job or preview the data in the source or the target, select the Settings (pencil) icon for the item in the Data Load Cart.

In the settings pane, on the Settings tab, you can view the name and size of the file in the title of the Load Data dialog box.

The Name field specifies the name of the target table. The value in the field varies depending on the selection in the Options field. If the option is Create Table, then the default target value is the name of source file or worksheet.

To specify a different name for the target table, enter it in the Name field. For the other target table choices in the Options field, the default value is <None>. Expand the drop-down list and select an existing table as the target.

In the Options field select Create Table, Insert into Table, Replace Data, Drop Table and Create New Table, or Merge into Table. Point to the question mark icon to see a brief description of the selected option.

Select a different schema from the Schema drop-down to create your target table in another schema.

Note

The Schema drop-down is available only if you have PDB_DBA role grant to you.

To grant yourself a PDB_DBA role, you must log into your Database Actions instance and enter the following command in the SQL worksheet area displayed in the SQL tab under Development tools present in the Launchpad.

Grant PDB_DBA to Username;

This drop-down is available for Create Table and Drop Table and Create New Table options.

The Source column name option specifies whether to get the source and target column names from the file or to specify the column names manually. Getting the column names from the header of the source file is the default. If you select the Column header option, then the first row in the file is processed as column names. If you deselect the option, then the first row is processed as data. To specify column names manually, enter a name for each target column in the Mapping section. You can also select a data type for the column.

The Start processing data at row field specifies how many rows to skip when loading the source data into the target. If you have selected the Column header option, and if you enter a number greater than 0 in the Start processing data at row field, then that number of rows after the first row are not loaded into the target. If you have deselected the Column header option, and if you enter a number greater than 0 in the Start processing data at row field, that number of rows including the first row are not loaded into the target.

To change the character set encoding for the contents of the file, select a value from the Encoding drop-down list.

To specify the characters that enclose text, select the double-quotes or single-quote character or None from the Text enclosure drop-down list.

To change the delimiter character that separates columns in the source, expand the Field delimiter drop-down list and select a character. For example, if the file has columns delimited by semicolons, change the delimiter from the default comma delimiter to a semicolon.

To convert any invalid value in a numeric source column to a null value in the target column, select the Numeric column Convert invalid data to null option.

Settings Template

The save settings feature saves the configuration set in the Cart settings in the form of a JSON file. When opening the Settings template, you have the following options:

Load Settings Template: Loads a settings template from your local system.
Save Settings Template: Saves the current existing settings template.

Description of settings-template.png follows

You can use the Load Settings Template if you want to use an existing customized template present in your local.

From the Settings Template in the Settings tab of the Load Data page, select Load Settings Template.
You will see a Load Settings Template wizard, click the Settings Template JSON to load a JSON file from your system.
Clicking the Settings template JSON will open your local system. Click OK to load the JSON file.
After you load the JSON file, you can view the updates applied automatically to the settings tab which matches the JSON settings template you load from your local.

You can use the Save Settings Template to save the existing current Settings template.

From the Settings Template in the Settings tab of the Load Data page, select Save Settings Template.
The Template file editor appears. Click the Template File name and name the new template.
Click OK to finish saving the new name of the existing template.
You can test the configuration of the new template.

Bulk Edit Settings

You can use the Bulk edit settings to update all the columns at once from the mapping table. Use it to apply changes to the selection currently displayed in the results pane. You can search for the values of the column you want to edit in the search field and click the magnifier icon. The mapping table will display the results of the search. Select the Bulk Edit setting to update the column. The Bulk Edit setting allows you to:

Update values of all the fields in a group.
Find and replace, Add Prefix and Add suffix to target column name.
Include the column(s) for loading data to the target table.
Exclude the column(s) for loading data to the target table.

Searching the Column

The Bulk Edit setting updates the columns returned by the search field. The search box besides the Bulk Edit setting icon filters the list of columns you wish to update in a bulk. As soon as you start typing in the search field, the tool returns the field values which contains the letters you type. You can remove the filter by deleting all the content from the search box and clicking the magnifier icon that appears next to the search box.

The Bulk Edit setting enables you to update the values of the following columns for all the searches returned by the search field:

Data Type
Target Column name
Include Columns for loading
Exclude columns for loading

Consider changing the Data Type of first to fifth rows from VARCHAR to NUMBER in the mapping table. Description of bulk-edit1.png follows

Type VARCHAR in the search field and click on the magnifier icon besides the search field.
The search will return the rows having VARCHAR as the Data Type. Select the Bulk Edit icon besides the magnifier icon.
Selecting the Bulk Edit icon opens the Bulk Edit Mapping dialog box.
Click Data Type and select NUMBER from the Data Type drop-down. Click OK.
The Data Type of the selected rows changes from VARCHAR to NUMBER.
Clear the content from the search field and click the magnifier icon to view the bulk edit updates in the Mapping table.

Specify Mappings

You can filter the results in the mapping table with Quick Filter field that enables you to filter out Columns or Expressions.

Select Add Expression to perform Sentiment Analysis or Key Phrase extraction with the source data. See Use OCI Language Service Capabilities in Data Studio for more details.

If you select the Create Table or Drop Table and Create New Table option and you are getting the source column names from the file header, then in the Mapping section either accept the default values for the target columns and data types or specify different values. To specify different values, in the target column, enter a name for the column. In the Data Type column, select a data type from the drop-down list. If you are not getting the source column names from the file header, then for each source column specify a name for the target column and select a data type from the Data Type drop-down list. For the Date data type, select a date format from the Format drop-down list.

Note

You will receive a tooltip error message with the exact reason for the error message when you complete editing a cell. The mapping grid cell will be highlighted with red to indicate an invalid value that must be fixed. The highlight is removed after you fix the invalid value. For example, you can view the following tooltip error message when the target column name is not filled in. Description of tooltip-error.png follows

Description of tooltip-error.png follows

For the Merge into Table option, for each source column, select a target column form the drop-down list. You must specify at least one column as a key column. To specify a column as a key column, select the Merge Key check box for the column. Merge keys are one or more columns that uniquely identify each row in the table. Merge keys must not contain any null values. For loading tables with primary keys, this option automatically enables the selection of primary key columns as the merge keys.

For the Insert into Table or Replace Data options, for each source column, select a target column from the drop-down list of existing columns.

Preview Source Data

To view a selection of data in the source file, select the Preview tab. The Preview tab displays a File menu which displays the data in tabular format with it's values. You can copy the table. The Load Preview tab displays the source data.

Any modifications you make in the source preview do not affect the loading of data from the file.

Preview Target Data

For all options except Create Table, to view the existing data in the target table, select the Load Preview tab. The load preview displays the data in the target table before you run the data load job.

To close the settings pane, click Close.

Run the Data Load Job

When you have added all of the sources for the job and specified the settings for each source, to run the job click the Start icon in the Data Load Cart menu bar. In the Run Data Load Job dialog box, click Start. To stop the data load job, click the Stop icon.

Once the data load job starts, you can view the progress of the job in the Data Load dashboard. See The Data Load Page for more details.

When the data load job completes, the Data Load dashboard page displays the results of the job. At the top of the header of the table load, you can view the name of the table along with the total columns present in the table.

Click Report to view the total number of rows processed successfully and the count of rejected rows. You can also view the Start time. The SQL pane of the Report displays the equivalent SQL code of the job.

To view information about an item in the job, click the Actions icon on the Table Load.

To view a log of the load operation, click the Logging icon. You can save the log, clear it, or refresh it. Click OK to dismiss the log.

The list of tables on the Data Load page contains any new tables created. The target tables for the Insert into Table, Replace Data, Drop Table and Create New Table, and Merge into Table options contain the loaded data.

Fixing a data load job

After your data load job, you might see errors that you want to correct, or upon inspection, realize that you wanted to name a column differently. In such cases, you will view a warning sign on the Table load. Click the Reload icon to reload source with suggested fixes. Click Actions icon on the Table load and select Edit to make any changes to the data load job (i.e., change a column name).

Click Apply to apply any changes. Click Close to return to the Database Actions page.

Parent topic: Loading Data

Oracle Cloud Infrastructure Documentation