Oracle Apex - PL/SQL - Apex_data_parser reading csv files

Jasper Tanglib

Hi,

In Oracle 20.2, I have a code that reads the csv file uploaded into wwv_flow_files. This will display the contents of a csv file which I would then use to insert into a table.

My code:

SELECT  col001 RN, col002 INST_ID, col003 NAME, col004 VALUE, col005 DISPLAY_VALUE

FROM wwv_flow_files f,

TABLE ( apex_data_parser.parse(

        p_content                     => f.blob_content,

        p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',

        p_file_name                   => f.filename,

        p_skip_rows                   => 2 ) ) p

WHERE name LIKE '%all_parameters.csv'

I'm expecting that the csv file is read like this (it is separated by a delimiter of 3 characters <,>). This is what it looks like when I open my csv file through notepad(.txt):

But using my code, the output of the csv file read is like this. The 4th and 5th column should actually be in only 1 column:

If I open the csv file using its .csv extension, it looks like this. It is detecting the comma (,) as something else and splitting the values into different columns.

I tried adding the p_csv_delimiter in my parameter but it just made the data worse.

SELECT  col001 RN, col002 INST_ID, col003 NAME, col004 VALUE, col005 DISPLAY_VALUE

FROM wwv_flow_files f,

TABLE ( apex_data_parser.parse(

        p_content                     => f.blob_content,

        p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',

        p_file_name                   => f.filename,

        p_skip_rows                   => 2,

        p_csv_row_delimiter               => '<,>' ) ) p

WHERE name LIKE '%all_parameters.csv'

How can I have apex_data_parser.parse read my CSV file in the format it would have when opened in .txt extension.

Any ideas or suggestions as appreciated,

-Jazz

jariola

Have you try set apex_data_parser.parse parameter p_csv_col_delimiter?

Jasper Tanglib

Hi @jariola never thought that'd actually work. Thank you!

Ultimately I wanted the comma (,) to be read as part of the data and not be split into different columns, and it seems p_csv_col_delimiter solved that.

But now, the data has an extra ,>.

I tried changing the value of p_csv_col_delimiter from <,> into just < or <, or ,> and it returned the same data

It seems it does not see the delimiter as 3 characters but only takes < as the delimiter, do you have any idea on this.

I know I can remove the ,> from the data by using regexp_replace but then I'll be doing that for the next 50 files and xxx columns so if you have an idea how I should give the value in p_csv_col_delimiter that would be a huge help

jariola

Have you try set apex_data_parser.parse parameter p_csv_col_delimiter?

Jasper Tanglib

Hi @jariola never thought that'd actually work. Thank you!

Ultimately I wanted the comma (,) to be read as part of the data and not be split into different columns, and it seems p_csv_col_delimiter solved that.

But now, the data has an extra ,>.

I tried changing the value of p_csv_col_delimiter from <,> into just < or <, or ,> and it returned the same data

It seems it does not see the delimiter as 3 characters but only takes < as the delimiter, do you have any idea on this.

I know I can remove the ,> from the data by using regexp_replace but then I'll be doing that for the next 50 files and xxx columns so if you have an idea how I should give the value in p_csv_col_delimiter that would be a huge help

jariola

It might parameter p_csv_enclosed will not help either, if set that to ,>? Sorry, I don't have any other ideas.

jariola

If that p_csv_enclosed not help, you can set parameter p_store_profile_to_collection to get file profile to apex_collections. Maybe you can cook something out of that and dynamically remove unwanted  ,> ?

AndyH

I suspect that the check for the separator is using an INSTR to identify where the separator starts and so identify the end of the current column and then uses that position plus 1 as the start of the following column - ignoring the fact that your separator is more than one character in length.