CODEWITHSUNDEEP
sqloracle-databasecsvloader
Andrei Bordeanu
Andrei Bordeanu

Reputation: 67

How to load CSV data into Oracle table that has more columns than the csv file?

I have a .csv file that contains data for only certain columns in a table. How do I import that .csv into the table leaving the other columns intact (or setting the NULL value)?

Initially the table had the same columns as the .csv file and I was using this SQL loader:

LOAD DATA
INFILE '"Path"/file.csv'
APPEND
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(
    COLUMN1,
    COLUMN2,
    COLUMN3,
    COLUMN4,
    COLUMN5
)

but then I had to add some other columns in table that need to be updated with data from other tables, and I want to update them (using a trigger) when inserting the data from the .csv for the initial columns. So how do I import in table only the columns in .csv?

Upvotes: 2

Views: 10382

Answers (2)

xDBA
xDBA

Reputation: 421

Also you can use awk.

Here is a detailed example on how to compose SQL queries based on values from CSV file.

SQL> create table MY_TABLE(a varchar2(100), b varchar2(100));

[oracle@myora ~]$ tail Some_Input_CSV_file
Some Data A 1,Some Data B 1
Some Data A 2,Some Data B 2
Some Data A 3,Some Data B 3
Some Data A 4,Some Data B 4
Some Data A 5,Some Data B 5
Some Data A 6,Some Data B 6
Some Data A 7,Some Data B 7
Some Data A 8,Some Data B 8
Some Data A 9,Some Data B 9
Some Data A 10,Some Data B 10
[oracle@myora ~]$

[oracle@myora ~]$ cat Some_Input_CSV_file | awk -F, ‘ { printf(“insert into MY_TABLE values(trim(\x27%s\x27), trim(\x27%s\x27));\n”, $1, $2); } ‘ > RunMe.sql

[oracle@myora ~]$ tail RunMe.sql
insert into MY_TABLE values(trim(‘Some Data A 1′), trim(‘Some Data B 1′));
insert into MY_TABLE values(trim(‘Some Data A 2′), trim(‘Some Data B 2′));
insert into MY_TABLE values(trim(‘Some Data A 3′), trim(‘Some Data B 3′));
insert into MY_TABLE values(trim(‘Some Data A 4′), trim(‘Some Data B 4′));
insert into MY_TABLE values(trim(‘Some Data A 5′), trim(‘Some Data B 5′));
insert into MY_TABLE values(trim(‘Some Data A 6′), trim(‘Some Data B 6′));
insert into MY_TABLE values(trim(‘Some Data A 7′), trim(‘Some Data B 7′));
insert into MY_TABLE values(trim(‘Some Data A 8′), trim(‘Some Data B 8′));
insert into MY_TABLE values(trim(‘Some Data A 9′), trim(‘Some Data B 9′));
insert into MY_TABLE values(trim(‘Some Data A 10′), trim(‘Some Data B 10′));
[oracle@myora ~]$

[oracle@myora ~]$ sqlplus myuser/mypwd@myinst

SQL> @RunMe.sql

…

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Upvotes: 0

user330315
user330315

Reputation:

There is nothing you need to do.

Only the column values from the file will be sent.

The other columns will get their default value (typically NULL, but that depends on the column definition) when the rows are inserted.

Not sure what you mean with leaving the other columns intact, as SQL*Loader will only insert new rows, there is nothing to be "left intact".

Upvotes: 3

Related Questions