CSV File.

Import CSV File Using New Oracle Apex 19.1 Data Loading Feature

by

Oracle Apex 19.1 introduced a new feature to load data in CSV, XLSX, XML, and JSON format. In this tutorial, you will learn how to import a CSV file using the new Oracle data loading feature.

Steps To Import CSV File Using Apex 19.1 Data Loading Feature

  1. Open Oracle Apex and login to your application by providing the Workspace, Schema, and Password information.
  2. Then click on the menu SQL Workshop > Utilities > Data Workshop as shown in the below image:

Apex 19.1 Data Loading

  1. Now click on the Load Data button to import the data from a CSV, XLSX, XML, and JSON file format to Oracle table, as shown in the below image:

Load Data screen.

  1. On the next screen, it will ask you to select the file. You can click on the Choose File button to browse and select the file, or you can drag and drop to that area, as shown in the below image:

Apex 19.1 - Select file.

  1. After specifying the CSV file, it will take you to the following screen, where you can select the delimiter, enclosing character and the file encoding options. Also, you can select/deselect columns by clicking on the Configure button.

Apex 19.1 - Data Loading Configuration

  1. After specifying the configuration, before clicking on the Load Data button, specify the staging table name to load the data. There is no option in Apex 19.1 to load data into an existing table in Oracle. You need to provide a new table which you can use as a staging table, and after that, you can transfer the data from the staging table to the actual table. Below is the example:

Apex 19.1 - Specify Tables.

  1. Now click on the Load Data button to import the CSV file in the specified table. You will get the successful import message as shown below:

Apex 19.1 - Import successfully completed.Your import is successfully completed, you can verify the data by querying the above table:

SELECT * FROM EMPLOYEES_STAGING;

Output

SQL Query Output

To transfer data from this staging table to the actual table you can create a stored procedure to validate and import the data or you can run the following command:

INSERT INTO employees
   SELECT EMPLOYEE_ID,
          FIRST_NAME,
          LAST_NAME,
          EMAIL,
          PHONE_NUMBER,
          TO_DATE (HIRE_DATE, 'dd/mm/yyyy'),
          JOB_ID,
          SALARY,
          COMMISSION_PCT,
          MANAGER_ID,
          DEPARTMENT_ID
     FROM employees_staging;

Note

I have experienced some issues when I was trying to import the file in Excel (XLSX) format. It was not importing the string columns. I tried to sort out but it did not work. So I saved the XLSX file in CSV format to import.

Maybe you can try also to import in Excel (XLSX) format in Oracle Apex 19.1 and share your experience in the comments section below. Thanks.

See also:

3 thoughts on “Import CSV File Using New Oracle Apex 19.1 Data Loading Feature”

  1. Vinish I tried creating data load wizard in front end rataher than from sql workshop, the issue I faced is validating the data, I mean I have a column named Count in the staging table (I created the table not the system generated during load) and data data type of the column is number. Now in the file I made one record under count column to varchar, I uploaded, I was expecting an error as wrong data type but for some reason no errors were thrown but that one row is not inserted into table again but no failed records were shown at the end.

    Reply

    • In Oracle, number data can be stored in varchar2, it will not show any error.

      For not importing data issue, you have to check the file format and the column defined in the import wizard.

    • Hi vinish,

      I know why the importing of that record is failed, coz tried to insert varchar in numeric column, but what I am expecting is to create some validation or something that will check data for data types to its matching columns and fail if discrepancies are there .

Leave a Comment