14.5 GET_FILE_PROFILE Function

This function returns the current file profile in JSON format. A file profile is generated when the parse() table function runs and no file profile is passed in. The file profile contains meta data about the parsed files such as the CSV delimiter, the XLSX worksheet name and the columns found during parsing and their data types.

The typical call sequence is as follows:

1. Invoke PARSE - Use this table function to parse the files and get rows and columns in order to display a data preview. While the function runs it computes the file parser profile which can be used in subsequent calls in order to further process the data.
2. Invoke GET_FILE_PROFILE - Retrieve file profile information in JSON format.
3. Process the data

Syntax

FUNCTION GET_FILE_PROFILE RETURN CLOB;

Parameter

None.

Returns

Returns file profile of the last PARSE() invocation in JSON format.

Example

select line_number, col001,col002,col003,col004,col005,col006,col007,col008 
   from table( 
              apex_data_parser.parse(
                  p_content         => {BLOB containing XLSX file},
                  p_file_name       => 'test.xlsx',
                  p_xlsx_sheet_name => 'sheet1.xml') ) ;

LINE_NUMBER COL001   COL002       COL003       COL004   COL005          COL006   COL007       COL008 
----------- -------- ------------ ------------ -------- --------------- -------- ------------ ------------- 
          1 0        First Name   Last Name    Gender   Country         Age      Date         Id       
          2 1        Dulce        Abril        Female   United States   32       15/10/2017   1562     
          3 2        Mara         Hashimoto    Female   Great Britain   25       16/08/2016   1582     
          4 3        Philip       Gent         Male     France          36       21/05/2015   2587     
          5 4        Kathleen     Hanner       Female   United States   25       15/10/2017   3549     
          6 5        Nereida      Magwood      Female   United States   58       16/08/2016   2468     
          7 6        Gaston       Brumm        Male     United States   24       21/05/2015   2554     
          8 7        Etta         Hurn         Female   Great Britain   56       15/10/2017   3598     
          9 8        Earlean      Melgar       Female   United States   27       16/08/2016   2456     
         10 9        Vincenza     Weiland      Female   United States   40       21/05/2015   6548     
          : :        :            :            :        :               :        :            :

select apex_data_parser.get_file_profile from dual;

{
    "file-type" : 1,
    "csv-delimiter" : "",
    "xslx-worksheet" : "sheet1.xml",
    "headings-in-first-row" : true,
    "file-encoding" : "AL32UTF8",
    "single-row" : false,
    "parsed-rows" : 2378,
    "columns" : [
       {
          "format-mask" : "",
          "name" : "C0",
          "data-type" : 2,
          "selector" : ""
       },
       {
          "name" : "FIRST_NAME",
          "data-type" : 1,
          "selector" : "",
          "format-mask" : ""
       },
       {
          "selector" : "",
          "data-type" : 1,
          "name" : "LAST_NAME",
          "format-mask" : ""
       },
       {
          "format-mask" : "",
          "data-type" : 1,
          "name" : "GENDER",
          "selector" : ""
       },
       {
          "name" : "COUNTRY",
          "data-type" : 1,
          "selector" : "",
          "format-mask" : ""
       },
       {
          "data-type" : 2,
          "name" : "AGE",
          "selector" : "",
          "format-mask" : ""
       },
       {
          "format-mask" : "DD\"/\"MM\"/\"YYYY",
          "selector" : "",
          "data-type" : 3,
          "name" : "DATE_"
       },
       {
          "name" : "ID",
          "data-type" : 2,
          "selector" : "",
          "format-mask" : ""
       }
    ],
    "row-selector" : ""
}