5.1.5 Loading Data

Use the Load Data Wizard to copy and paste or upload data.

• About Loading Data
  Load data into the Oracle APEX by copying and pasting CSV data, selecting a sample data set, or uploading a file.
• Copying and Pasting CSV data or Selecting a Sample Data Set
  You can copy and paste CSV data, or select a sample data set sample into the Load Data Wizard.
• Loading an XML Document
  Load an XML document using Load Data Wizard.
• Loading a JSON File
  Load a JSON file using Load Data Wizard.
• Loading a XLSX File
  Load a XLSX file using Load Data Wizard.

See Also:

Import, Export, Load, and Unload Options

5.1.5.1 About Loading Data

Load data into the Oracle APEX by copying and pasting CSV data, selecting a sample data set, or uploading a file.

Key capabilities when loading data into the APEX include:

• Copy and paste CSV data, or select a sample data set.
• Upload a file (supports CSV, XLSX, TXT, XML or JSON files).

  Note:

  Configure load settings such as column headers, file encoding, column delimiter / enclosed by (for CSV or TXT), and Select Sheet (for XLSX with multiple worksheets).

• View full preview and change more settings by clicking Configure button. Configure dialog displays maximum of 100 columns and 100 rows. You can configure same setting as settings in the main dialog plus maximum Rows to Sample and columns to load/unload.
• In Configure dialog, under Columns to Load tab, you can enter case insensitive value that will be treated as NULL. For example, "tbd", "n/a", "unknown", "-".
• Post loading, if any rows fail, up to 10 rejected rows display. To view more rows which have error, click Open in Object Browser button. You can optionally click Drop Table and Reload button to try the data load again.
• Settings change will change preview both in main and configure dialogs. They also affect data load result.
• Select table owner, enter table name and error table name

  Note:

  By default, all table names are converted to upper case. If data load succeeds without any error, the error table is dropped automatically.
• Post loading, a View Table button and Continue to Create Application Wizard button display if there are no failed rows. The load is successful if ta table is created and displayed.

5.1.5.2 Copying and Pasting CSV data or Selecting a Sample Data Set

You can copy and paste CSV data, or select a sample data set sample into the Load Data Wizard.

To load data by copying and pasting CSV data or selecting a sample data set:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Under Get Started, click Load Data.
   The Load Data Wizard appears.
5. Click Copy and Paste.
   Under Copy and Paste, you can either copy and paste CSV data or select a sample data set from the drop down menu.
6. Click Next.
7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make the appropriate changes and click Save Changes.
9. Click Load Data.

5.1.5.3 Loading an XML Document

Load an XML document using Load Data Wizard.

XML files can also be uploaded to Oracle APEX. However, APEX only supports flat XML structures which can easily be mapped to a table and columns. XML structures with multiple levels of nesting cannot be loaded by the Data Loading wizard.

To load an XML document:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate xml file.

   Note:

   There are no delimiter or enclosing characters to choose for XML file load. Also the column names are derived from the XML tag names, so there is no First lines contains headers checkbox. XML files are limited to 10MB each.
7. Under Load to Table, do the following:
   1. Table Owner - Select the table owner.
   2. Table Name - Enter the table name.
   3. Error Table Name - Enter the error table name to save errors during data load.

   Note:

   By default, all table names are converted to upper case. If data load succeeds without any error, the error table is dropped automatically.
8. (Optional) To view the full preview, configure data load settings, and set which columns to load, click Configure.
9. (Optional) Make appropriate changes and click Save Changes.
10. Click Load Data.

Once Data Loading is finished, a success message is displayed. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.

5.1.5.4 Loading a JSON File

Load a JSON file using Load Data Wizard.

Loading JSON files works similar to loading XML files.

Note:

Only flat structures are supported - deeply nested JSON structures cannot be loaded.

To load a JSON file:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate JSON file .

   Note:

   JSON files are limited to 20MB on 11g and 12.1 databases. For Oracle Database 12.2 or higher, there is no size limit for JSON files.

7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make the appropriate changes and click Save Changes.
9. Click Load Data.

   Once Data Loading is finished, a success message displays. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.

5.1.5.5 Loading a XLSX File

Load a XLSX file using Load Data Wizard.

Loading XLSX files is similar to loading CSV files. There is no need to detect or to choose delimiter or enclosing characters.

To load a XLSX file:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate XLSX file .

   Note:

   If the uploaded XLSX file contains multiple worksheets, Load Data wizard picks the first sheet by default. To load another sheet, pick it from the Select Sheet select list. The First lines contains headers checkbox works similar to CSV files. The same is true for the Configure dialog, the behavior is the same as it is for CSV files. Uploading XLSX files is limited to 20MB for each file.
7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make appropriate changes and click Save Changes.
9. Click Load Data.

   Once Data Loading is finished, a success message is displayed. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.