Upload multiple excel files into tables using APEX

user651341

Hi folks,
I'm wondering if anyone has ever uploaded multiple .csv files simultaniously and store the data into the database using APEX XE before.
I can browse for a single file, and execute that okay and a good example of doing that can be found at http://advait.wordpress.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
This works fine when the user browses to a specific file on their network and then uploads the data from that one file.
However I need the ability to 'grab' every file in a specific directory one after the other to process rather than having to specify which one to load everytime, and wondered if anyone has come across this before.

Many thanks

Graham.

ATD

Hi Graham,

Unfortunately, you can't do this any more as modern browsers do not allow you to retrieve directory listings and code the file upload functionality. The only way you can automate a directory upload would be through the backend using the UTL_FILE package functionality.

Andy

TexasApexDeveloper

Graham,
You could use pices of this routine and modify the interface.. Instead of a user selecting the file with a directory lookup, you would get the directory information from the server directory, load it into a collection and loop through the collection passing the collection item that contains the file name to the load process..

Thank you,

Tony Miller
Webster, TX

user651341

Okay, thanks for the quick replies.
I'll try using UTL_FILE and see how that goes by looping through the directory and update the thread.
If you have done this before yourselves, then additional advise would be much appreciated.

Thanks
Graham.

ATD

Hi Graham,

Unfortunately (or, fortunately, perhaps?!?!), I haven't needed to do this. As this is not Apex specific, you may find it better to post a question on the general database forums instead - there's bound to be someone on there that has done this or, at least, can give you better pointers than I could. I've tried googling for this, but haven't yet found anything other than how to upload a specifically named file.

Andy

M Tajuddin

Hello,

Take a look at these threads which might outline different ways to do it -

624911

547565

336988

Hope this helps,

M Tajuddin
http://tajuddin.whitepagesbd.com

user651341

Thanks guys,
Had a look at the threads you attached and I think for my needs the best one is 547565

I shall try out over the next few days and let you know how it goes.

Regards
Graham.

user651341

Just for completeness ...

Got this to work, but it's a pl/sql issue as opposed to an APEX issue.
Anyway, if anyone needs to have the ability to read multiple files then a quick easy way to do it (as lomg as they know the file names that will be read), is to create a directory on the database which points to the actual harddrive on your PC, then create a table (called an external table) and read from that external table as if it was an actual database table ...

1 - Log on as sys and grant CREATE ANY DIRECTORY to whatever user you are logging in as (assuming you are not using sys to create apps)

2 - Create a directory e.g....CREATE OR REPLACE DIRECTORY GB_TEST AS 'c:\gbtest';

3 - Create an external table as ...

CREATE TABLE gb_test
(file_name varchar2(10),
rec_date date
rec_name VARCHAR2(20),
rec_age number,
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY GB_TEST
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('data1.csv','data2.csv','data3.csv','data4.csv')
)
PARALLEL 5
REJECT LIMIT 20000;


That's it then ...

select * from gb_test
where file_name = 'xxx'

will return all the data where the file_name = 'xxx'
very easy to use.

ATD

Thank you for providing this, I'm sure others will find it useful

Regards

Andy