CODEWITHSUNDEEP
oracle-databasecsvsql-loader
Eugene Zhulenev
Eugene Zhulenev

Reputation: 9734

SQL*Loader - load CSV with multiple chunks

I've got a CSV file that contains multiple data chunkds:

Alice
Age,Weight,Height
20y,50kg,170cm
Bob
Age,Weight,Height
22y,80kg,180cm

I need to load it into table PEOPLE in such way:

Name   |  Age   |  Weight   |  Height 
Alice     20y       50kg        170cm
Bob       22y       80kg        180cm

Is it possible to do it with SQL*Loader? Other way is to write a csv pre-processor on groovy for example, but it would be much better to solve it simply with SQl*Loader

Upvotes: 1

Views: 509

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

An alternative is to just load it into an intermediate table (or load it as an external table) with generic columns C1, C2, C3; then it'd be simple to write the SQL to process it.

Upvotes: 0

Zsolt Botykai
Zsolt Botykai

Reputation: 51603

Assuming you are on linux and or have access to awk:

awk -v Q="'" -F "," \
    'BEGIN {
         print "create table YOURTABLE (name varchar2(X), age varchar2(Y), weight varchar2(Z), height varchar2(N));"
     }
     /^[^,]\+$/ {name=$0}
     /^Age,Weight,Height$/ {
         getline 
         print "insert into YOURTABLE values (" Q name Q ", " Q $1 Q ", " Q $2 Q ", " Q $3 Q ") ;"
         print "commit;"
     }
     END { print "exit;" }' INPUT.CSV > LOAD.SQL

So it prints a create table ... statement in which you should replace your field lengths and tabblename. Then reads your CSV and on every line if the line does not contain any , it sets the name variable, then on every Age,Weight,Height line reads the next one and prints an insert statement with the contents (and a commit;). At the end it prints an exit;.

At voila, you can load it with sqlplus.

Or rewrite it a bit to create a standard csv, it's easy.

Upvotes: 2

Related Questions