Avatar of NiceMan331
NiceMan331
 asked on

append dbf files to oracle table

i have to every month append data for my existing table from dbase server ( dbf format )
here i'm using to import it first to ms excell , then from access to oracle
any procedure to use the dbf file name as a parameter , search for it in a specific folder , then upload it to my table
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
NiceMan331

8/22/2022 - Mon
Sean Stuber

If everything is in windows you could try using odbc drivers for dbase and then opening the dbf file as external tables with the heterogeneous gateway.


https://www.experts-exchange.com/articles/9850/Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html
David VanZandt

Also, you imply you're concerned with a single table. I'd skip the Access step and use SQLLoader to read the Excel CSV file.
NiceMan331

ASKER
sdstubar
it is too dificult for me
dvz
use SQLLoader to read the Excel CSV file.
what is the steps
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sean Stuber

>>>> it is too dificult for me

which step did you get stuck on?
NiceMan331

ASKER
Sorry for my bad expression , but I just read the article , I found that some database admin tasks such as database link and drivers which I don't have privilege to create it in addition to adding values to the registery where our erp is strict for a specific values in
Geert G

technically you need to change the point where the data is inserted
don't insert it in the dbase, insert it in the oracle directly

if you describe the dbase tables and the oracle destination tables
it should be easy enough to come up with an etl program
do run the conversion

you'll need to identify what records need to be uploaded
> probably easiest in the oracle
then load that data from dbase
insert it in oracle
mark finished

dbase is almost ancient technology ... it might be more future proof to get rid of it
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
NiceMan331

ASKER
dbase is almost ancient technology
yes i know , but it is the policy of the company , i have nothing to do with

don't insert it in the dbase, insert it in the oracle directly
i'm not inserting into dbase , it is job of other department they do as per the instruction of the management
you'll need to identify what records need to be uploaded
actually the programm every month it created a file named by month name
for example sep-15
and here i want to upload all its records to my oracle table

then load that data from dbase
insert it in oracle
mark finished
this is my question , how ?
Geert G

i was thinking of building you an app ...
the data is in the dbase and you want it copied (or moved ?) to the oracle

i'll conjure up something and come back with it
Geert G

i'd appreciate a dbf file with a few records in it

the descr of the oracle table
i suppose you'll map the column names to the same column names ?

what i was thinking off ...
on oracle:
select max(id) last_id from ora_table
> this gives me the last record inserted in the oracle

select * from dbase_table
where id > :last_id

insert into ora_table (id, col1, col2, col3)
values (:id, :col1, :col2, :col3);

technically you only need these 3 queries per table do the copy from dbase 2 oracle
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
NiceMan331

ASKER
actually i don't require the first 2 queries , because , as i explained before , the dbase file created every month with new name "month name"
so , i not care about the first & last id , i want to insert the whole records from this file
named "sep-15"
next month i will copied all records from "oct-15" to my table , and so on
and still now i don't know how to do the last query
NiceMan331

ASKER
i already created the table in oracle , and append the table of ms access into it
now i can append from toad from dbase into oracle , but still one issue
my table in oracle has a field "period = date format " to have the value of date for this transactions
how i can insert this value together while appending the next file
if there is no solution to make the append by script ,
ASKER CERTIFIED SOLUTION
Geert G

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
NiceMan331

ASKER
hi
i'm so sorry for the delay
but the reason i waited to the new dbase file been created
then , the file you mensioned above is not working ( not able to install)
when i click it to install it shutted down
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Geert G

install ?
it's an exe ... you just run it on windows
it doesn't require an installation

you need to provide a params.txt with options as specified below:

params.txt
dbase_alias=db1
query_dbase.sql=dbase.sql
oracle_alias=sample_tns_alias
oracle_username=username
oracle_password=password
query_oracle.sql=oracle.sql
rowspercommit=10000
NiceMan331

ASKER
Yes , when I'm clicking it it just open dos Windows then close immediately , I'm using Windows 7
NiceMan331

ASKER
But any how I don't need third party , I need a code to do it within oracle , because it should search for my parameter ( file name ) then append it in addition to extra variables
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

>>Yes , when I'm clicking it it just open dos Windows then close immediately , I'm using Windows 7

Open a CMD prompt and run it from there.  It should be able to provide more information about why it isn't running.

>>But any how I don't need third party , I need a code to do it within oracle

Oracle doesn't know how to read a DBase file natively.  You will need to write the code yourself, buy it, or find ways to do it on the web.

Geert Gruwez was nice enough to write some Delphi code for you.  I would probably continue with him.
Geert G

the program requires the BDE to be installed
and for you to have setup an alias to dbase and an alias to oracle
provide the parameters for those items in the params.txt

then run the program in cmd
you don't need anything else, it copies the data from dbase to oracle

since you are using software which is 2 decades old ... the BDE ... i assumed you know how to use DOS
NiceMan331

ASKER
Sorry again , what is : BDE ? And how to create the aliases you refer to ? Then what should I do with Delphi code ? Do I need to create text file name it : param ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

>>Sorry again , what is : BDE ?

I must admit, I had no idea.  So I Googled:  Delphi BDE

Found this:
https://en.wikipedia.org/wiki/Borland_Database_Engine

>>And how to create the aliases you refer to ?

Those are the parameters:  dbase_alias and oracle_alias

>>Do I need to create text file name it : param ?

I believe the direction was:
you need to provide a params.txt with options as specified below:

Name the file params.txt and provide it as a command line argument.

Look at the raw source code provided:  usage: Dbase2Oracle params.txt

I would also expect it to display that message if you ran the .exe from a CMD prompt.

But those are all just guesses since I don't know Delphi...
NiceMan331

ASKER
sorry , i didnt know how to use that program also
i found in Toad can use what i need
thanx to all
Geert G

I gave all the possible help I could imagine
Toad ... for dbase ????
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
NiceMan331

ASKER
yes geert i'm really thanking you
but i didn't understand how to do with your program
Geert G

you working with ancient technology ... dbase
it's not from the time the dinosaurs walked on this planet, but a little later
it's from the time a 56k modem was used to logon to internet providers

it's really time to invest in upgrading that ancient technology

what i see is that you are giving very limited information
what version is the dbase database ?
NiceMan331

ASKER
Geert , I'm not the owner of the business , I'm just an employee , and I'm trying to immigrate the data at least to oracle because I'm not the design maker to decide to upgrade the system
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Geert G

i guessed as much

what happens when you indicate to the business it's getting impossible to get it right ?
reason > software technology which doesn't meet current business requirements anymore

this should be an indication to the business that this item requires a lot more attention than they expected.

you can keep wading through the mud
... or replace the mud with a solid floor
NiceMan331

ASKER
anyhow , this is the case now and it is an issue for me
because i'm concerning to analyse the data , i used to immagrate it to oracle for analyses purposes