Update Oracle table with values from CSV file

Question

I have a CSV file which contains an ID and several other columns. I also have a table in oracle where the ID identifies a row. How can I best replace the values that are in the table with the values in the CSV file while keeping the other columns the way they were before?

This has to be done with tools available in oracle itself (i.e. PL/SQL or SQL scripts), I can not use a "real" scripting language (Python, ...) or a "real" program.

Thanks, Thomas

Answer 1

Look at EXTERNAL TABLES in the Oracle doc. You can copy the csv file onto the Oracle server box and get Oracle to present it as a "normal" table on which you can run queries.

Then the problem just becomes one of copying data from one table to another.

External tables are really very useful for handling data loads like this.

Answer 2

The "standard" Oracle tool for loading CSV-type datafiles into the database is SQLLoader. It's normally used to insert records, but the control file can be configured to run an update instead, if that's what you so desire.

It's not the easiest tool in the world to use (read "it's a pain in the arse"), but it's part of the standard toolkit, and it does the job.

Answer 3

Another option is to read in the file line-by-line, parse out the fields in the line using something like REGEXP_REPLACE, and update the appropriate rows. You can parse a comma-delimited string like the following:

SELECT TRIM(REGEXP_REPLACE(strLine, '(.*),.*,.*', '\1')),
       TRIM(REGEXP_REPLACE(strLine, '.*,(.*),.*', '\1')),
       TRIM(REGEXP_REPLACE(strLine, '.*,.*,(.*)', '\1'))
  INTO strID, strField1, strField2      FROM DUAL;

This is useful if your site doesn't allow the use of external tables.

Share and enjoy.

Answer 4

Use SQL*Loader

sqlldr username@server/password control=load_csv.ctl

The load_csv.ctl file

load data
 infile '/path/to/mydata.csv'
 into table mydatatable
 fields terminated by "," optionally enclosed by '"'          
 ( empno, empname, sal, deptno )

where /path/to/mydata.csv is the path to the CSV file that you need to load, on Windows something like C:\data\mydata.csv. The tablename is mydatatable. The columns are listed in order that they apear in the csv file on the last line.

Unless you have a very large volume of data this is the easiest to maintain way of getting the data in. If the data needs to be loaded on a regular basis this can be worked into a shell script and run by CRON on a U*NX system.