4 Data Loading Wizard Examples

This section explains how to import data into an Oracle Application Express application using an existing application Data Loading Wizard.

Tip:

To use the functionality described in this section, your application must be built with Data Upload capability. To learn more, see "Creating Applications with Data Loading Capability" in Oracle Application Express Application Builder User's Guide.

4.1 About Using the Data Loading Wizard

Applications with data loading capability enable end users to dynamically import data into a table within any schema to which the user has access. End users run a Data Load Wizard that uploads data from a file or copies and pastes data entered by the end user directly into the wizard.

The Data Loading wizard for your application may have been built to include the ability to apply table lookup and transformation rules during the data upload process. Before the data is actually imported into the database, you are given the opportunity to review the data after all look up and transformation rules have been applied.

  • Table Lookups - These rules automatically map data in the import file or copy and paste field to data that is found in another table. For example, if the import file contains a department name for the DEPTNO column but the upload table requires a number for that column, use a table lookup rule to find the corresponding department number for that department name in another table.

  • Data transformation rules - These rules automatically perform formatting transformations such as changing import data to uppercase, lowercase, and so on. For example, if the import file includes column data with both upper and lowercase and the destination table requires all uppercase, data transformation rules can automatically insert only uppercase into that column during data upload.

4.2 Importing Data from a File into Your Application

To upload data to your application, the application must have been built with Data Upload capability and the file must be formatted properly. This section illustrates how to use the Data Load Wizard to import data from a text file and includes examples from the sample packaged application, Sample Database Application.

See Also:

"Utilizing Packaged Applications" and "Understanding the Packaged Application Sample Database Application" in Oracle Application Express Application Builder User's Guide

To upload data from a file:

  1. Run the sample packaged application, Sample Database Application.

  2. On the Customers page, click Upload Data.

    The Data Load wizard appears. Next, select how to upload data.

  3. From Import From, select Upload file, comma separated (*.csv) or tab delimited.

    Description of data_load_source.png follows
    Description of the illustration data_load_source.png

  4. Under Data Load Source:

    1. File Name - Browse to the file name containing the data to upload.

      Tip:

      To create a text file with sample data, expand the Sample Data region and copy and paste the data shown into a text file. If you use this sample data, remember to enter a comma (,) in the Separator attribute.

    2. Separator - Enter the column separator character. Enter a comma (,) for comma separators or \t for tab separators.

    3. Optionally Enclosed By - Enter a delimiter character. Use this character to delineate the starting and ending boundary of a data value. If you specify a delimiter character, Data Workshop ignores whitespace occurring before the starting and ending boundary of a data value. You can also use this option to enclose a data value with the specified delimiter character.

    4. First Row has Column Names - Select this option if f your data contains column names in the first row.

    5. Use Application Date Format - Select this option to use the Application Date and Timestamp formats during data loading. See "Configuring Globalization Attributes" in Oracle Application Express Application Builder User's Guide.

    6. File Character Set - Select the file character set associated with the file to be uploaded.

    7. Globalization - Optionally expand the Globalization region and configure the following attributes:

      • Currency Symbol - If your data contains international currency symbol, enter it here. See field-level Help for examples.

      • Group Separator - Enter a group separator to separate integer groups. Any non-numeric character can the group separator with a few exceptions. To learn more, see field-level Help.

      • Decimal Character - Enter a decimal character to separate the integer and decimal parts of a number. To learn more, see field-level Help.

    8. Click Next.

      The Data/Table Mapping page appears.

      Description of data_table_mapping.png follows
      Description of the illustration data_table_mapping.png

  5. For Data/Table Mapping:

    1. Column Mapping - Indicates the destination column name. To change the column name, select a new column name from the list. To hide a column, select Do Not Load.

    2. Click Next.

    The Data Validation page appears showing the data to be inserted or updated after any table lookup and transformation rules have been applied. For more information regarding table look up and transformation rules, see "About Using the Data Loading Wizard."

    Description of validation.png follows
    Description of the illustration validation.png

  6. Review the data to be uploaded and click Load Data.

    The Data Load Results page displays:

    • Inserted Row(s) - This is the number of new rows uploaded to the table.

    • Updated Row(s) - This is the number of rows in the table updated with new information.

    • Failed Row(s) - This is the number of rows from the upload file that were not added or updated.

    • To be Reviewed Row(s) - This is the number of rows needing review.

  7. Click Finish.

4.3 Copying and Pasting Data into Your Application

To upload data to your application, the application must have been built with Data Upload capability and the pasted data must be formatted properly. This section illustrates how to use the Data Load wizard to copy and paste data from a text file and includes an example from the sample packaged application, Sample Database Application.

See Also:

"Utilizing Packaged Applications" and "Understanding the Packaged Application Sample Database Application" in Oracle Application Express Application Builder User's Guide

To upload data by copying and pasting:

  1. Run the sample packaged application, Sample Database Application.

  2. On the Customers page, click Upload Data.

    The Data Load wizard appears. Next, select how to upload data.

  3. From Import From, select Copy and Paste.

    Description of data_load_paste.png follows
    Description of the illustration data_load_paste.png

  4. Under Data Source Load:

    1. Separator - Enter the column separator character. Enter a comma (,) for comma separators or \t for tab separators.

    2. Optionally Enclosed By - Enter a delimiter character. Use this character to delineate the starting and ending boundary of a data value.

    3. First Row has Column Names - Select this option if f your data contains column names in the first row.

    4. Use Application Date Format - Select this option to use the Application Date and Timestamp formats during data loading. See "Configuring Globalization Attributes" in Oracle Application Express Application Builder User's Guide.

    5. File Character Set - Select the file character set associated with the file to be uploaded.

    6. Copy and Paste Delimited Data - Copy and paste your delimited data.

      Tip:

      To create a text file with sample data, expand the Sample Data region and copy and paste the data that displays. If you use this sample data, remember to enter a comma (,) in the Separator attribute.

      The sample data appears in the Copy and Paste field.

    7. Globalization - Optionally expand the Globalization region and configure the following attributes:

      • Currency Symbol - If your data contains international currency symbol, enter it here. See field-level Help for examples.

      • Group Separator - Enter a group separator to separate integer groups. Any non-numeric character can the group separator with a few exceptions. To learn more, see field-level Help.

      • Decimal Character - Enter a decimal character to separate the integer and decimal parts of a number. To learn more, see field-level Help.

    8. Click Next.

      The Data/Table Mapping page appears.

      Description of data_table_mapping.png follows
      Description of the illustration data_table_mapping.png

  5. For Data/Table Mapping:

    1. Column Mapping - Indicates the destination column name. To change the column name, select a new column name from the list. To hide a column, select Do Not Load.

    2. Click Next.

      The Data Validation page appears showing the data that to be inserted or updated after any table lookup and transformation rules have been applied. For more information regarding table look up and transformation rules, see "About Using the Data Loading Wizard."

      Description of validation.png follows
      Description of the illustration validation.png

  6. For Data Validation, review the data to be uploaded and click Load Data.

    The Data Load Results page shows:

    • Inserted Row(s) - This is the number of new rows uploaded to the table.

    • Updated Row(s) - This is the number of rows in the table updated with new information.

    • Failed Row(s) - This is the number of rows from the upload file that were not added or updated.

    • To be Reviewed Row(s) - This is the number of rows needing review.

  7. Click Finish.