Loading Spread Sheet Data into Oracle 10G XE

If you have ever loaded data into an Oracle table, you know that it is a rather complicated process. You will be surprised at how much easier it is to do in Oracle 10G XE. Keep reading to find out.


SQL*Loader, Oracle’s utility program, was used to load data into an Oracle table. The program needed two files, a data file and a control file. The control file with the file extension .ctl controlled the file to be saved, errors in loading, and so forth. SQL*Loader also required the SQL*Net program. It also created a log file, giving further information on the loading. All this is simplified in Oracle 10G XE. If you know Cntr+C and Cntr+V, your loading is almost finished.

This tutorial is about loading an Excel spread sheet into an Oracle table. It’s very easy and you will be surprised at its simplicity. The tutorial also shows what can go wrong and how to fix it in a simple case. The tutorial uses a table (Employees) from the Northwind database.

Creating a test spread sheet

A test spread sheet was created by exporting a table from Northwind.mdb to an Excel file. The is easily accomplished from File–>Export which brings up the window Export Table ‘Employees’ to… , at which point you use the SaveAsType drop-down to pick up the Microsoft Excel 97-2003 *xls format. Give it a name and it will be saved to the same default directory, C:MY Documents. The exported table had the design parameters shown in the next picture. The reason for choosing this file was that it had different data types with different formatting options.

{mospagebreak title=Loading the XLS file into Oracle}

Log into HR Database

Log into the Oracle sample database HR using the short-cut hyperlink Go To Database Home Page… from All Programs–>Oracle 10g Database Express Edition. This opens up the login page for the HR database. Assuming you have already permitted the hr user with hr password for the database, you can enter the credentials and get access to the database as shown in the next picture.

Move to Home

After the login is accepted you will find the Home page of the HR database, where you will find a number of icons. These were already explained in my first tutorial on this topic on the DevShed site. Please review the previous tutorials and the two more that followed. Click on the Utilities icon, and from the drop-down click on the Load item.

Change to Data Load/Unload

In this window you can load several different formats of data including Text, Spread Sheet and XML. We will be loading the Employees.XLS we created from the Northwind database, therefore click on Load SpreadSheet Data icon.

{mospagebreak title=Loading the Spread Sheet into Oracle XE}

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.


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.

{mospagebreak title=Continuing to Load}

If you were to start afresh and come to the stage where you can make changes to the data you will see this screen. Here for the two columns with the data type date, you can pull down on the drop-down and change them to type Date.

If you now continue to load you will end up with 9 failures and 0 successes as shown in the next picture. The reason for this is that the date format mm/dd/yy is not acceptable to Oracle 10G XE.



Now if you click on the hyperlink in the Failed column in the next table pertaining to this load, TestNW2, you will see the following screen displayed. This shows why the failure occurred (Ora 01843: not a valid month).


Now if you make the following changes while loading you will see that the loading was a success for this loading, TestNW4.


You may click on the table name TestNW4 and verify that the data type for the two columns with the date data type came out okay.


Loading an MS Excel file into Oracle 10G XE was a very simple exercise. It required no more than cutting and pasting the data. The interface is simple, intuitive and the loading process is well staged. It would have been helpful if some interactive help were available for data formatting. The reporting of loading success and failure is immediately available without opening another file, making the process very simple and effortless. Oracle 10G XE is really a one-stop solution for data management and application development that would appeal to many.

Google+ Comments

Google+ Comments