Skip to Main Content

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!

import excel file

jacknomizOct 29 2019 — edited Oct 29 2019

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.

Comments

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: SYS

CREATE 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: HR

declare

  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

1 - 7

Post Details

Added on Oct 29 2019
7 comments
222 views