Importing a CSV Text File into Oracle 10G XE - Providing details of the file
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
