Javascript required
Skip to content Skip to sidebar Skip to footer
Home / Upload Csv File in Oracle Apex Package

Upload Csv File in Oracle Apex Package

Noon application pages provide a mechanism for application users to load data from CSV files into awarding tables via Information Loading.

In prior versions of APEX (pre-21.one) the Information Load definition would be specified during the Data Loading 'create page magician'. The wizard would contain the post-obit steps:

Ascertain the Data Load:

  • The table to exist loaded into
  • Specify unique columns
  • Define whatsoever transformation rules
  • Define any table lookups
Create 4 pages to be used by the application end users in the Data Load wizard:
  • Data Load Source
  • Data / Table Mapping
  • Data Validation
  • Data Load Results

Book a meeting with an Oracle APEX expert

Once the 'create folio wizard' was completed, the Data Load definition created could be viewed and amended in 'Shared Components' under 'Data Sources'.

The process has moderately inverse with version 21.1.

Now the Data Load needs to be defined in 'Shared Components' prior to the creation of whatever associated pages. The idea backside this is to simplify the Data Load process for the end user where the aforementioned loads are repeated. Instead of the data source, mapping and validation being specified by the user in the wizard pages, they can now be defined by the developer for reuse past the user.

Information Load Definition

Creating a Data Load definition is now a pre-requisite of building the Data Load pages and is undertaken by selecting 'Data Load Definitions' under Data Sources in Shared Components and clicking the 'Create' push.

The wizard to create a Information Load definition is broadly similar (with knobs on) to a combination of the steps taken in previous versions by the developer to create the definition and the user to perform a Data Load. At present, information technology uses sample data to aid build the definition. It includes options to:

  • Copy an existing Data Load definition (or create one from scratch)
  • Load into a tabular array or a drove (previously this was table only)

image002

  • Create the Data Load definition based on sample data

image004

  • Create the Information Load definition based on files in csv, xlsx, xml or json format (previously just comma separated or tab delimited files were allowed)

And now the post-obit steps are applied during the definition phase past the programmer rather than at run time by the end user:

  1. Select to upload a file or copy and paste data (now sample data to build the definition)
  2. Specify whether the first line contains column headers
  3. Specify the File Encoding
  4. Perform the Column Mapping, which itself allows the programmer to:
    • Map the information source cavalcade to the table/collection cavalcade
    • Specify a format mask for each cavalcade (at that place is likewise a preview page which displays how the sample data is interpreted, which is quite useful)
    • Grouping separator and decimal characters tin now be specified at the column level
    • Place columns as principal cardinal columns

A preview of the data tin can be viewed via a second tab to assist with the mapping.

image005

The sorcerer provides the developer with the choice of continuing to create an associated Data Loading page or saving the Data Load definition at this stage (the Data Loading page can and then be created afterward using the Create Page wizard).

Once the Create Data Load wizard is completed the definition can be viewed and edited.

At this point information technology is possible to configure or ameliorate the:

  • Loading Method – Append, Merge or Replace
  • Fault Handling – Ignore, Finish, Log into Collections, Log into Mistake Log

Information technology'due south useful to annotation that assistance is available on both these parameters to understand the unlike settings.

image008

  • Data Profile where it is possible to:
    • Re-sequence columns
    • Specify an XLSX worksheet name
    • Skip rows in the source file before data parsing starts
    • Add together new columns (based on Information, Lookup, SQL Expression, SQL Query)
    • Meliorate column mapping attributes, including whether column mapping choice is based on Name, Sequence or Regular Expression
    • Configure Transformation Rules

image009

Data Loading Page

Once the Data Load definition has been completed, the Information Loading page tin can exist created. Equally previously mentioned, this tin can be completed as part of an extended Data Load Definition wizard or separately in the 'Create Page' wizard. Either way, the steps are the aforementioned, and simplified compared to the Data Loading create folio magician of previous versions as the Information Load has already been defined.

  • Specify the folio number, name mode
  • Specify navigation: breadcrumb, navigation menu entry
  • Select the data load definition that the page will utilise (i.eastward. the one created in the steps above)

image012

  • Determine whether the information will be uploaded from a file or from pasting delimited information (just available for definitions based on CSV)

Now the Data Load and page are ready to be run.

The application page is at present much simpler. The end user only has to select the data e.g. choose a file with the File Browse folio item or paste the delimited data.

image013

Upon selecting the file (later clicking the 'Next' push if pasted delimited data is used), the data is shown in preview. It's worth noting that the information hasn't withal been loaded into a collection.

image015

So, click the 'Load Data' push button and the Data Load is executed.

In prior version, the Data Load wizard made apply of a number of Apex collections to manage the data load, such equally:

  • LOAD_COL_HEAD
  • LOAD_CONTENT
  • NOT_LOAD_CONTENT
  • PARSE_COL_HEAD
  • SPREADSHEET_CONTENT

In version 21.1, these are dispensed with. The data, either from file or pasted data, is presented in 'Preview' on the application folio past making employ of the APEX_DATA_PARSER package.

I have previously written a weblog on the APEX_DATA_PARSER package which tin can be establish here.

APEX_DATA_LOADING Package

The new APEX_DATA_LOADING parcel provides the ability to load data past calling an awarding Information Loading definition. This can be used in place of native data loading.

The APEX_DATA_LOADING.LOAD_DATA part performs the load and there are ii broadly similar signatures, one for loading Hulk data eastward.one thousand. from a file, another for loading CLOB data due east.one thousand. from copied data pasted into a text area.

The LOAD_DATA function returns load status information containing processed rows and fault rows in the 't_data_load_result' type.

So, I created a page to select a file and load it equally blob into APEX_APPLICATION_TEMP_FILES (the blob could equally have been from another tabular array or created from a file in a directory).

Then, as office of a post-submit process, I executed the APEX_DATA_LOADING.LOAD_DATA office specifying the name of the Data Load definition (p_static _id) and the hulk (p_data_to_load) to be loaded.

image018

The Excel file contents were successfully loaded into the collection, which I was then able to report on.

image019

Conclusion

APEX 21.i changed the manner Application Data Loads are created so that a greater emphasis of the definition sits with the developer.

This simplifies the end-user process, removing the requirement for users to perform tasks that may exist repeated from 1 Information Load to the side by side for the same definition e.g. file definition and data mapping.

In addition, with the availability of the APEX_DATA_LOADING API, the Data Load definition tin can be executed in stored procedures, which could help bypass user interaction and simplify code compared to using the APEX_DATA_PARSER API alone.

The change represents quite an overhaul of the application Data Load process, and then information technology volition exist worth checking when future versions of APEX are released, whether Data Loads' built-in versions prior to 21.ane go deprecated or are removed.

DSP-Explorer Oracle Noon Services

If this blog was of interest, be sure to discover more almost our Oracle Apex Services, we'd be happy to talk over the art-of-the-possible with y'all!

Book a meeting with an Oracle APEX expert

Author : Philip Ratcliffe

Job Title : Oracle Apex Development Consultant

Bio : Philip is a Development Consultant at DSP-Explorer. Building on considerable feel in evolution including using Oracle PL/SQL and supporting Oracle EBS, Philip is employing APEX to provide quality, bespoke software solutions to a range of organisations.

glencrosshintud.blogspot.com

Source: https://content.dsp.co.uk/apex/enhancing-oracle-enterprise-manager-with-oracle-apex-0