It is always a hassle and time consuming to create a table in the database and insert the values in it manually. Most of the time we want a predefined data to be used for analyzing the database queries This article describes how to load data with Oracle Database 10g Express Edition. Furthermore, loading data means copying data into the database from external text files. Additionally, the data can be either in standard delimited form or in the format supported by Oracle.
Here is step by step explanation of loading a .csv file ‘online_retail_II.csv’ The source of .csv file is https://archive.ics.uci.edu/ml/machine-learning-databases/00502/
Likewise, for other such sources to download datasets refer to https://dextutor.com/top-10-dataset-repositories/
Steps to load data from an external source
To load the ‘online_retail_II.csv’ file as a table:
1. Log in to the Oracle Database XE graphical user interface as any user other than SYSTEM or SYS. In case, if no database user other than SYSTEM or SYS exists, create one.
2. On the Database Home Page, click Utilities icon.
3. On Utilities icon select Data Load/Unload option and click on Load
4. After clicking on the Load option, you will be redirected to another page. Select the appropriate icon based on the type of file that you want to load. In this article, we are loading a .csv file, so select the Load Spreadsheet Data icon.
5. The Load Data page appears, showing the Target and Method wizard step. Under Load To heading, select New Table and under Load From select Upload file(comma-separated or tab-delimited). Click Next.
The File Details wizard step appears. After that, click Browse, select the ‘online_retail_II.csv’ file. Click Open. In the Separator field, by default it is a comma(,). Keep it comma(,) for .csv file. However, if you are loading a .txt file, then change it to ‘\t’ to indicate field separator. In the File Character Set list, select Unicode UTF-8. Click Next
7. The Table Properties wizard step appears. In the Table Name field enter the name of the table. Here it is “Online_Retail”. Accept the default ‘Yes’ in all Upload list. Setting Upload to ‘No’ excludes the column from the load operation. Click Next.
The Primary Key wizard step appears. After that, next to Primary Key From the label, either you can choose to Use an existing column or Create a new column. Here we are using an existing column Customer_ID as a Primary Key. Next to the Primary Key Population label, select Generated from a new sequence. Create a new sequence. Click Load Data.
9. The table is created in the database with the name “Online_Retail”. To check the table, on Database Home Page, click on the Object Browser icon. Select Browse and in Browse select Tables option.
10. Yes! you can see your table and information related to the table.
