Forum Stats

  • 3,837,553 Users
  • 2,262,270 Discussions
  • 7,900,318 Comments

Discussions

Upload multiple excel files into tables using APEX

user651341
user651341 Member Posts: 48
edited Oct 8, 2008 1:50AM in APEX Discussions
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.
Tagged:

Answers

  • ATD
    ATD Member Posts: 11,184
    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
    TexasApexDeveloper Member Posts: 7,941 Gold Crown
    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
    user651341 Member Posts: 48
    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
    ATD Member Posts: 11,184
    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
    M Tajuddin Member Posts: 505 Bronze Badge
    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
    user651341 Member Posts: 48
    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
    user651341 Member Posts: 48
    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
    ATD Member Posts: 11,184
    Thank you for providing this, I'm sure others will find it useful

    Regards

    Andy
This discussion has been closed.