Home arrow Oracle arrow Page 3 - Loading Spread Sheet Data into Oracle 10G XE

Loading the Spread Sheet into Oracle XE - Oracle

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.

TABLE OF CONTENTS:
  1. Loading Spread Sheet Data into Oracle 10G XE
  2. Loading the XLS file into Oracle
  3. Loading the Spread Sheet into Oracle XE
  4. Continuing to Load
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 29
July 26, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jayaram Krishnaswamy
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: