APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Apex - PL/SQL - Apex_data_parser reading csv files

Jasper TanglibApr 14 2021

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

This post has been answered by jariola on Apr 14 2021

Jump to Answer

Frank Kulash

Hi, User_4G3I3
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. For PL/SQL questions, include a complete working block that does the parts you already know how to do. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).
So how can I detect every new ID
Create a variable to hold the value of the previous id. Initialize it to something that can not be a value of id. Inside the beginning of the loop say

IF  id = prev_id THEN ...

At the end of the loop, say

prev_id := id;

Alternatively, you could add a column in the query that tells if a row is the first row for the id, using an analytic function such as LAG or ROW_NUMBER.

1 person found this helpful

Solomon Yakobson

First of all there is no row order in relational table. Order is provided vi ORDER [SIBLINGS] BY clause. So I will assume rows for same ID should be ordered by PAYMENT. Then there is no need to PL/SQL. All you need is:

SELECT  ID,
        MIN(PAYMENT)
  FROM  YOUR_TABLE
  GROUP BY ID
  ORDER BY ID
/

SY.

User_4G3I3

Sorry, I am new to this community and I will follow your guidelines next time :)
Here is the block of code. I am using the database from this textbook (Oracle 11g pl sql programming 2nd edition casteel)
SQL developer Version 21.2.1.204 Build 204.1703
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
example (1).JPG