Importing a CSV Text File into Oracle 10G XE

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.

Introduction

In an earlier article, importing data in a spreadsheet into Oracle 10G XE was described. The present article looks at importing a Comma Separated Value (CSV) file, used commonly in exporting MS Excel files, into an Oracle 10G XE table. CSV files are very popular and frequently used data transformation formats since legacy data are usually of this type. In recent times XML formatted data has replaced them. 

However, there is a whole lot of legacy data that needs to be loaded on to more recent databases. Hence, every database vendor provides a program to accomplish this conversion. Also programs exist which takes a CSV file and convert it into an XML file. Perhaps this is another route one can take in data conversions for legacy data.

The CSV file used in this tutorial

In order to load a CSV file to a table in Oracle 10G XE, a CSV file will be created from an Access database table in this tutorial. This will not only simplify the process of understanding the procedure, but also gives us an opportunity to correctly implement the conversion, since the original data, with a good design, is available at the outset. The table we will be using to create a CSV file is the Employees table in the Northwind.mdb, an MS Access database whose design view is as shown.

{mospagebreak title=Exporting the Employees table from MS Access}

Click on the File menu and in the drop-down, click on Export… which opens the window Export Table ‘Employees’ To…. Click on the drop-down for Save As type: Text Files(*.txt;*.CSV;*.tab;*.asc) and then click on the button Export. You will have to type in the extension type from among the four choices shown; the default is *.txt. Again by default the file will be saved to the My Documents folder as shown.

When the button Export All, which gets enabled, is clicked, the Export Text Wizard comes up as shown. It is already showing that a delimited character will separate the fields. Fixed width will be another option for the export process, if that restriction is acceptable. It also shows a sample of how the export looks, showing the columns.

When you click on the Next button in the above screen you will shift to the next screen shown here, which comes up with the default choice selecting comma as the delimiter between the fields. Since column names are important for the table that will be created in Oracle 10G XE, make sure you place a check mark on the check box marked Include Field Names for First Row. Click on the Next button. The choice of what Text Qualifier to use is also important as discussed later.

This brings you to the final screen showing that the EmployeesCsv.CSV file will be saved to the location shown in the Export to File: textbox.

When you click on the Finish button, you get the final screen showing where you can find your file.

The next picture shows a part of the exported EmployeesCsv.csv file opened using Notepad. Notice that all fields are enclosed in double quotes. The very first line has the names of the column. Also note the column values for the ‘Photo’ column are all empty. The picture shows text corresponding to only two rows in the original table. It will be useful to open this file using MS Excel to verify that the table is faithfully reproduced in the CSV file.

{mospagebreak title=Importing the EmployeesCsv.csv file into Oracle 10G XE}

Log into Oracle 10G XE, which brings up the next screen with several icons for the various areas, such as Administration, Object Browser, etc. Click on the Utilities icon and from the drop-down go to the Data Load/Unload drop-down in which you click on the Load menu item as shown.

This brings you to the next screen where you can Load Text Data, Load Spreadsheet Data or Load XML Data.

Click on the icon for loading text data which brings up the next window. The picture has been rearranged in this view. If the file size is less than 30KB, Oracle 10g XE supports cut and paste, not only for CSV files but also spreadsheet files. The other option is to upload a file, either comma delimited or tab delimited as shown. This option is chosen for this tutorial.

In the above screen choose to Upload a file and choose to load into a New table. This completes the target and method task on the left hand side. Click on the Next button.

{mospagebreak title=Providing details of the file}

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.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye