Populate oracle table with values in CSV using Java  
Author Message
csv java





PostPosted: 2006-10-13 23:19:00 Top

java-programmer, Populate oracle table with values in CSV using 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.

 
steve





PostPosted: 2006-10-14 6:14:00 Top

java-programmer >> Populate oracle table with values in CSV using Java On Fri, 13 Oct 2006 23:18:50 +0800, csv java wrote
(in article <email***@***.com>):

> 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





PostPosted: 2006-10-14 8:17:00 Top

java-programmer >> Populate oracle table with values in CSV using Java
steve wrote:
> On Fri, 13 Oct 2006 23:18:50 +0800, csv java wrote
> (in article <email***@***.com>):
>
> > 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.

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

 
 
steve





PostPosted: 2006-10-15 6:13:00 Top

java-programmer >> Populate oracle table with values in CSV using Java On Sat, 14 Oct 2006 08:16:47 +0800, Furious George wrote
(in article <email***@***.com>):

>
> steve wrote:
>> On Fri, 13 Oct 2006 23:18:50 +0800, csv java wrote
>> (in article <email***@***.com>):
>>
>>> 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.
>
> 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
>

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.