Update Oracle Table With Values From Csv File

February 23, 2024 Post a Comment

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 tab

Solution 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.

Solution 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.

Solution 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:

SELECTTRIM(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.

Solution 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.

Solution 5:

First create a table and put all your CSV data into that table,and then write a cursor to update your oracle table with the CSV created table corresponding to IDs

createtable t

and then import your CSV data into this table.

now write a cursor

declarecursor c1 isselect*from t1;
beginupdate Oracle tableset
t1.Column=t2.column
where t1.id=t2.id;

i think it will work

Lab Sql