Easy way to load a CSV file from the command line into a new table of an Oracle database without specifying the column details

user_65889 0
Viewed 1603

I often want to quickly load a CSV into an Oracle database. The CSV (Unicode) is on a machine with an Oracle InstantClient version 19.5, the Oracle database is of version 18c.

I look for a command line tool which uploads the rows without me specifying a column structure.

I know I can use sqlldr with a .ctl file, but then I need to define columns types, etc. I am interested in a tool which figures out the column attributes itself from the data in the CSV (or uses a generic default for all columns).

The CSVs I have to ingest contain always a header row the tool in question could use to determine appropriate columns in the table.

import csv oracle database
2 Answers

Starting with Oracle 12c, you can use sqlldr in express mode, thereby you don't need any control file.

In Oracle Database 12c onwards, SQLLoader has a new feature called express mode that makes loading CSV files faster and easier. With express mode, there is no need to write a control file for most CSV files you load. Instead, you can load the CSV file with just a few parameters on the SQLLoader command line.

An example

Imagine I have a table like this

CREATE TABLE EMP
(EMPNO number(4) not null,
ENAME varchar2(10),
HIREDATE date,
DEPTNO number(2));

Then a csv file that looks like this

7782,Clark,09-Jun-81,10
7839,King,17-Nov-81,12

I can use sqlldr in express mode :

sqlldr userid=xxx table=emp

You can read more about express mode in this white paper

Express Mode in SQLLDR

user_137555380

Forget about using sqlldr in a script file. Your best bet is on using an external table. This is a create table statement with sqlldr commands that will read a file from a directory and store it as a table. Super easy, really convenient.

Here is an example:

create table thisTable (
    "field1" varchar2(10)
    ,"field2" varchar2(100)
    ,"field3" varchar2(100)
    ,"dateField" date
) organization external (
    type oracle_loader
    default directory <createDirectoryWithYourPath>
    access parameters (
        records delimited by newline 
        load when (fieldname != BLANK)
        skip 9
        fields terminated by ',' optionally ENCLOSED BY '"' ltrim
        missing field values are null
        (
            "field1" 
            ,"field2"
            ,"field3"
            ,"dateField" date 'mm/dd/yyyy'
        )
    )
    location ('filename.csv')
);
user_100125190
Related