Loading the Spread Sheet into Oracle XE - Oracle

The loaded data goes into a table and several things have to be declared. Loading takes place after you make the choices for the following four items, to which you will be guided step by step.

• Target and method:
• Data:
• Table Properties:
• PrimaryKey

Target and method

While loading you have the option to load into an existing table or create a new table. Also you can copy and paste (up to 30 KB) or upload a csv or tab separated file. Here a new table option with the copy/paste method was chosen, as shown in the next picture.

Data

When you click on the Next button in the screen shown above, you will be guided to the following window. Here you can paste the data from an Excel file in the text area box. While cutting and pasting there is a size limit of 30 KB. If this is exceeded you can use the file upload option in the previous screen.

This is the copied area of the Excel spread sheet that is loaded. This screen does not show all the columns.

When pasted it effectively becomes a tab delimited text file, as shown in the next picture.

Table Properties

When you click on the next button in the above screen, you will get to the stage where you need to make choices on the table, as shown in the next picture. You need to give a name to the table (with no white spaces). You can see all the defaults for the data brought in. Except for the Employee_ID, all the rest appears to be of type varchar2. Just accept the defaults and click on the next button. Here you may make changes as required: you may choose not to include certain columns in the loading, change the data size, and so forth. For the present, the defaults were accepted. You may now click on the Next button, which takes you to the next screen, where you make your Primary key choice.

Primary Key

Here you will have to make choices regarding the Primary Key. Basically you will need to choose whether you want to use the key from an existing column or create a new one. Here the one that is existing is used (EmployeeID). This attaches a constraint as shown. You also make your choice for Primary Key population by choosing the required sequence generation.

When you click on the Load Data button after making the choices, you will see the next screen which shows the results of loading. TestNW1 came out with 9 successes and 0 failures. Since there were only 9 rows, all of them came through.

Now if you click on the table hyperlink for TestNW1, you will see the schema of the table as shown in the next picture. You notice that the Birth Date and Hire Date columns came out as of data type varchar2, whereas in the original table they are of type date.