To load data from local files into your Oracle Autonomous Database, on the Data Load page, select LOAD DATA and LOCAL FILE, then click Next. 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, TSV, delimited TXT, XLS, XLSX, XML. 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, click the Actions icon and 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.
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.
Bulk Edit Settings
- 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.
- Data Type
- Target Column name
- Include Columns for loading
- Exclude columns for loading
Description of the illustration bulk-edit1.png
VARCHARin the search field and click on the magnifier icon besides the search field.
Description of the illustration bulk-edit2.png
The search will return the rows having
VARCHARas the Data Type. Select the Bulk Edit icon besides the magnifier icon.
Description of the illustration bulk-edit3.png
Selecting the Bulk Edit icon opens the Bulk Edit Mapping dialog box.
Description of the illustration bulk-edit4.png
Click Data Type and select
NUMBERfrom the Data Type drop-down. Click OK.
Description of the illustration bulk-edit5.png
The Data Type of the selected two rows changes from
Description of the illustration bulk-edit6.png
Clear the content from the search field and click the magnifier icon to view the bulk edit updates in the Mapping table.
Description of the illustration bulk-edit7.png
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.
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 File tab. The source preview has a Preview size field and a portion of the data in the file. To change the number of rows displayed, you can enter a value from 1 to 100 in the field.
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 Run. To stop the data load job, click the Stop icon.
When the data load job completes, the Local Files page displays the results of the job. At the top of the page, the Status shows the number of items for which the load has completed over the number of items in the job, and the total time elapsed for the job.
To view information about an item in the job, click the Settings icon in the item. In the settings pane, the Settings tabs are the same as before running the job, except that the target preview now contains the data loaded by the data load job and a Data Definition tab . To close the pane, click Close.
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 / Explore 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.
Click the Data Load Jobs card on the Data Load home page to view the new or updated table on the Catalog page.
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, click the Reload Cart option to reload cards from your recent cart and edit them as you did before your first attempt. Click the pencil icon to make any changes to the data load job (i.e., change a column name).
Click Done to return to the Database Actions page.