Loading External Data

Load external data from Excel, XML, JSON, or CSV files using declarative pages, page processes, or PL/SQL APIs.

Pair the file profile it represents with APEX_DATA_PARSER.PARSE to preview data or process it in custom ways. The same function can also infer a file profile at runtime to visualize or process external data with an unknown structure.

Create a Data Loading page using the wizard that lets users upload an external data file, preview the data, and load it. It combines all of the above features. When necessary, the APEX_ZIP package lets you process the contents of a compressed archive for bulk data loading use cases.

• Exploring External Data Examples
  Review external employee data examples in Excel, CSV, JSON, and XML formats.
• Creating a Data Load Definition
  Create a Data Load Definition using the wizard in Shared Components.
• Augmenting Data Load Profile Columns
  Add data profile columns to a Data Load Definition using SQL expressions or queries.
• Generating and Using a Data Loading Page
  Generate a Data Loading page with the Create Page Wizard to let users load data by choosing a file.
• Parsing Data Without Data Load Definition
  Parse external data without a Data Load Definition by letting APEX discover the file structure at runtime.
• Loading Multiple Files Including ZIPs
  Load employee data from multiple uploaded files, including ZIP archives, using Data Load Definitions and PL/SQL.

Review external employee data examples in Excel, CSV, JSON, and XML formats.

In the Woods HR application, HR reps sometimes need to load employee data from external files. An Excel spreadsheet, as shown below, is the most common format they encounter. The figure shows employee information in two rows of a spreadsheet featuring column headings in the first row: Employee Number, Last Name, Job Title, Manager ID, Hire Date, Salary, Commission, and Department No.

WORKER_NO,LAST_NAME,POSITION,BOSS_NO,ENTRY_DATE,PAY_RATE,BONUS_AMOUNT,SECTION_NO
8001,HOPPER,CLERK,7902,2025-03-14T00:00:00,900,,20
8002,CURIE,SALESMAN,7698,2025-11-05T00:00:00,1350,1200,30

{
    "items": [
        {
            "employeeId": 8003,
            "fullName": "GALLO",
            "positionTitle": "MANAGER",
            "reportsTo": 7839,
            "startDate": "2025-04-22",
            "salaryAmount": 2450,
            "commissionValue": "",
            "departmentCode": 10
        },
        {
            "employeeId": 8004,
            "fullName": "DARWIN",
            "positionTitle": "SALESMAN",
            "reportsTo": 7698,
            "startDate": "2025-09-10",
            "salaryAmount": 1600,
            "commissionValue": 300,
            "departmentCode": 30
        }
    ]
}

<?xml version="1.0"?>
<ROWSET>
    <ROW>
        <STAFF-ID>8005</STAFF-ID>
        <SURNAME>MENDEL</SURNAME>
        <ROLE>ANALYST</ROLE>
        <JOIN-DATE>2025-02-11T00:00:00</JOIN-DATE>
        <BASE-PAY>3200</BASE-PAY>
        <DIVISION>10</DIVISION>
    </ROW>
    <ROW>
        <STAFF-ID>8006</STAFF-ID>
        <SURNAME>LOVELACE</SURNAME>
        <ROLE>SALESMAN</ROLE>
        <SUPERVISOR-ID>7839</SUPERVISOR-ID>
        <JOIN-DATE>2025-08-27T00:00:00</JOIN-DATE>
        <BASE-PAY>2750</BASE-PAY>
        <ADDITIONAL_COMP>1300</ADDITIONAL_COMP>
        <DIVISION>30</DIVISION>
    </ROW>
</ROWSET>

Using a Data Load Definition shared component, your app can make quick work of loading external data in any of these formats.

Create a Data Load Definition using the wizard in Shared Components.

As shown below, you can target a table or collection, and specify the target object name. Here, your new Load Employees from Excel definition loads employee data into the EBA_DEMO_EMP table.

On the second step, upload an example file. It can be an Excel spreadsheet, CSV, JSON, or XML file.

When you proceed, the wizard uploads and analyzes the file's contents. It detects names, data types, and format masks as needed.

Then, as shown below, the Map Columns step shows the discovered Source Columns in a grid. Use the select list in each row of the Map To column to pick a target column for the data you want to load.

