CODEWITHSUNDEEP
javaoracle-database
nath
nath

Reputation: 2848

Import CSV file to oracle DB

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

Upvotes: 3

Views: 9017

Answers (6)

Alex B
Alex B

Reputation: 2395

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.

Upvotes: 0

elwood
elwood

Reputation: 29

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.

Upvotes: 0

dogbane
dogbane

Reputation: 274878

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.

Upvotes: 5

gautamlakum
gautamlakum

Reputation: 12035

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

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

Upvotes: 0

HeDinges
HeDinges

Reputation: 4607

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.

Upvotes: 3

greuze
greuze

Reputation: 4398

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

Upvotes: 0

Related Questions