3.2.4 Loading Data
In the SQL page, you can load data from one or more local files into one or more tables.
The file formats that you can load are CSV, XLS, XLSX, TSV, TXT, XML, JSON, and AVRO. For XML, JSON, and AVRO files, see Format Specifications for JSON, AVRO, and XML Files.
3.2.4.1 Loading Data from a Local File to a New Table
To load data from a local file to a new table:
-
You can start in one of the following ways:
-
In the Navigator tab, in the left pane, click Object submenu, select Data Loading, and then select Upload Data into New Table.
Figure 3-6 Upload Data to New Table Option
Description of "Figure 3-6 Upload Data to New Table Option" -
In the Navigator tab, drag and drop the local file into the left pane. When you drag a file into the pane, the following message is displayed Drop the file here to start.
The Upload Data into New Table is displayed. A preview of the data is displayed in a grid format.
-
-
Click Show/Hide options to display options that you can modify for data preview:
-
Column names: Select Get from file to display column headers in the first row.
- Encoding: An option to select the encoding type is visible when the loaded file is in plain text format (CSV, TSV, or TXT). The default encoding type is UTF-8.
-
Text enclosure and Field delimiter: These options are visible only when the selected file is in plain text format (CSV, TSV, or TXT). Select or enter the character used in the source file for text enclosure and field delimiter.
-
Rows to skip: Enter or use the up and down arrows to select the number of rows to skip.
-
Preview size: Enter or use the up and down arrows to select the number of rows to preview.
-
Limit rows to upload: If you select this option, you need to specify the rows to load. Use the up and down arrows to select the number of rows to load.
To remove the options selected and the data preview, click Clear.
After selecting the required options, click Apply, and then click Next.
-
-
In Table Definition, do the following:
-
In the Table Name field, enter a name for the target table.
-
Select the check box at the beginning of a row to add the column to the target table.
-
Select or enter values for column attributes such as Column Name, Column Type, Precision, Scale, Default, Primary Key and Nullable.
-
The Format Mask column appears for date, timestamp and numeric types of data. For date and timestamp types, you must select a value from the drop-down list or type the value in the Format Mask field. For numeric types, the format mask is optional.
For a date and timestamp column, you need to supply a compatible format mask that describes the data being uploaded. For example, if the date data looks like
12-FEB-2021 12.21.30
, you need to supply a date mask ofDD-MON-YYYY HH.MI.SS
. The format mask is automatically determined based on the data in the file. You need to review the suggested format mask and if needed, modify it by entering the format directly into the target cell.
Figure 3-7 Table Definition Step in Upload Data into New Table
Description of "Figure 3-7 Table Definition Step in Upload Data into New Table"Click Next.
-
-
Review the generated DDL code based on the selections made in the previous screens. The mapping of the source to target columns are also displayed.
Click Finish. After the data is successfully loaded, the new table is displayed in the Navigator tab.
- For a detailed report of the total rows loaded and failed,
do one of the following:
-
Right-click the table in the Navigator tab, select Data Loading, and then select History. This displays the report for a specific table.
-
In the Navigator tab, select Object submenu , select Data Loading, and then select History. This displays the report for all tables in the schema that is selected in the Navigator tab.
-
In the worksheet output pane, select the Data Loading tab. This displays the report for all visible tables (including tables from other schemas).
A summary of the data loaded is displayed in the History dialog. If any data failed to load, you can view the number of failed rows in the Failed Rows column. Click the failed rows column to open a dialog showing the failed rows.
In the History dialog, you can also search for files loaded by schema name, table name, or file name. To remove the loaded files, click Remove all history .
You can also load data from a file to a new table using the steps in Loading Data from Multiple Local Files into Mutiple Tables.
-
3.2.4.2 Loading Data from a Local File to an Existing Table
To load data from a local file to an existing table:
3.2.4.3 Loading Data from Multiple Local Files into Mutiple Tables
3.2.4.4 Format Specifications for JSON, AVRO, and XML Files
Data has to be stored in a particular format for JSON, AVRO, and XML files to load them successfully into a table.
The format specifications are described in the following sections.
3.2.4.4.1 JSON and AVRO Files
For JSON and AVRO files, the conversion for primitive types to table columns is supported only for top-level data. Nested objects are saved as JSON strings such as VARCHAR2 (JSON) or CLOB (JSON).
Note:
JSON check constraints are available only for Oracle Database 12c and later releases.
Consider the following JSON file as an example:
[
{
"ItemNumber": 1,
"Description": "One Magic Christmas",
"Part": {
"UnitPrice": 19.95,
"UPCCode": 13131092899
},
"Quantity": 9,
"Total": 179.55
},
{
"ItemNumber": 2,
"Description": "Lethal Weapon",
"Part": {
"UnitPrice": 17.95,
"UPCCode": 85391628927
},
"Quantity": 5,
"Total": 89.75
}
]
The AVRO schema for this file:
{
"type": "array",
"items": {
"type": "record",
"fields": [
{
"name": "ItemNumber",
"type": "int"
},
{
"name": "Description",
"type": "string"
},
{
"name": "Part",
"type": {
"type": "record",
"fields": [
{
"name": "UnitPrice",
"type": "float"
},
{
"name": "UPCCode",
"type": "float"
}
]
}
},
{
"name": "Quantity",
"type": "int"
},
{
"name": "Total",
"type": "float"
}
]
}
}
Load the JSON file using "Upload Data" in the SQL page, and it is converted
to the following table with two rows. part
is a nested object that is
assigned the column type CLOB (JSON) during data mapping.
3.2.4.4.2 XML Files
This section lists the specifications for loading XML files.
-
Attributes will have their own columns
If the XML data is structured as:
<?xml version="1.0"?> <catalog> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </catalog>
The generated columns in the table are
id
,author
,title
,genre
, andpublisher
. -
Two or more levels of nesting are needed to parse the data
In the following example, the data that needs to be parsed will not be located because it has only one level of nesting (catalog).
<?xml version="1.0"?> <catalog> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </catalog>
However, the following examples will work:
<?xml version="1.0"?> <catalog> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </catalog>
or
<?xml version="1.0"?> <catalog> <bookstore> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </bookstore> </catalog>
or
<?xml version="1.0"?> <catalog> <bookstore> <shelf> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </shelf> </bookstore> </catalog>
-
Special characters such as hyphen (-) and period (.) in tag names are replaced by underscore (_) in the column name
XML tag names can contain hyphens and periods. Since the parser is converting XML to JSON, these characters become invalid object keys.
<?xml version="1.0"?> <catalog> <book id="bk102"> <author-name>Ralls, Kim</author-name> <title.1>Midnight Rain</title.1> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </catalog>
The generated columns are
id
,author_name
,title_1
,genre
, andpublisher
. -
First-level only-text tags are ignored
<?xml version="1.0"?> <catalog> <library> New Age Library </library> <book id="bk102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </catalog>
The
<library>
tag is ignored and only the content of the<book>
tag is taken into account. The generated columns areid
,author
,title
,genre
, andpublisher
. -
First-level repetitive data is interpreted as an array of values
<?xml version="1.0" encoding="UTF-8"?> <items id="orders"> <item_number>1</item_number> <description>One Magic Christmas</description> <part> <unit_price>19.95</unit_price> <upccode>13131092899</upccode> </part> <quantity>9</quantity> <total>179.55</total> <item_number>2</item_number> <description>Lethal Weapon</description> <part> <unit_price>17.95</unit_price> <upccode>85391628927</upccode> </part> <quantity>5</quantity> <total>89.75</total> </items>
The generated columns include
item_number
,description
,part
, and each column will have only one row with the following values respectively ([1,2
], ["One Magic Christmas","Lethal Weapon"
], [{" unit_price":19.95,"upccode":13131092899},{"unit_price":17.95,"upccode":85391628927
}] and so on for the remaining columns. -
Tags containing values and attributes are converted to object
<?xml version="1.0"?> <catalog> <book id="bk102"> <author country="ca">Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <publisher>John Doe</publisher> </book> </catalog>
The
<author>
tag is converted to a column and will have an object as value that is structured as:{ "_":"Ralls, Kim", "country":"ca" }
Note that the value of the tag has underscore ("_") as key and the attributes as "attribute_name": "attribute_value".