import excel file

jacknomiz

Hi, I'm using APEX 19.1.

I need to upload Excel xlsx file into blob and then process it into tables.

Is there a native APEX functionality to achieve this, or do I need to revert to csv format?

There are some posts here about 'Antons plugin', curious if it is the preferred way or has anybody used APEX_DATA_PARSER?

Many thanks in advance.

Ora_Learner890

in my opinion, if it is possible to have csv file it is far better than excel file because it is originally a text file which can be read in any environment through various methods, means no limitation at all, be FREE to use it anywhere, in any device. one can use (as I am doing like various others) oracle native's to read / write these files using UTL_FILE as below:

example below is to CREATE a CSV file and add data. to read a file just enter 'r' (read) instead of 'w' (write) or 'a' (append)

user: SYSCREATE OR REPLACE DIRECTORY etl_dir AS 'c:\etl_dir';GRANT READ, WRITE ON DIRECTORY etl_dir TO hr;GRANT EXECUTE ON UTL_FILE TO hr;user: HRdeclare  mydir  varchar2(64) := 'ETL_DIR';  myfile utl_file.file_type;  filename varchar2(100) := 'temp.csv';  cursor c1 is select employee_id, last_name, to_char(hire_date,'YYYYMMDD') hiredate from hr.employees;begin  myfile := utl_file.fopen(mydir, filename, 'w');  utl_file.put_line(myfile, 'EMPID' ||','||                            'LNAME' ||','||                            'DATE' );  utl_file.fclose(myfile);  --  myfile := utl_file.fopen(mydir, filename, 'a');  -- to show append feature of utl_file  for i in c1 loop    utl_file.put_line( myfile,                     i.employee_id  ||','||                     i.last_name    ||','||                     i.hiredate);  end loop;  utl_file.fclose(myfile);exception  when others then utl_file.fclose(myfile);  -- as per advise, WHEN OTHERS should avoid   raise;end;/

jacknomiz

Yeah, I prefer csv file too, nice and simple, but users insist on the xlsx.

Ora_Learner890

there is a Package AS_XLSX which is not a plugin, use it anywhere.

https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

Ora_Learner890

may be you find useful as per your requirement(s)

https://blogs.oracle.com/apex/quick-and-easy-data-loading-with-apex-191

( here scroll down or just find/search for:  loading xlsx files )

or here

https://jeffkemponoracle.com/2018/11/load-spreadsheet-data-into-apex/

( here scroll down or just find/search for: solutions for excel files )

hope these will help

jacknomiz

Hey thanks, looks like there is hope. Will try this one first - it will be perfect if it works

https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

Ora_Learner890

yeah, the same guy as i have shared the first link in my reply #4. anyway, hope you will find a better solution. just a request, which ever solution worked for you, give here feedback so, others can get benefit too

we all are continuously in learning stage.

EDIT: yeah, it is better than what I have shared in reply #4

jacknomiz

Yeah absolutely, this forum is great - always helpful solutions and good ideas. Priceless