Skip to Main Content
  • Questions

  • Use Data Loading in APEX to upload a specific tab of an Excel sheet

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Dick.

Asked: March 15, 2023 - 8:25 pm UTC

Last updated: March 28, 2023 - 2:22 pm UTC

Version: 21.2

Viewed 10K+ times! This question is

You Asked

We want to use the Data Loading functionality of APEX to upload a specific tab of an Excel sheet.

We only found out it the Data Loading function only imports the first tab of the Excel sheet.
We want to upload the second one of the Excel sheet.
What are we missing here?

and Chris said...

Using the Load Data wizard (SQL Workshop -> Utitlies -> Data Workshop) in Settings there's a dropdown where you can "Select Sheet".

For more on this see:

https://blogs.oracle.com/apex/post/quick-and-easy-data-loading-with-apex-191
https://docs.oracle.com/en/database/oracle/apex/22.2/aeutl/loading-data.html#GUID-5B932032-7641-46EA-ABC4-314D33DECE95

Rating

  (3 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Dick, March 16, 2023 - 2:36 pm UTC

Thanks for your quick answer .... I understand the functionality by choosing the Data Workshop functionality, but I mean using the Data Load functionality by using the App Builder. (NOT by the SQL Workshop).
How can we fix it?

Chris Saxon
March 17, 2023 - 4:41 pm UTC

I'm not sure - we're not APEX experts here. I suggest asking on the APEX forum https://forums.oracle.com/ords/apexds/domain/dev-community/category/apex

Manual import

Damian, March 27, 2023 - 2:39 pm UTC

Hi,
you can create your manual import. Add item with type 'File Browse...' with Storage Type 'Table APEX_APPLICATION_TEMP_FILES' to load Excel to Apex. Then when parsing this Excel use something like 
SELECT *
FROM apex_application_temp_files f
                         ,table(apex_data_parser.parse(p_content           => f.blob_content
                                                      ,p_file_name         => f.filename
                                                      ,p_xlsx_sheet_name   =>'sheet name')) p
                   WHERE f.name = :FILE_NAME;

One of parameter of parse function is sheet name (see: https://docs.oracle.com/en/database/oracle/application-express/19.1/aeapi/PARSE-Function.html#GUID-B815CF74-C469-4F78-9433-643D1339E930 ).
Chris Saxon
March 28, 2023 - 2:22 pm UTC

Thanks for sharing

Dick, April 12, 2023 - 2:38 pm UTC

Thanks for the answers ... it helped me a lot.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.