Often we need to load text files, such as comma delimited files, into Oracle databases as tables.
The following are five ways to do it, i.e., external table, SQL loader, SQL insert, Oracle SQL Developer Import Data function and Oracle Apex Load Data. Actually, the real options are only the first three, external table, SQL loader, and SQL insert. SQL Developer and Apex use of the three options to import files. Of course, there are more than five ways to do it. For example, we can also use third party tools such ETL utilities, Microsoft Access, R etc. to load data into the database through ODBC connections.
We have described how to define external table in the database that points to text files in post Analyze Text Files in Real Time Using SQL Without Loading Them into Database. Once a external table is defined, we can simply define a permanent in database table using CTAS "create table as select". For example, we create a database table for the external TBL_DATA1_EXT as the following.
SQL> create table tbl_data1_real as select * from TBL_DATA1_EXT;
The above SQL statement creates a table of the same format as the external table and physically load the data into it. This is my favorite way of loading data. Using this approach, I am able to perform some analyze on the external tables and make sure them look right before I load them.
We follow the three steps to use SQL loader to import data: 1.create a table; 2. Write a control file; and 3. run sqlldr to load the file.
Step 1. Create table using SQL.
create table tbl_data1_real
Step 2. Compile a control file like the following.
into table TBL_DATA1_REAL
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
( ADS_ID ,
Step 3. Load the file into the table.
$ sqlldr user/password@localhost:1521/xe CONTROL=ads_log2.ctl skip=1
SQL*Loader: Release 220.127.116.11.0 - Production on Tue Nov 12 07:04:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Please see my post A Quick Way to Import Spreadsheet Into a Relational Database. We simply generate SQL insert statements for our data and run them. It is a great way o quickly import text files of small size.
Oracle SQL Developer Import Data
Oracle SQL Developer is a free development tool. We first create the destination table using SQL statement mentioned in the above section SQL Loader. Within SQL Developer, we select the table can right click to select Import Data.
There are three options to load the data, Insert Scripts, Staging External Table, and SQL Loader Utility as shown below. We can pick the one we like.
Oracle Apex Text File Import
We described five ways of importing text files into Oracle database tables. My favorite way is Oracle external table because I can run SQL queries against the files to validate them before they are physically imported. Of course, there are more than five ways to do it. For example, we can also use third party tools such ETL utilities, Microsoft Access, R etc. to load data into the database through ODBC connections.