To preview the data, visit the Preview tab as shown below. Then click (Create Data Load) to complete the wizard.

Add data profile columns to a Data Load Definition using SQL expressions or queries.

For example, as shown below, the Last Name column in the Excel spreadsheet contains names in mixed case like Faraday and Noether.

The Woods HR app expects the ENAME column data to be uppercase, so adjust the Load Employees from Excel data load definition to convert each employee name before loading it. Edit the data load definition and open its Data Profile. As shown below, start by using the grid to edit the existing ENAME column to change its name to ORIG_ENAME, then click (Apply Changes).

Next, click the (Add Column > ) button in the Data Profile to add a new ENAME column, of type SQL Expression with Data Type VARCHAR2. Use the expression below, referencing other data profile columns names as necessary. The new ENAME column derives its value by uppercasing the value in the ORIG_ENAME column loaded from the spreadsheet.

upper( ORIG_NAME )

Generate a Data Loading page with the Create Page Wizard to let users load data by choosing a file.

• Creating a Data Loading Page
  Create a data loading page by choosing Data Loading in the Create Page Wizard.
• Using a Data Loading Page
  Run a generated Data Loading page to upload, preview, and load external employee data.
• Understanding Data Loading Pages
  Understand the features a generated Data Loading page combines to upload, preview, and load data.

Parse external data without a Data Load Definition by letting APEX discover the file structure at runtime.

In Previewing External Data Before Loading, the APEX_DATA_PARSER.PARSE function referenced an existing Data Load Definition by static ID. This is the most efficient approach if you know the structure of the data to load ahead of time. However, for more dynamic use cases you can also call PARSE to automatically discover the structure of a data file on the fly and return the data it contains in a row set with columns names from C001 to C300. It handles CSV, XML, JSON, and Excel data.

To work with the file profile information in a PL/SQL record structure instead, pass the file profile JSON to JSON_TO_PROFILE to get a T_FILE_PROFILE record structure.

Load employee data from multiple uploaded files, including ZIP archives, using Data Load Definitions and PL/SQL.

• Adding XML, CSV, JSON Data Load Definitions
  Add Data Load Definitions for XML, CSV, and JSON files that all load into the same target table.
• Setting File Upload to Handle Multiple Files
  Configure a File Upload item for multiple files and track processing results in hidden page items.
• Processing Multiple Uploaded Files
  Process multiple uploaded files with PL/SQL that chooses the right Data Load Definition and expands ZIP archives.
• Uploading Multiple Data Load Files
  Upload multiple employee data files and load them together, including files inside ZIP archives.

Create a data loading page by choosing Data Loading in the Create Page Wizard.

As shown below, just pick an existing Data Load Definition like Load Employees from Excel and click (Create Page). While the default Upload Data From setting of File is what you need here, notice it's also possible to create a page where the user can paste in the data to upload.

Run a generated Data Loading page to upload, preview, and load external employee data.

Running the generated Data Loading page, it displays a file drop zone as shown below.

After dragging and dropping an emp.xlsx Excel spreadsheet file onto the drop zone, the page uploads the file straight away and previews the data to load. Clicking (Load Data) completes the operation. The figure shows the generated Data Loading page's preview of the uploaded spreadsheet data.

The success message shown below confirms the number of rows processed.

When Susan, the HR Representative, visits the Employee Directory page, she sees the two new employees, with Employee Name values in uppercase, so the SQL Expression data profile column explained in Augmenting Data Load Profile Columns functioned as expected. The figure shows the Woods HR Employee Directory page, with its Interactive Report filtered to show employees with names FARADAY and NOETHER.

Understand the features a generated Data Loading page combines to upload, preview, and load data.

• Specifying the File and Uploading It
  Understand how the generated Data Loading page uploads a selected file and stores it temporarily..
• Validating the Uploaded File Type
  Understand how the generated Data Loading page validates the uploaded file before loading it.
• Letting User Select Worksheet
  Understand how the generated Data Loading page lets users choose a worksheet when an Excel file has more than one.
• Previewing External Data Before Loading
  Understand how the generated Data Loading page previews uploaded data before loading it.
• Loading Data with Native Page Process
  Understand how the generated Data Loading page uses a native Data Loading process to load the uploaded file.

