13 Replies Latest reply: Jan 9, 2014 10:32 AM by CInglez RSS

    Batch Import of Excel Files

    CInglez

      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

        • 1. Re: Batch Import of Excel Files
          jariola

          Hi,

           

          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

          http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/excel2collections_271.html

           

          Also if you use APEX Listener, it has build in feature load native Excel to APEX_COLLECTION.

           

          Regards,
          Jari

          • 2. Re: Batch Import of Excel Files
            CInglez

            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).

            • 3. Re: Batch Import of Excel Files
              jariola

              Hi,

               

              OK, have you check that EXCEL2COLLECTIONS process plugin?

               

              Regards,

              Jari

              • 4. Re: Batch Import of Excel Files
                CInglez

                I will try using it. I could not find a good example. Thanks

                • 5. Re: Batch Import of Excel Files
                  TexasApexDeveloper

                  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..

                  Thank you,

                   

                  Tony Miller

                  LuvMuffin Software

                  • 6. Re: Batch Import of Excel Files
                    Mike Kutz

                    I still have a test setup on apex.oracle.com that uses the Excel2Collection (v0.804)

                    https://apex.oracle.com/pls/apex/f?p=80185

                    normal user:  demo/demo

                     

                    developer info

                    workspace:  mikekutz_test

                    developer account:  developer/trymeout

                    • 7. Re: Batch Import of Excel Files
                      TexasApexDeveloper

                      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..

                       

                      Thank you,

                       

                      Tony Miller

                      LuvMuffin Software

                      • 8. Re: Batch Import of Excel Files
                        Mike Kutz

                        wrong 'bug'.

                         

                        The Interactive Report is selecting only the first 10 columns

                        C001 through C010

                         

                        go modify the IR and see what happens.

                         

                        MK

                        • 9. Re: Batch Import of Excel Files
                          CInglez

                          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...

                           

                          Thanks

                          • 10. Re: Batch Import of Excel Files
                            TexasApexDeveloper

                            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..

                             

                            Thank you,

                             

                            Tony Miller

                            LuvMuffin Software

                            • 11. Re: Batch Import of Excel Files
                              TexasApexDeveloper

                              Yes, my bad.. I was not looking at the report close enough...

                               

                              Thank you,

                               

                              Tony Miller

                              LuvMuffin Software

                              • 12. Re: Batch Import of Excel Files
                                CInglez

                                Yes, but how do I reference it to the plugin, since it seems to work only with the File Browse component? Sorry if I didn't get it.

                                • 13. Re: Batch Import of Excel Files
                                  Mike Kutz

                                  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' )

                                   

                                  MK