[ACCEPTED]-Update Oracle table with values from CSV file-import
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.
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.
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.
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