Geek Answers Handbook

Refresh Oracle table with values ​​from CSV file

I have a CSV file containing an ID and several other columns. I also have a table in oracle where the identifier identifies the row. What is the best way to replace the values ​​that are in the table with the values ​​in the CSV file, preserving the other columns as they were before?

This needs to be done using tools available in oracle (for example, PL / SQL or SQL scripts), I cannot use the “real” scripting language (Python, ...) or the “real” program.

Thank you Thomas

+7
sql import oracle plsql csv
source share
5 answers

Look at EXTERNAL TABLES in an Oracle document. You can copy the csv file into the Oracle server field and force Oracle to present it as a “normal” table where you can run queries.

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

External tables are really very useful for processing data such as.

+10
source share

Oracle "standard" tool for loading CSV-type data files into the SQLLoader database. It is usually used to insert records, but the control file can be configured to start the update instead, if that is what you so desire.

This is not the easiest tool in the world that you can use (read "it's a pain in the ass"), but it is part of the standard tools and does the job.

+5
source share

Another option is to read the file line by line, analyze the fields in the line using something like REGEXP_REPLACE, and update the corresponding lines. You can parse comma delimited strings as shown below:

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 does not allow the use of external tables.

Share and enjoy.

+1
source share

Use SQL * Loader

 sqlldr username@server/password control=load_csv.ctl

File load_csv.ctl

 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 you need to download, on Windows something like C: \ data \ mydata.csv . Table_name is mydatatable . The columns are listed so that they appear in the csv file on the last line.

If you have a very large amount of data, this is the easiest way to save data in. If data needs to be loaded on a regular basis, it can be processed in a shell script and run CRON on a U * NX system.

+1
source share

First create a table and put all the CSV data in this table, and then write the cursor to update the oracle table using the CSV table corresponding to the identifiers

ex

 create table t

and then import the CSV data into this table.

now write the cursor

 declare cursor c1 is select * from t1; begin update Oracle table set t1.Column=t2.column where t1.id=t2.id;

I think it will work

-7
source share

More articles:


All Articles