Question: What is best way to import a CSV file into an Oracle table? I have a comma delimited CSV file, and I want to rub SQL queries against the file. What are my options for loading my CSV?
Answer: There are several ways to query a CSV file with SQL, by loading it into an Oracle table:
– SQL*Developer: Use the SQL Developer wizard to load the CSV file:
1. Define the empty table. (create table . . . . )
2. Select the table in the navigation tree.
3. Right click and select ‘import data’.
4. Follow the instructions in the wizard.
– SQL*Loader: Use the sqlldr utility. See my notes on sqlldr. That page has an example of a sqlldr parfile to load a table from a CSV.
– External Table: Define an external table. See my notes here on using an external table and creating an external table. Here is an example control file for defining a CSV as an external table:
create directory testdir as ‘u01/oracle/oradata/testdb’;
create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
Organization external
(type oracle_loader
default directory testdir
access parameters (records delimited by newline
fields terminated by ‘,’)
location (’emp_ext.csv’))
reject limit 1000;