Populate oracle table with values in CSV using Java

[csv java]

Hi friends,
I want to populate an oracle table with values from a CSV file using
java could some body briefly explain me the procedure please.

Thanks,
Samanth.

 

[Oliver Wong]

Hi friends,
I want to populate an oracle table with values from a CSV file using
java could some body briefly explain me the procedure please.

Start with http://java.sun.com/docs/books/tutorial/essential/io/

- Oliver

 

[steve]

Hi friends,
I want to populate an oracle table with values from a CSV file using
java could some body briefly explain me the procedure please.

Thanks,
Samanth.

check and open an jdbc oracle connection , you need the oracle libraries from
their web site.

turn OFF auto commit.

open a file stream to get you CSV values in.

read a line in from your file.
split the line down at the commas, and stick into an array. (1 line at a
time)

use an oracle prepared statement , and bind variables.
bind each variable in a single row of your array to the oracle statement.

execute the statement.

go back to your file for more values.
then repeat the procedure.

finally when your file is finished AND there have been no errors issue an
oracle commit.

if there were any errors, issue an oracle rollback.


close your oracle connection.
close your file
exit.


OR if you are using oracle 9i/10 g, and you have access to the database you
can link the flat CSV file DIRECTLY to a table.

by using a dummy table. so that the database accesses the flat file , but
gives the appearance it is a table.


Steve

 

[Furious George]

check and open an jdbc oracle connection , you need the oracle libraries from
their web site.

turn OFF auto commit.

open a file stream to get you CSV values in.

read a line in from your file.
split the line down at the commas, and stick into an array. (1 line at a
time)

use an oracle prepared statement , and bind variables.
bind each variable in a single row of your array to the oracle statement.

execute the statement.

go back to your file for more values.
then repeat the procedure.

this is OK, but there is probably a much better way.

finally when your file is finished AND there have been no errors issue an
oracle commit.

if there were any errors, issue an oracle rollback.


close your oracle connection.
close your file
exit.


OR if you are using oracle 9i/10 g, and you have access to the database you
can link the flat CSV file DIRECTLY to a table.

by using a dummy table. so that the database accesses the flat file , but
gives the appearance it is a table.

This sounds much better. The ORACLE product probably has an "import"
or "load data" or "link data" feature like the above described.
Assuming it does, why not just use it. Then the java coding would be
simple .. something like
statement . executeUpdate ( "LOAD DATA 'mycsvfile.csv' INTO TABLE
myoracletable FIELDS SEPARATED BY ',' LINES SEPARATED BY '\n' ;" ) ;
//PSEUCODE...I don't know the actual Oracle syntax
The OP should consult his/her Oracle documentation.

 

[steve]

this is OK, but there is probably a much better way.


This sounds much better. The ORACLE product probably has an "import"
or "load data" or "link data" feature like the above described.
Assuming it does, why not just use it. Then the java coding would be
simple .. something like
statement . executeUpdate ( "LOAD DATA 'mycsvfile.csv' INTO TABLE
myoracletable FIELDS SEPARATED BY ',' LINES SEPARATED BY '\n' ;" ) ;
//PSEUCODE...I don't know the actual Oracle syntax
The OP should consult his/her Oracle documentation.

He said he wanted to use java, he may also want to range check.
and just for the record.
loading directly into the server , can only be done from the server, whereas
my method works from a client.

 

[Greg R. Broderick]

This sounds much better. The ORACLE product probably has an "import"
or "load data" or "link data" feature like the above described.
Assuming it does, why not just use it. Then the java coding would be
simple .. something like
statement . executeUpdate ( "LOAD DATA 'mycsvfile.csv' INTO TABLE
myoracletable FIELDS SEPARATED BY ',' LINES SEPARATED BY '\n' ;" ) ;
//PSEUCODE...I don't know the actual Oracle syntax
The OP should consult his/her Oracle documentation.

Better?

Doing the data load into a table using Java/JDBC has at least a chance that
the code will work with not only Oracle, but also with other database
products.

Doing the data load into a table using Java/JDBC is significantly more effort
- this effort is justified if the application is going to need to load
similar data into the table on an ongoing basis.

Doing the data load into a table using Oracle-specific functionality abandons
all hope for cross-platform compatibility.

Doing the data load into the table using Oracle-specific functionality
requires significantly less up-front effort, is probably better if the user
only needs to load the data into the table once or a few times.

So which is better? Depends on your perspective.


Cheers!

--
---------------------------------------------------------------------
Greg R. Broderick (e-mail address removed)

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

 

[Furious George]

Better?

Doing the data load into a table using Java/JDBC has at least a chance that
the code will work with not only Oracle, but also with other database
products.

Doing the data load into a table using Java/JDBC is significantly more effort
- this effort is justified if the application is going to need to load
similar data into the table on an ongoing basis.

Doing the data load into a table using Oracle-specific functionality abandons
all hope for cross-platform compatibility.

Doing the data load into the table using Oracle-specific functionality
requires significantly less up-front effort, is probably better if the user
only needs to load the data into the table once or a few times.

So which is better? Depends on your perspective.

Very true. In general, I prefer to avoid doing programming work if
someone else has already done it. Why reinvent the wheel? OTH - like
you said - this approach abandons hope for cross-platform
compatibility.

I just hope the OP takes the Java/JDBC approach and packages his/her
effort as an open-source file importing program. Then the next time, I
need to load data from a file, I'll just say why reinvent the wheel or
lose cross-platform compatibility...I'll just use the OP's program.