3

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.

halloleo
  • 9,216
  • 13
  • 64
  • 122

2 Answers2

4

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

Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
  • Interesting! I didn't know this. Unfortunately this method does not create the table for me. So I still have to fiddle with column specs... – halloleo Aug 26 '20 at 13:32
  • @halloleo, that is for sure. There is no way to create the table automatically by just reading plain text. Oracle can't figure out automatically which data types you want for your columns. However, it is quite easy to write a bash function to read the header of the csv and create automatically the columns using a varchar2 generic data type – Roberto Hernandez Aug 26 '20 at 13:36
0

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')
);
alexherm
  • 1,362
  • 2
  • 18
  • 31
  • Mmmmmh, certainly an out-of-box solution. Doesn't look much easier to write this SQL script than writing a sqlldr control file. Plus it doesn't seem to auto detect the fields/columns at all. – halloleo Sep 01 '20 at 02:40
  • 1
    The beauty of the external table is that the file can be updated in the filesystem and it is immediately read into the table the next time anything selects from it. Yes, the building of the ```create table``` is more or less equal to building a control file, but it doesn't require any sort of trigger to refresh. The field listing is only there to define date format. If you don't need dates, or want to go rogue and import them as strings instead, you should not require the lower field definition. And yet, I agree this still does not quite fulfill what you're asking for. – alexherm Sep 01 '20 at 22:30
  • Maybe consider issuing templates to users. On the other hand, you might be able to figure out a way to build a dynamic solution based on what I sampled. – alexherm Sep 01 '20 at 22:36
  • Super intersting that the CSV file is re-read later on again! Definitely _not_ my use case but in other circumstances this can come in handy. Thanks. – halloleo Sep 02 '20 at 08:25