how to load csv file from a button in oracle apex screen, that should read data from csv file to table through plsql process code

Question

I am trying to upload a csv file from a button from oracle apex screen , i have used the below plsql code in my process

declare
     l_file_blob  blob;
     l_file_name  BFL_SVC_PROCESS_TRACKER_V24.file_path%type;
begin

   SELECT filename,blob_content
      INTO l_file_name,l_file_blob
      FROM APEX_APPLICATION_TEMP_FILES where name = :P401_SOURCE;

    insert into a  select col001 ,col002
      from 
           table( apex_data_parser.parse(
                      p_content                     => l_file_blob,
                      p_add_headers_row             => 'Y',
                      --
                      p_max_rows                    => 5,
                      p_skip_rows                   => 1,
                      p_csv_col_delimiter           => ';',
                      --
                      p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                      p_file_name                   => l_file_name ) ) p;commit;
 end;

But the data going into table is like below

1234,abcd,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 

What code need to be used to resolve this topic

Answer 1

You're not showing what data is in your file nor how you want it to be parsed (might be a good idea for your next post ;) )... but it looks like your columns are delimited by comma's and you're telling the API to split by semi-colons here: p_csv_col_delimiter => ';',.

I'd say this is working as expected. Since the data does not contain any semi colons, the whole row is treated as a single column value. Try using p_csv_col_delimiter => ',', instead.

Here is a handy trick for testing if the data will be parsed correctly:

SELECT line_number, col001, col002, col003, col004
  FROM TABLE ( apex_data_parser.parse(
                p_content => apex_util.clob_to_blob(
                  p_clob => 
'
"col1","col2","col3","col4"
1234,abcd,,
'
                 ,p_charset => 'AL32UTF8'
                )
               ,p_file_name => 'emp.csv' /*needed to get the file type*/
               ,p_skip_rows => 1 /*default 0*/
               ,p_csv_col_delimiter => ','
               ) );

LINE_NUMBE COL001     COL002     COL003     COL004    
---------- ---------- ---------- ---------- ----------
         2 1234       abcd