In this Tutorial, you will learn how to load data from text Data sources to database tables.

We will be using the Apex data parser package for data uploading (in orange color) in the screenshot below. The package contains a number of routines, but in this tutorial, we will use the parse pipeline table function (in purple color), which provides this output (in yellow color). There is a line number column, then columns named COL001 to COL300, so there is a maximum of 300 columns in the data.

For this exercise, we are going to use this EMP CSV file (in red color), which is placed in a directory named dmpdir4 on the database server (in green color). And this is the content of the CSV file (in yellow color). A link for all the files used in this exercise is provided Here.

These are the 4 steps for this exercise.

Step 1

First, create a directory on the server. a) We created a directory named dmpdir4 on the E Drive of our server. b) Then connect to your Oracle database instance as a system using the SQL command prompt. c) and d) On the SQL prompt execute the following two commands. Here DMS (in yellow color) is the schema that contains the table in which we will import data from the CSV file. e) Copy the downloaded emp CSV file to the dmpdir4 directory on the server.

This is the directory structure on our server (in the screenshot below) after completing step 1.

Step 2

Now, log in to your Apex workspace. We are connecting to the DMS workspace in which we will create a table to hold the uploaded data. Go to SQL command and execute this script. The script will create a function named file to blob (in green color) that will load the data from the CSV file into a blob (in orange color).

Step 3

Next, create a table that will be used to receive and hold the data from the CSV file. Create table code is provided in the Code.txt file (above).

Step 4

Finally, execute the PL SQL script to read and upload data from the CSV file. The first select statement in this script evaluates the number of Records in the CSV file using the parse function of the Apex data parser package (in yellow color). The parse function calls the file to blob function passing dmpdir4 folder and emp CSV file as parameters (in blue color). Then a for Loop is invoked to read the data from The Blob and write it to the EMP temp table (in orange colors).

And here's the outcome of this whole process. As you can see the employee's temporary table has been populated with data from the CSV file.

EMP XML File

In addition to the CSV file, you can also upload data in Oracle Apex from XML and Microsoft Excel files. The emp XML file contains data in this format.

In this case, we need to use the P row selector parameter (in red color) to show where the array of raw data can be found, which in this case is the results row tag (in yellow color).

Note that the column names are not included in the output by default, If you want to display the column names set the value of P_add_headers_row parameter from N to Y (in blue color). And click the Run button (in green color).

EMP Excel File

The EMP Excel file contains the CSV data from the EMP CSV loaded into Excel and saved as an XSLX file. There is only a single worksheet named EMP sheet.