Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Upload multiple excel files into tables using APEX

user651341
Member Posts: 48
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.
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.
Answers
-
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 -
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 -
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. -
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 -
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. -
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. -
Thank you for providing this, I'm sure others will find it useful
Regards
Andy
This discussion has been closed.