Hi. I have a request to import excel files data into the database, using APEX. Today, the user opens the excel file (locally on his machine), and there is a macro connecting to the database and loading the spreadsheet data when the user pushes a button. The database connection will not be available anymore (there will be no way to connect directly to the database), so the excel macro won't work anymore. I wonder if there is a way to create a form / report in APEX, witch lists the xls/xlsx files in a local directory and then, after selecting one of them, loads its data into database tables. Something like we could do in Oracle Forms OLE2 (Webutil). We can do it using forms, but I would like to use APEX for new developments.
Any ideas? All I have found so far needs csv, or loads the entire worksheet (not all cells need to be loaded).
Thanks in advance
Why not create APEX application to maintain data directly in database?
Most of solutions load data from CSV or Excel place data to APEX_COLLECTION where you can insert needed columns/rows to table.
Here is one plugin that handles native Excel
Also if you use APEX Listener, it has build in feature load native Excel to APEX_COLLECTION.
Hi. That was exactly my first sugestion too, but the problem is: data on spreadsheets are often created on non web/network environments, so there would be no way to access apex pages. These are pre-formated worksheets with orders info, sent by email to the system user, who checks and loads the data into oracle tables (orders).
At this point in time, there is NOT a viable solution for what you want, since it would involve allowing the user to select the columns they want to upload BEFORE uploading to the database.. Excel allows you the access to the data, thus it allows you to determine the rows/columns to process BEFORE sending to the database..
Unless somehow you build a routine to get the structure of the worksheet ahead of time, allow the user to select the desired columns and then import the worksheet and cut it up to the required format behind the scenes..
I still have a test setup on apex.oracle.com that uses the Excel2Collection (v0.804)
normal user: demo/demo
developer account: developer/trymeout
Just for giggles & grins...
I tried your application with the plugin to load a collection with data from an Excel 2007 file and found that a worksheet with 16 columns, the plugin only allowed/processed the first 10 columns into the collection....Last 6 columns were dropped/ignored...
So I think this MIGHT not be a good option for all cases..
Well, for me the Excel2Collection plugin seems to work, but the user must choose the files one by one. I wonder if there is a way to load all files on a local directory. If there is, my problem is solved. Any clues? It seems the plugin only works with the File Browse component...
Couldn't you use an external table setup to load the data files? If you have a set of files to upload, and the names do not change and the structures don't change.. Then you could run a pl/sql process to load those files into your tables..
I've just ran through an exercise doing exactly what you are wanting to do.
However, since it was a one-off scenario, I just bulk loaded the files into a table via External Tables. (its a DBA thing...)
I've seen a plugin or two that allows the end user to add multiple files at once through APEX
One idea I have been playing with: Oracle DBFS
Specifically, Oracle 12c. In 12c, you can 'export' the table as a WebDAV or FTP service from the database.
FTP is a little funky.. but, my MFPs are now capable of scanning documents into the database
I've only been able to get WebDAV to work with Windows XP. (I haven't tried doing it over a secure connection with Win7+)
Basically: when an end-user drags-and-drops files onto the WebDAV file share, the files magically appear in a database table.
Modifying the Excel2Collection to pull a blob from a table other than WWV_FLOW_FILES was easy (for me, at least).
Looping? sorry.. I used a 'brute force method' where I had to click a button for each file (with a Display Only showing the 'next file to process' )