[ACCEPTED]-Update Oracle table with values from CSV file-import

Accepted answer
Score: 11

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

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

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

Score: 5

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

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

Score: 1

Another option is to read in the file line-by-line, parse 6 out the fields in the line using something 5 like REGEXP_REPLACE, and update the appropriate 4 rows. You can parse a comma-delimited string 3 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 2 site doesn't allow the use of external tables.

Share 1 and enjoy.

Score: 1

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 10 /path/to/mydata.csv is the path to the CSV file that you need 9 to load, on Windows something like C:\data\mydata.csv. The 8 tablename is mydatatable. The columns are listed 7 in order that they apear in the csv file 6 on the last line.

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

More Related questions