Single Article

Paste Excel data to database

Category APEX Sample Applications

Sometimes it is very handy to have a solution to paste or drag&drop Excel data into a database. The solution we are offering here utilizes Oracle Application Express and Oracle database. Excel data can be pasted via a web browser into the database and then published on the web page.

Our Paste Excel Data application makes it possible to build Ad-hoc web reports based on the data stored in MS Excel, and then make additional processing for example to remove duplicates or to harmonize data. This is the common task required by major corporations nowadays. Instead of using Excel worksheets, one can utilize this approach to achieve the goal.

Also, this solution helps in unifying usage of data and publishing it for anyone concerned, also reducing extra work with endless Excel sheets by a single user.

This is a sample application and requires further customization according to your reporting needs.

Minimum Application Express version is 3.2.

Oracle XE can be used as a free option for the database.

Download application for APEX 3.2

You can check the working sample on

apex.oracle.com

Social Buttons

Comments

  • João 30 Jun 2016  

    hello in my apex i can't see the processes, you can give me the process?

        
  • Jari Laine 30 May 2015  

    Hi Ima,

    You have downloaded version for APEX 3.2

    Please download and try latest demo version witch is for APEX 4.1

    Regards,
    Jari

        
  • Ima 29 May 2015  

    Hi Jari,

    Could you please provide the latest application code for Paste Excel?

    Your demo currently works fine but the application provided for downloading is not working for me. I uploaded to apex.oracle.com (I think it's is version 5 now)

    What is version of the application that is currently for demo at https://apex.oracle.com/pls/apex/f?p=26034:1?

    Clicking on the Submit button in my instance of your app does not do anything. To log in it at https://apex.oracle.com/pls/apex/f?p=27591:1 as demo/demo

    Thanks & regards

    Ima

        
  • Susan 20 Mar 2012  
    Found it. It is linked via the application process (shared component) snippet from javascript on the page header "APPLICATION_PROCESS=UPLOAD_EXCEL" There is an application process under the shared components of the application called UPLOAD_EXCEL which call the "paste_excel_util" package via an anonymous pl/sql block.     
  • Susan 20 Mar 2012  
    Where can I find in the Application Builder the correlation between the clobUpload function & the paste_excel_util package?     
  • Jari Laine 21 May 2011  

    Hi nadrog,

    It really depend how you like do it.
    Sample does insert data to collection and you need customize it depending requirements.

    I do not provide solution how insert data to directly table.

    Regards,
    Jari

        
  • nadrog 21 May 2011  
    Hi Jari, thanks for reply, but I have questions how can I change code: "Change code so that it insert/update data to table. Or you can modify code so that you insert/update data from  collection to table" -- is there any examples? or any references for that insert? Where I can put insert? I was trying from command line typing SELECT COLLECTION_NAME, SEQ_ID, C001, C002, C003, C004, C005, C006, C007, C008, C009, C010, C011, C012, C013, C014, C015, C016, C017, C018, C019, C020, C021, C022, C023, C024, C025, C026, C027, C028, C029, C030, C031, C032, C033, C034, C035, C036, C037, C038, C039, C040, C041, C042, C043, C044, C045, C046, C047, C048, C049, C050, CLOB001, MD5_ORIGINAL FROM apex_collections WHERE collection_name = '&EXCEL_COLLECTION.' AND seq_id        > (SELECT   CASE     WHEN :P1_HEADERS = 'Y'     THEN 1     ELSE 0   END AS start_row FROM dual ) but theres no data. Should I change package PASTE_EXCEL_UTIL in body theres PROCEDURE parse and upload, or application process or add new application process and new button INSERT INTO TABLE_A?     
  • Jari Laine 21 May 2011  

    Hi,

    You do not need compile anything to APEX_040000 schema.

    Package need to be in application parsing schema.
    You need have same application process.
    Place JavaScript to page HTML header as in sample.
    Change jQuery selector accordingly in JavaScript.
    Create needed objects to page.

    As sample application works perfectly, when you install it you have miss create something to your app.
    I can not help on this without seeing actual problem.

    If you like insert data to table, you can insert it e.g.  On Demand process at last step.
    Or you can modify package to insert data directly to table instead of collection.

    Regards,
    Jari

        
  • ChristineH. 20 May 2011  
    Hi Jari, I'm running APEX v4.0. I imported your application and it works as expected however, when I tried to integrate the functionality into the application I'm building, it doesn't work. I've looked at the substitution string document you referred to and the substitution string settings in your demo app. I've also looked at the apex_collection api documentation but I don't quite understand how to use the substitutions and collections just from the documentation. (I need an Aha! moment). I've set my substitution variables to be the same values as yours: substitution string = EXCEL_COLLECTION and substitution value = EXCEL_DATA. So I think that this line in the page 0 application process UPLOAD_EXCEL: paste_excel_util.upload('&EXCEL_COLLECTION.',null); should be taking the data that one copies into the submit window and should create an apex_collection with it, but at what point does that collection get the name EXCEL_DATA? I think that may be where my application is missing something. The package paste_excel_util was compiled into my application parsing schema should it have been placed under the apex_040000 schema instead? (could that be the problem?). Also, while I'm on the subject. In the post above on 2/20/11 where you suggest a way to get data from a collection into a table did you mean to add that insert example to the paste_excel_util package? Ultimately that is what I'm trying to do, take an excel file from a user, upload it into a collection and then update another table with the values provided in the spreadsheet.     
  • Jari Laine 20 May 2011  

    Hi,

    Yes, you need modify code.

    Change code so that it insert/update data to table.
    Or you can modify code so that you insert/update data from collection to table.

    Regrads,
    Jari

        

Global Right Column

Search

Archives

Subscribe in a Reader