Add Data Load Definitions for XML, CSV, and JSON files that all load into the same target table.

As shown below for the JSON file example, even though each file uses different nomenclature for the fields, they all target the EBA_DEMO_EMP table, so their Map To columns are the same.

Configure a File Upload item for multiple files and track processing results in hidden page items.

Process multiple uploaded files with PL/SQL that chooses the right Data Load Definition and expands ZIP archives.

A subsequent Execute Code page process references these translatable messages to compute the value of the hidden P33_SUCCESS_MESSAGE. It uses APEX_LANG.GET_MESSAGE and CASE expressions with to supply an appropriate number of placeholder values for a conditional translatable text message key.

:P33_SUCCESS_MESSAGE :=
    apex_lang.get_message(
        p_name => case when :P33_FILE_COUNT = 1
                       then 'ONE_FILE_ROWS_PROCESSED'
                       else 'MANY_FILES_ROWS_PROCESSED'
                  end,
        p_params => case when :P33_FILE_COUNT = 1
                         then apex_t_varchar2('0',:P33_PROCESSED_ROWS)
                         else apex_t_varchar2('0',:P33_FILE_COUNT,
                                              '1',:P33_PROCESSED_ROWS)
                  end);

The source for the EMPLOYEES_FOR_FILES procedure appears below. It loops over the uploaded files in temporary storage from the File Upload item passed in, and calls the DATA_LOAD_FOR_FILE helper procedure to perform the data load on the current file.

-- In package eba_demo_woodshr_load
procedure employees_from_files(
    p_file_names      in varchar2,
    p_file_count     out number,
    p_processed_rows out number,
    p_error_rows     out number)
is
begin
    p_file_count     := 0;
    p_processed_rows := 0;
    p_error_rows     := 0;

    -- Loop over uploaded files in temp storage from File Upload item
    for j in (select blob_content, filename, mime_type
                from apex_application_temp_files
               where name in (select column_value
                                from apex_string.split(p_file_names,':')))
    loop
        -- Load the employees from the current file
        data_load_for_file(
            p_content        => j.blob_content,
            p_filename       => j.filename,
            p_mime_type      => j.mime_type,
            p_file_count     => p_file_count,
            p_processed_rows => p_processed_rows,
            p_error_rows     => p_error_rows);
    end loop;
end employees_from_files;

The DATA_LOAD_FOR_FILE procedure looks at the extension of the passed-in file name as well as the MIME Type of the file if supplied to decide the suffix of the Data Load Definition to use. Depending on the match, the l_data_load_def_suffix ends up being csv, json, xml, excel, or zip. If nothing matches, it will be null.

If we're processing a ZIP file, then it calls the DATA_LOAD_FROM_ZIP helper procedure to process the contents of the zip file. Otherwise, if the data load suffix is not null, it appends the suffix to "load_employees_from_" to produce the static ID of the Data Load Definition to use. Finally, it passes this data load definition static id to APEX_DATA_LOADING.LOAD_DATA to perform the data load based on the file profile it specifies.

It keeps a running total of the number of files loaded and employees processed using the processed_rows field value from the t_data_load_result return value.

-- In package eba_demo_woodshr_load
----------------------------------------------
-- Load the employees data using the filename
-- extension or the mime type to determine
-- whether we're processing a zip file, or
-- a CSV, JSON, XML, or Excel file to data load
----------------------------------------------
procedure data_load_for_file(
    p_content        in blob,
    p_filename       in varchar2,
    p_mime_type      in varchar2,
    p_file_count     in out number,
    p_processed_rows in out number,
    p_error_rows     in out number)
is
    c_extension            constant varchar2(255)  := extension_of(p_filename);
    l_data_load_def_suffix          varchar2(255);
    l_result                        apex_data_loading.t_data_load_result;
begin
    l_data_load_def_suffix :=
        case
            when    c_extension in ('txt','csv')
                 or p_mime_type in ('text/plain','text/csv')
            then
                'csv'
            when    c_extension = 'json'
                 or p_mime_type = 'application/json'
            then
                'json'
            when    c_extension = 'xml'
                 or p_mime_type = 'text/xml'
            then
                'xml'
            when    c_extension in ('xls','xlsx')
                 or p_mime_type in ('application/vnd.ms-excel',
                                    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            then
                'excel'
            when    c_extension = 'zip'
                 or p_mime_type = 'application/zip'
            then
                'zip'
        end;
    if l_data_load_def_suffix = 'zip' then
        data_load_from_zip(
            p_zip_content    => p_content,
            p_file_count     => p_file_count,
            p_processed_rows => p_processed_rows,
            p_error_rows     => p_error_rows);
    elsif l_data_load_def_suffix is not null then
        l_result := apex_data_loading.load_data(
                        p_static_id => 'load_employees_from_'||l_data_load_def_suffix,
                        p_data_to_load => p_content);
        p_file_count     := p_file_count + 1;
        p_processed_rows := p_processed_rows + l_result.processed_rows;
        p_error_rows     := p_error_rows + l_result.error_rows;
    end if;
end data_load_for_file;

The DATA_LOAD_FROM_ZIP uses APEX_ZIP.GET_FILES and a FOR loop to process the list of files contained in the zip file. It calls DATA_LOAD_FOR_FILE to get the job done.

-- In package eba_demo_woodshr_load
----------------------------------------------
-- Load the employees data files contained in
-- the supplied zip file blob
----------------------------------------------
procedure data_load_from_zip(
    p_zip_content    in blob,
    p_file_count     in out number,
    p_processed_rows in out number,
    p_error_rows     in out number)
is
    l_files        apex_zip.t_files;
    l_file_content blob;
begin
    -- Get list of files from the zip
    l_files := apex_zip.get_files(p_zip_content);

    -- Process the individual files inside the zip
    for j in 1..l_files.count loop

        -- Get the contents of the current file in the zip file
        l_file_content := apex_zip.get_file_content(p_zip_content,l_files(j));

        -- Load employees from the current file
        data_load_for_file(
            p_content        => l_file_content,
            p_filename       => l_files(j),
            p_mime_type      => null,
            p_file_count     => p_file_count,
            p_processed_rows => p_processed_rows,
            p_error_rows     => p_error_rows);
    end loop;        

Upload multiple employee data files and load them together, including files inside ZIP archives.

When an HR Rep like Susan visits the Load Employees page, as shown below, she can now pick an Excel file, CSV file, JSON file, and XML file all at once, and click (Load) to perform the bulk employee data load.

The custom, conditional success message confirms that four files were loaded containing eight total new employees.

The same works if Susan submits a mix of files and zip files. All the different XML, CSV, JSON, and Excel files containing employee data get loaded according to their respective Data Load Definitions, including ones that were part of any submitted ZIP files.

Understand how the generated Data Loading page uploads a selected file and stores it temporarily..

The Load Employees from Excel page shown below is a wizard-created page. It contains the P32_FILE File Upload page item configured to display as a Block Dropzone. It stores the uploaded file in the Table APEX_APPLICATION_TEMP_FILES for the duration of the user session. By default, it's set to upload a single file at a time.

The Upload a File dynamic action event handler reacts to the P32_FILE File Upload item's Change event. This happens when the user chooses a file to upload. It triggers an immediate page submit using the native Submit Page action. This uploads the file to the server. The figure shows the Submit Page dynamic action step selected in the component tree and highlights the Submit Page action it's configured to perform.

An After Submit computation shown below populates the display only P32_FILE_NAME page item with the uploaded file name. It selects the FILENAME column from the APEX_APPLICATION_TEMP_FILES table using the unique uploaded file name in the P32_FILE File Upload item.

Then, since the page contains no branches to navigate elsewhere the page re-renders after processing the page submit.

Understand how the generated Data Loading page validates the uploaded file before loading it.

A validation on the P32_FILE File Upload page item ensures only the expected file type is submitted. As shown below, it uses the following PL/SQL code that calls ASSERT_FILE_TYPE in the APEX_DATA_PARSER package to test if the submitted file is an Excel spreadsheet. It passes in the value of P32_FILE_NAME, the File Upload item. It contains the unique uploaded file name that identifies the file in the APEX_APPLICATION_TEMP_FILES table. Notice it references the C_FILE_TYPE_XLSX constant in the same package to indicate the type of file to check. If true, the validation succeeds. If false, it clears the File Upload item by assigning null to it and returns false to signal the validation failed.

if apex_data_parser.assert_file_type(
       p_file_name => :P32_FILE_NAME,
       p_file_type => apex_data_parser.c_file_type_xlsx )
then
    return true;
else
    :P32_FILE := null;
    return false;
end if;

Understand how the generated Data Loading page lets users choose a worksheet when an Excel file has more than one.

The P32_XLSX_WORKSHEET select list has a server-side condition so it displays only when the uploaded file contains multiple worksheets. The Function Body type condition below references the unique uploaded file name in P32_FILE in a SQL query that counts the number of worksheets. It joins the APEX_APPLICATION_TEMP_FILES table with the results of the GET_XLSX_WORKSHEETS function in the APEX_DATA_PARSER package. Then, it returns true to display the select list if the sheet count is greater than one.

declare
    l_sheet_count number;
begin
    select count(*)
      into l_sheet_count
      from apex_application_temp_files f,
           table( apex_data_parser.get_xlsx_worksheets(
                     p_content => f.blob_content ) ) p
     where f.name = :P32_FILE;

     -- display if the XSLX file contains multiple worksheets
    return ( l_sheet_count > 1 );
exception
    when others then
        return false;
end;

select p.sheet_display_name,
       p.sheet_file_name
  from apex_application_temp_files f,
       table( apex_data_parser.get_xlsx_worksheets( p_content => f.blob_content ) ) p
 where f.name = :P32_FILE

Since the data to load is different on each sheet, a dynamic action shown below reacts to the value change in the P32_XLSX_WORKSHEET_NAME select list to submit the page. When the page re-renders after this submit, the Preview region displays the data preview for the newly selected sheet. The figure shows the Submit Page dynamic action step selected in the components tree and highlights its Submit Page action.

Understand how the generated Data Loading page previews uploaded data before loading it.

The Preview region is a Classic Report with a server-side condition to display only when the P32_FILE Item is not null. In other words, it shows only after the user uploads a file. Its query below shows up to 100 rows of data from the file using APEX_DATA_PARSER.PARSE to read the data using the file profile in the Data Load Definition with static ID load_employees_from_excel. The query uses the unique uploaded file name in the File Upload page item P32_FILE to access the uploaded spreadsheet BLOB_CONTENT from the APEX_APPLICATION_TEMP_FILES table. It passes in the user-selected sheet name by referencing the P32_XLSX_WORKSHEET_NAME page item.

select p.line_number,
       p.col001, p.col002, p.col003, p.col004, p.col005,
       p.col006, p.col007, p.col008, p.col009, p.col010
       -- add more columns (col011 to col300) here.
  from apex_application_temp_files f,
       table( apex_data_parser.parse(
                  p_content         => f.blob_content,
                  p_file_name       => f.filename,
                  p_xlsx_sheet_name => case when :P32_XLSX_WORKSHEET is not null
                                            then :P32_XLSX_WORKSHEET end,
                  p_file_profile    => apex_data_loading.get_file_profile(
                                          p_static_id => 'load_employees_from_excel'),
                  p_max_rows        => 100 ) ) p
 where f.name = :P32_FILE

The figure shows the Preview classic report region selected in the component tree and highlights its data source SQL query it uses to preview the data from the uploaded file.

The Preview region has its Heading > Type set to None on the Attributes tab of the Property Editor. As shown below, ensure the Sorting > Default Sequence of the LINE_NUMBER column is set to one (1) with Ascending sort direction so the user sees the data preview in line number order.

Understand how the generated Data Loading page uses a native Data Loading process to load the uploaded file.

After reviewing the data preview, when the user clicks the (Load) button to load the data, it submits the page and executes the Load Data page process. As shown below, its server-side condition ensures it runs only when the LOAD button is pressed.

The Load Data page process is a native Data Loading process type, with its Data Load Definition set to Load Employees from Excel.

While it can also load data from a text area, or a SQL query returning a CLOB or BLOB, here it's set to use the contents of the P32_FILE File Upload item's temporary file. It configures the P32_XSLX_WORKSHEET item to supply the worksheet name, if any.