Loading Data

Loading a Text File or Spreadsheet Data

You can upload a Text file or copy and paste tab-delimited data directly into the Load Data Wizard.

To load data from a text file:

1. On the Workspace home page, click the SQL Workshop icon.

2. Click Utilities.

3. Click Data Workshop.

4. Under Data Load, click Text Data.

   The Load Data Wizard appears.

5. Under Load To, select either Existing table or New table.

6. Under Load From, select either Upload file or Copy and paste.

   • Upload file - Select to specify an existing text file to upload.

   • Copy and paste - Select to create a text file to upload by performing a copy and paste.

7. Click Next.

8. If you selected Upload file, under Load Data, make the following changes:

   • Text File - Browse for or enter the name of the Text File to load.

   • Separator - Enter the column separator character. See item Help for more details.

   • Optionally Enclosed By - If your data contains a delimiter character, enter it here. See item Help for more details.

   • First row contains column names - Check this box if the first row of your data contains column names. See item Help for more details.

   • File Character Set - Select the character set in which your text file is encoded. See item Help for more details.

9. If you selected Copy and paste, under Data make these changes:

   • Data - Enter the text file to upload.

   • First row contains column names - Check this box if the first row of your data contains column names.

10. Under Globalization, make the following changes:

    • Currency Symbol - If your data contains an international currency symbol, enter it here. See item Help for more details.

    • Group Separator - If your data contains a character that separates integer groups, for example to show thousands and millions, enter it here. See item Help for more details.

    • Decimal Character - If your data contains a character that separates the integer and decimal parts of a number, enter it here. See item Help for more details.

11. Click Next.

    The table Properties page appears. Before performing the data upload, this page allows you to optionally customize the data.

12. For Table Properties, make the following changes and click Next:

    • Schema - Select the schema containing the table to load.

    • Table Name - Enter the table to load.

    • Column Names - Enter the names of the columns.

    • Data Type - Select the data type for this column.

    • Column Length - Enter the length of the data in this column.

    • Upload - Select Yes to upload data for this column. Select No to not upload data for this column and skip to the next column.

13. For Primary Key, make the following changes:

    • Primary Key From - Define the primary key column by choosing either an existing column or creating a new column. If you choose an existing column you must select the column from the columns being loaded. If you choose to create a new column you must specify the name of the new column.

    • PK Constraint Name - The constraint name will default to the table name appended with _PK. You can update the name if desired.

    • Primary Key Population - You need to decide how your primary key is populated. You either generate it from a new sequence, from a new sequence or not at all. If you choose a new sequence, you can use the sequence name given or update it as necessary. The sequence will be created for you. If you choose an existing sequence, you must select the sequence from those currently defined in your schema. If you generate the primary key from a sequence, either new or existing, the wizard creates a trigger on the table to retrieve the next sequence value and populate the primary key when a record is inserted.

14. Click Load Data.

Loading an XML Document

Oracle Application Express supports XML documents in Oracle's canonical XML format.

In Oracle's canonical XML format, each element represents a column value, each element is named after the column, all elements that are part of the same row are children of a <ROW> element, and all <ROW> elements are children of a <ROWSET> element.

To load an XML document:

1. On the Workspace home page, click the SQL Workshop icon.

2. Click Utilities.

3. Click Data Workshop.

4. Click Load XML Data.

   The Load XML Data Wizard appears.

5. Select the appropriate schema for your data and click Next.

6. Select the table to load the data into and click Next.

7. Follow the on-screen instructions.