APEX Cloud 20.1 - How to create application to allow users to upload Excel file xslx to a table

Joe1

I've read in Cloud 20.1 documentation how to upload xlsx file into table by going to Workspace homepage, SQL workshop, Utilities,Data Workshop, Load Data.  That works fine, but I think this method is for developers or admins only.  I need to create an application that will let a user, not a developer, load their spreadsheet into an existing table, replacing the previous uploaded data.  In order to maintain data integrity, having the user save as csv and then upload csv is not an option; it must be the native spreadsheet.  When I tried creating a Data Loading page within an application, the wizard only shows support for csv or delimited files, not xlsx.

I was hoping Cloud 20.1 would allow direct xlsx upload by a user, but I cannot find that option.  Can anyone give me the options for doing this within 20.1.

jariola

I would add display only item Px_FILE_NAME for show file name.

Create display only item and add After Submit Computation

• Type: SQL Query (return single row)
• SQL Query:

  select f.filename from apex_application_temp_files f where 1 = 1 and f.name = :Px_FILE

Set Server-side Condition to this computation

• Type: Item is NOT NULL
• Item: Px_FILE

Second problem comes because you submit page again, and you don't select file.

This case file browser item session state is set to null.

I would add hidden item Px_NAME that we can use for insert process fetch correct file from apex_application_temp_files.

Create hidden item and add After Submit Computation

• Type: Item
• Item Name: Px_FILE

Set Server-side Condition to this computation

• Type: Item is NOT NULL
• Item: Px_FILE

In above examples Px_FILE is your file browser item.

Now use Px_NAME in your insert process instead of Px_FILE, that is file browser item.

I hope this works

jariola

4100203 wrote:I've read in Cloud 20.1 documentation how to upload xlsx file into table by going to Workspace homepage, SQL workshop, Utilities,Data Workshop, Load Data. That works fine, but I think this method is for developers or admins only. I need to create an application that will let a user, not a developer, load their spreadsheet into an existing table, replacing the previous uploaded data. In order to maintain data integrity, having the user save as csv and then upload csv is not an option; it must be the native spreadsheet. When I tried creating a Data Loading page within an application, the wizard only shows support for csv or delimited files, not xlsx.I was hoping Cloud 20.1 would allow direct xlsx upload by a user, but I cannot find that option. Can anyone give me the options for doing this within 20.1.

Check sample application File Upload and Download.

This might also help

https://blogs.oracle.com/apex/super-easy-csv-xlsx-json-or-xml-parsing-about-the-apex_data_parser-package

Joe1

Thank you jariola.  That example in the blog was very helpful.  I followed the steps in the blog, and was able to get it working but I had a couple problems I can't seem to get quite right.  Hoping you can help me get these two issues resolved.

1) When the file is chosen and the parse button is clicked, the parser results and column information regions populate and/or refresh, but the file browser selection does not show the selected file any longer, so the user does not have any idea what file is being parsed.  How to keep that file box filled in, or is it better to just add a text item to show the filename?  If the data is being displayed, the filename it came from should be shown as well.

2) I want the user to select the parse button first so they can see the results of their file, then press another button like LOAD to do the actual insert.  I added the LOAD button to submit page, then added the PL/SQL code for the insert (same code as at the end of the blog) into an After Submit process.  But it seems this only works if I press the LOAD button first, and not the PARSE button first.  If I hit the PARSE button first, it seems the LOAD does not enter into the PL/SQL loop, so not data inserted.  The PARSE button works repeatedly.  I know it has to do with the data not being available after the PARSE button is pressed, but I cannot figure out why, especially since the PARSE button continues to work repeatedly.

jariola

I would add display only item Px_FILE_NAME for show file name.

Create display only item and add After Submit Computation

• Type: SQL Query (return single row)
• SQL Query:

  select f.filename from apex_application_temp_files f where 1 = 1 and f.name = :Px_FILE

Set Server-side Condition to this computation

• Type: Item is NOT NULL
• Item: Px_FILE

Second problem comes because you submit page again, and you don't select file.

This case file browser item session state is set to null.

I would add hidden item Px_NAME that we can use for insert process fetch correct file from apex_application_temp_files.

Create hidden item and add After Submit Computation

• Type: Item
• Item Name: Px_FILE

Set Server-side Condition to this computation

• Type: Item is NOT NULL
• Item: Px_FILE

In above examples Px_FILE is your file browser item.

Now use Px_NAME in your insert process instead of Px_FILE, that is file browser item.

I hope this works

Joe1

Thank you Jariola!  Your assistance was spot on.  My application is up and running now.