Update Oracle Table with Values from CSV File

Update Oracle table with values from CSV file

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.

Update database using variable from csv file in cx_Oracle

• ORA-01036 raises due to the wrong type of placeholders for oracle, rather prefer using integers prepended with colons such as
  :1 , :2

• Don't forget to trim the second placeholder's value in order to get
  rid of whitespaces wrapping around the value for y

• No need to embed the parameters within the DML, rather convert them
  to a tuple such as (301547, y)

  ...
  with open(r'D:\db_results.csv') as file:
      next(file)
      for line in file:
          x = line.replace('\n', ' ').replace('\r', '')
          columns = x.split(",")
          y = columns[1]
          SQL = "UPDATE inventory SET model=:1 WHERE order_id = TRIM(:2)"
          cur.execute(SQL, (301657, y))
      con.commit()
  con.close()
  

How to update big table from csv file/another table in Oracle

I would look at loading your data into a global temporary table, then using a MERGE statement to join between that and the target table on a common and unique key value, and thus apply the changes.

There are plenty of MERGE examples on SO, and in the documentation.

https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

How to read column from csv and run a update query?

You could use a MERGE statement:

MERGE INTO TABLE1 t1
  USING (SELECT *
           FROM TABLE3) t3
    ON (t1.ID = t3.ID AND
        t3.CREATED_TIME BETWEEN TO_TIMESTAMP('01-APR-2020', 'DD-MON-YYYY')
                            AND TO_TIMESTAMP('02-MAY-2020', 'DD-MON-YYYY') - INTERVAL '0.000000001' SECOND)
  WHEN MATCHED THEN
    UPDATE SET t1.RESULT_CODE = 62;

Bulk Update in Oracle from CSV file

There are 2 basic techniques to do this:

1. sqlldr
2. Use an external table.

Both methods are explained here:
Update a column in table using SQL*Loader?

Is there a way to import a CSV to Oracle DB with automatic table creation? Using commando line

Give csv2db a try.

The generate option will create the table for you.

# https://github.com/csv2db/csv2db

# drop user
echo 'drop user csvdata cascade;' | sqlplus -S system/oracle@localhost:1521/XEPDB1

# create user and grant privileges
sqlplus -S system/oracle@localhost:1521/XEPDB1 <<EOF
create user csvdata identified by load default tablespace USERS temporary tablespace TEMP quota unlimited on USERS;
grant create session, resource to csvdata;
EOF

# generate DDL
time csv2db generate --file=movies.csv --table=MOVIES | sed 's/1000/4000/' \
| sqlplus -S csvdata/load@localhost:1521/XEPDB1

# load data
time csv2db load --user=csvdata --password=load --host=localhost --port=1521 --dbname=XEPDB1 \
       --separator=',' --table=MOVIES --directpath \
       --file=movies.csv

# what do we have?
echo 'select count(*) from movies;' | sqlplus -S csvdata/load@localhost:1521/XEPDB1

Best of luck!

---

Related Topics