Home arrow Oracle arrow Page 4 - Importing a CSV Text File into Oracle 10G XE

Providing details of the file - Oracle

If you need to deal with legacy data by moving it to more recent databases, this article might help. It will show you how to move data from an MS Excel file into an Oracle 10G XE table.

TABLE OF CONTENTS:
  1. Importing a CSV Text File into Oracle 10G XE
  2. Exporting the Employees table from MS Access
  3. Importing the EmployeesCsv.csv file into Oracle 10G XE
  4. Providing details of the file
By: Jayaram Krishnaswamy
Rating: starstarstarstarstar / 31
September 26, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The task gets changed as in the next window since you need to provide the details of the file as well as the table properties with a Primary key. By clicking on the Globalization arrow you will see the formatting for the locale. You may also browse to save the file in your My Documents folder. It has already come up with comma as the field separator. You need to choose a character set from a list of options as shown.

You have chosen the character set, now use the browse button and browse to the EmployeesCsv.CSV  file on the My Documents folder; click on the Next button. 

This populates the Set Table Properties window as shown. The first item to be chosen is the name of the table. The chosen name for this table is EmployeesCSV. The next four windows show the very large window cut into four pieces and shown in a sequence to cover all the columns (Oracle 10G XE graphics are not easily resized). The EmployeeID has come up with Number data type; the names and titles are with data type Varchar2 with a default size of 30; for the two dates, a format of mm/dd/yyyy has been added; and the 'Photo' column was chosen not to be up loaded.

The size of the text columns were not altered in anyway. After making these changes, the Next button is clicked. This brings up the window where you need to choose the Primary key and generate a sequence. Sequence is approximately the item that corresponds to the auto increment column. The Primary Key was chosen to be an existing column, namely the EmployeeID as shown. Now you are ready to load. Click on the Load Data button.

This brings up the next window listing all the activities with the most recent at the top of the list. The table name showing whether the load succeeded or not is shown in the last two columns of this table. As it stands it shows that the last two loads succeeded and the previous two did not. For working with the Oracle 10G XE GUI, please refer to related articles on the DevShed.com site.

Now go back to the object browser and browse for the newly created table EmployeesCSV in the list of tables as shown.

The EmployeesCSV table is located in the Tables listing. It shows the design view of the table. Since no changes were made to the data type they all remained in the "as imported" state.

The data from this import is shown in the next picture. As you can see all the items of the data type "text" have double quotes.

This next picture is from a previous import which was made into an existing table with a design to mimic the original table using the same CSV file.

The table design for the NorthEmployees table above was made using the Oracle 10G XE table designer.

It is important  to have the text qualifier choice in MS Access as double quotes, otherwise in the final load, the columns get scrambled, as shown from a load that did not choose that option.

Summary

The load success depends on the correct format of the CSV file. An MS Excel file saved to CSV format successfully produces a file that is easy to load. After bringing in the CSV data changes can be made to the table properties such as changing the data type, format, yes/no load choices and so on, which makes it very flexible. For smaller files the copy and paste option is available. Populating an existing table with CSV file data appears to be easier.



 
 
>>> 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: