Using APEX_DATA_PARSER to Load CSV and Excel Files in APEX

User Driven Data Imports

Many APEX applications need to let users upload spreadsheet data: importing contacts from a CSV file, loading budget figures from Excel, or processing transaction files from external systems. The APEX_DATA_PARSER package, introduced in APEX 19.1, provides a powerful server side parser that handles CSV, XLSX, XML, and JSON files with automatic column detection and data type inference.

Basic File Upload and Parse

Start with a File Browse item on your page. Then parse it using APEX_DATA_PARSER in a PL/SQL process:

DECLARE
    l_blob BLOB;
BEGIN
    SELECT blob_content
      INTO l_blob
      FROM apex_application_temp_files
     WHERE name = :P10_FILE_UPLOAD;

    INSERT INTO staging_employees (first_name, last_name, email, department, salary)
    SELECT col001, col002, col003, col004, TO_NUMBER(col005)
      FROM TABLE(
          APEX_DATA_PARSER.PARSE(
              p_content   => l_blob,
              p_file_name => :P10_FILE_UPLOAD,
              p_skip_rows => 1
          )
      );
    COMMIT;
END;
/

Column Discovery

APEX_DATA_PARSER can automatically detect column names and data types from the file. Use the DISCOVER procedure to examine the file structure before loading:

SELECT *
  FROM TABLE(
      APEX_DATA_PARSER.DISCOVER(
          p_content   => l_blob,
          p_file_name => 'employees.xlsx'
      )
  );

This returns detected column names, data types, and format masks, which you can present to the user for confirmation before proceeding with the actual data load.

Handling Multiple Sheets

For Excel files with multiple worksheets, use the p_xlsx_sheet_name parameter to specify which sheet to parse. You can get the list of sheet names using the GET_XLSX_WORKSHEETS function and present them in a select list for the user to choose from.

Error Handling and Validation

Real world uploaded files are messy. Load the parsed data into a staging table first, validate it, show the user a preview with any errors highlighted, and only move it to production tables after the user confirms the data looks correct. This preview-validate-confirm pattern is far more user friendly than attempting a direct load and showing database error messages when something goes wrong.