Forum Stats

  • 3,851,682 Users
  • 2,264,012 Discussions
  • 7,904,817 Comments

Discussions

Categories

Skip rows in Data Loading Page Definition possible?

Jon Finke
Jon Finke Member Posts: 65 Bronze Badge
in APEX Discussions

I am using APEX 21.1 on an 19C database. I am writing an application that needs to load and parse Excel files from a vendor. I would rather not have my users doing the column matching (as you could with Legacy data loading). The Data Loading Capability looks just about perfect for my application, except the vendor spreadsheets don't start with the data/headers, but rather a couple of lines down. I can't seem to find any way to tell data loading wizard to skip two rows before processing headers and data. Is there a way to do so?

Using the APEX_DATA_PARSER I am able to set the P_SKIP_ROWS parameter in the PARSE function, and that works, but leaves me to recreate the huge amount of function in the built in data loading support.

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee
    Answer ✓

    Hi Jon,

    ah - that's right. At design time there is no attribute to specify the amount of rows to skip. But the column mapping is only done once; so for the sample file you're uploading, you can also manually remove the rows in question, then create the data load definition, add the "Rows to Skip" attribute and save. Then you should be good, right?

    But this is a good heads-up: We might have the Skip Rows attribute in the wizard ...

    best regards

    -Carsten

    · Share on TwitterShare on Facebook

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee

    Hi Jon,

    there is a setting for this when using the new Data Load Definitions in APEX 21.1.

    • After creating the data load, navigate to Shared Components, Data Load Definitions
    • Edit your very Data Load Definition
    • Click the button to edit the Data Profile, which contains all information about the format, columns or data types
    • Scroll down towards the bottom and you'll see an attribute named Skip Rows.

    Does that help?

    regards

    -Carsten

    · Share on TwitterShare on Facebook
  • Jon Finke
    Jon Finke Member Posts: 65 Bronze Badge
    edited Jul 29, 2021 3:57PM

    Alas, it appears too late - I need to skip the rows when defining the columns to match. It looks like while I can skip the rows, I have to go in and manually define each of the columns.

    But perhaps I can edit the original spreadsheet to make the data definition...

    · Share on TwitterShare on Facebook
  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee
    Answer ✓

    Hi Jon,

    ah - that's right. At design time there is no attribute to specify the amount of rows to skip. But the column mapping is only done once; so for the sample file you're uploading, you can also manually remove the rows in question, then create the data load definition, add the "Rows to Skip" attribute and save. Then you should be good, right?

    But this is a good heads-up: We might have the Skip Rows attribute in the wizard ...

    best regards

    -Carsten

    · Share on TwitterShare on Facebook
  • Jon Finke
    Jon Finke Member Posts: 65 Bronze Badge

    Carsten,

    If I edit the excel file ahead of time, delete the header rows, use that to create the data load template, I can then use that to load the spreadsheet. That will work for my application.

    But it would be really nice to have the SKIP_ROWS available as an option the wizard when defining the load sources.

    · Share on TwitterShare on Facebook