Simple file upload using APEX_DATA_PARSER

I

I have been asked multiple times about CSV files processing recenly. I guess CSV files are not going away any time soon.

Excel (.xlsx) files, CSV, XML and JSON files can be uploaded and processed by awesome APEX_DATA_PARSER package. You can check my more sophisticated uploader I wrote few years ago, but this short tutorial is a great way how to start.

First you have to upload your file, you can use File page item for that. Then you will see list of uploaded files (for your active session) and file profile, columns and data. All of this is achieved by simple queries:

Uploaded files:

SELECT
    f.id,
    f.filename AS file_name,
    f.name,
    f.mime_type,
    f.created_on
FROM apex_application_temp_files f
WHERE f.application_id = :APP_ID;

File profile:

SELECT
    f.name,
    f.mime_type,
    REGEXP_SUBSTR(f.name, '([^/]*)$') AS file_name,
    --
    JSON_VALUE(f.profile_json, '$."file-encoding"'          RETURNING VARCHAR2)     AS file_encoding,
    JSON_VALUE(f.profile_json, '$."csv-delimiter"'          RETURNING VARCHAR2)     AS csv_delimiter,
    JSON_VALUE(f.profile_json, '$."csv-enclosed"'           RETURNING VARCHAR2)     AS csv_enclosed,
    JSON_VALUE(f.profile_json, '$."headings-in-first-row"'  RETURNING VARCHAR2)     AS headings,
    JSON_VALUE(f.profile_json, '$."force-trim-whitespace"'  RETURNING VARCHAR2)     AS whitespace,
    --JSON_VALUE(f.profile_json, '$."columns".size()'       RETURNING NUMBER)       AS cols_,
    JSON_VALUE(f.profile_json, '$."parsed-rows"'            RETURNING NUMBER) - 1   AS rows_
FROM (
    SELECT
        f.name,
        f.mime_type,
        APEX_DATA_PARSER.DISCOVER (
            p_content           => f.blob_content,
            p_file_name         => f.name,
            p_max_rows          => 100000
        )                       AS profile_json
    FROM apex_application_temp_files f
    WHERE f.name = :P1_FILE
) f;

File columns:

SELECT
    column_position                 AS column_id,
    REPLACE(column_name, ']', '_')  AS column_name,
    data_type,
    REPLACE(format_mask, '"', '')   AS format_mask
FROM TABLE(APEX_DATA_PARSER.GET_COLUMNS((
    SELECT
        APEX_DATA_PARSER.DISCOVER (
            p_content           => f.blob_content,
            p_file_name         => f.name,
            p_max_rows          => 100000
        ) AS profile_json
    FROM apex_application_temp_files f
    WHERE f.name = :P1_FILE
)));

File content:

SELECT
    p.line_number - 1 AS line_number,
    --
    p.col001, p.col002, p.col003, p.col004, p.col005, p.col006, p.col007, p.col008, p.col009, p.col010,
    p.col011, p.col012, p.col013, p.col014, p.col015, p.col016, p.col017, p.col018, p.col019, p.col020,
    p.col021, p.col022, p.col023, p.col024, p.col025, p.col026, p.col027, p.col028, p.col029, p.col030
    -- col300
FROM apex_application_temp_files f
CROSS JOIN TABLE(APEX_DATA_PARSER.PARSE(
    p_content           => f.blob_content,
    p_file_name         => f.name,
    p_skip_rows         => 1
)) p
WHERE f.name = :P1_FILE;

You can use up to 300 columns. I use page items and simple process to rename headers for the data columns:

FOR c IN (
    SELECT
        'P1_COL' || LPAD(column_position, 3, '0')   AS page_item,
        REPLACE(column_name, ']', '_')              AS column_name
    FROM TABLE(APEX_DATA_PARSER.GET_COLUMNS((
        SELECT
            APEX_DATA_PARSER.DISCOVER (
                p_content           => f.blob_content,
                p_file_name         => f.name,
                p_max_rows          => 100000
            ) AS profile_json
        FROM apex_application_temp_files f
        WHERE f.name = :P1_FILE
    )))
) LOOP
    IF APEX_CUSTOM_AUTH.APPLICATION_PAGE_ITEM_EXISTS(c.page_item) THEN
        APEX_UTIL.SET_SESSION_STATE(c.page_item, c.column_name);
    END IF;
END LOOP;

Now you have to decide what to do with the data. Push them into APEX_COLLECTION or to a real table? That is up to you.

You can download the Simple Uploader app from my GitHub.