How to Import a CSV in Oracle

Galaxy Glossary

How do I import a CSV into Oracle quickly?

Importing a CSV in Oracle quickly loads external data into permanent relational tables.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why import a CSV into Oracle?

CSV imports let you bulk-load storefront data—customers, orders, or products—without manually typing rows. This speeds up migrations, backfills, and nightly feeds.

What tools can I use to load CSV?

Oracle offers three main options: External Tables, SQL*Loader (sqlldr), and SQL Developer’s GUI. External Tables are easiest for repeatable loads; SQL*Loader remains fastest for very large files; SQL Developer works well for ad-hoc uploads.

How do External Tables work?

External Tables read the file directly from disk without staging.You create a table that points to the CSV, then query or INSERT … SELECT into a normal table.

Step-by-step External Table load

1 Create a DIRECTORY object that maps to the server folder.
2 Grant READ on the directory to your schema.
3 Create the External Table with Oracle Loader access parameters.
4 INSERT … SELECT into the real table.

How to load with SQL*Loader?

SQL*Loader uses a control file to map CSV fields to table columns. Run sqlldr from the command line.It generates a log, bad, and discard file so you can audit rejects.

Which parameters matter most?

FIELDS TERMINATED BY sets the comma; OPTIONALLY ENCLOSED BY handles quotes; DATE "YYYY-MM-DD" converts date strings; SKIP identifies header rows. Always match data types exactly to avoid conversion errors.

How do I verify the load?

Query the target table and compare counts against sqlldr log rows or the External Table’s COUNT(*).Spot-check a few records to confirm data integrity.

Best practices for CSV imports

• Validate UTF-8 encoding before loading.
• Stage files in a secure DIRECTORY with minimal privileges.
• Use External Tables for repeat jobs; switch to PARALLEL for speed.
• Keep control files in version control.

What’s a real-world ecommerce example?

Load customers.csv into Customers, then relate it to Orders. After import, a simple JOIN query powers a customer lifetime value dashboard.

.

Why How to Import a CSV in Oracle is important

How to Import a CSV in Oracle Example Usage


-- Import OrderItems from a CSV file using SQL*Loader
-- order_items.ctl (control file)
LOAD DATA
INFILE 'order_items.csv'
INTO TABLE OrderItems
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id INTEGER, order_id INTEGER, product_id INTEGER, quantity INTEGER )

-- Shell command
sqlldr userid=ecommerce_app/secret control=order_items.ctl log=order_items.log

How to Import a CSV in Oracle Syntax


-- 1. Create a DIRECTORY that Oracle can read
CREATE OR REPLACE DIRECTORY csv_dir AS '/u01/data/csv';
GRANT READ ON DIRECTORY csv_dir TO ecommerce_app;

-- 2. External Table pointing to customers.csv
CREATE TABLE customers_csv
  ( id           NUMBER,
    name         VARCHAR2(100),
    email        VARCHAR2(150),
    created_at   DATE )
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY csv_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    SKIP 1                                 -- skip header row
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ( id           INTEGER EXTERNAL,
      name         CHAR(100),
      email        CHAR(150),
      created_at   DATE "YYYY-MM-DD" ) )
  LOCATION ('customers.csv') );

-- 3. Move data into permanent table
INSERT /*+ APPEND */ INTO Customers (id, name, email, created_at)
SELECT id, name, email, created_at FROM customers_csv;

Common Mistakes

Skipping DIRECTORY privileges: Failing to GRANT READ on the DIRECTORY causes ORA-29913 errors. Always grant READ (and optionally WRITE) before creating the External Table.
Mismatching data types: Declaring a VARCHAR2 column for numeric CSV data leads to TO_NUMBER conversion errors downstream. Define columns with the exact target types in both control and external tables.

Frequently Asked Questions (FAQs)

Can I load multiple CSV files at once?

Yes. List multiple filenames inside the LOCATION clause or use wildcards with the PREPROCESSOR directive.

How do I handle embedded commas?

Wrap the field in double quotes and keep OPTIONALLY ENCLOSED BY '"' in your ACCESS PARAMETERS or control file. Oracle will treat the entire quoted string as one column.

Is SQL Developer slower than SQL*Loader?

SQL Developer uses INSERT statements under the hood, so very large files load slower. SQL*Loader and External Tables bypass the SQL layer, offering higher throughput.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.