top of page
  • naveenaggarwal12


The APEX_DATA_PARSER is a frequently used API which provides an easy interface to parse files from different file formats such as comma-separated-values (.csv), json, xml and xlsx. It was first introduced in APEX 19.1 version

It also helps us to analyze a file to understand its format and structure and identify the field names and datatypes of the fields.

In order for APEX_DATA_PARSER to parse a file, it must be in a BLOB format. We can make use of APEX_WEB_SERVICE API for this purpose.

The apex_web_service.make_rest_request_b is a PL/SQL function which invokes a RESTful style Web service and returns the results in a BLOB.

The parser is implemented as a table function - so the developer accesses parser results like a table. Therefore, the parser can utilize INSERT ... SELECT statements to insert rows directly into a table from the specified file.

In today's blog I am going to show a simple example of file parsing using APEX_DATA_PARSER API.

Step 1: I uploaded a simple .csv file into my Github repository. The link for the same is as follows :

Step 2: Created a table (name: API_DATA) to hold the .csv file data. Here I am using replica of EMP table to hold data from emp.csv file.

Step 3: The function supports parsing up to 300 columns


insert into API_DATA
select col001, col002, col003, col004, col005, col006, col007, col008, col009
from table
( apex_data_parser.parse
(  p_content=> apex_web_service.make_rest_request_b('', 'GET')
, p_file_name=> 'emp.csv', p_skip_rows=> 1 )

This script uses a REST request to GET the table data in csv format from the github website, parses the csv using APEX_DATA_PARSER and then insert the data into the destination table API_DATA.

Description of the parameters used above:

  • P_CONTENT - the file content to be parsed as a BLOB

  • P_FILE_NAME - the name of the file; this used to derive the file type.

  • .xml extensions will be treated as XML files

  • .json, .geojson or .js extensions will be treated as JSON files

  • .csv or .txt extensions will be treated as delimited files

  • .xlsx extensions will be treated as Excel Workbooks (only the XLSX format is supported)

  • P_SKIP_ROWS - skip the first N rows before parsing the rest of the file.

There are many more parameter also which can be used if required.

Step4 : When we run the above script it inserts the data in the table.

APEX_DATA_PARSER also discovers information on columns and data types, which can be retrieved from a collection or directly using APEX_DATA_PARSER.DISCOVER and APEX_DATA_PARSER.GET_COLUMNS functions.

APEX_DATA_PARSER expects the file contents simply as a BLOB - it does not matter where this BLOB comes from. While we used a file upload in this example, the BLOB can also come from an existing table or from the web using the APEX_WEB_SERVICE or UTL_HTTP packages.

Go through the following link to read more about the APEX_DATA_PARSER

I hope you enjoyed reading my post.

Thanks for your time !!

1,054 views0 comments

Recent Posts

See All
bottom of page