Import CSV file to oracle DB

Is there a simple Java library or approach that will take a SQL query and load 开发者_Python百科data in a CSV file to oracle database. Pls help

You don't have to use Java to load a data file into a table unless it is absolutely necessary. Instead, I'd recommend Oracle's command-line SQL*Loader utility which was designed specially for this purpose.

For similar tasks I usually use Groovy scripts as it's really easy and quick to write and runs on the JVM off course.

...an example:

import groovy.sql.Sql

def file1 = new File(/C:\Documents and Settings\USER\Desktop\Book1.csv/)
def reader = new FileReader(file1)

def sql = Sql.newInstance("jdbc:oracle:thin:@XXXXXX:XXXX:XXX", "SCHEMA",
      "USER", "oracle.jdbc.driver.OracleDriver")

reader.each { line ->
   fields =  line.split(';')
   sql.executeInsert("insert into YOUR_TABLE values(${fields[0]},${fields[1]},${fields[2]})")
}

It's a basic example, if you have double quotes and semi columns in your csv you will probably want to use something like OpenCSV to handle that.

You could transform each line in the CSV with regular expressions, to make an insert query, and then send to Oracle (with JDBC).

I think this tool will help you for any type of database import-export problem.

http://www.dmc-fr.com/home_en.php

Do you have that CSV in a file on the database server or can you store it there? Then you may try to have Oracle open it by declaring a DIRECTORY object for the path the file is in and then create an EXTERNAL TABLE which you can query in SQL afterwards. Oracle does the parsing of the file for you.

If you are open to Python you can do bulk load using SQL*Loader

loadConf=('sqlldr userid=%s DATA=%s control=%s LOG=%s.log BAD=%s.bad DISCARD=%s.dsc' % (userid,datafile, ctlfile,fn,fn,fn)).split(' ')

p = Popen(loadConf, stdin=PIPE, stdout=PIPE, stderr=PIPE, shell=False, env=os.environ)

output, err = p.communicate()

It's will be much faster that row insert.

I uploaded basic working example here.