append dbf files to oracle table

NiceMan331
NiceMan331 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.


http://www.experts-exchange.com/articles/9850/Configuring-and-using-Oracle-Database-Gateway-for-ODBC.html
David VanZandtOracle Database Administrator III

Commented:
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.

Author

Commented:
sdstubar
it is too dificult for me
dvz
use SQLLoader to read the Excel CSV file.
what is the steps
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
>>>> it is too dificult for me

which step did you get stuck on?

Author

Commented:
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 GOracle dba
Top Expert 2009

Commented:
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

Author

Commented:
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 GOracle dba
Top Expert 2009

Commented:
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 GOracle dba
Top Expert 2009

Commented:
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

Author

Commented:
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

Author

Commented:
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 ,
Oracle dba
Top Expert 2009
Commented:
took some time ... darn bde wasn't installed on my test pc

use the attached Dbase2Oracle.Exe to upload the data from dbase to oracle
if the oracle.sql file doesn't exist, it will write a csv.txt file with the dbase data

the code in Delphi:
{
usage:
Dbase2Oracle params.txt

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
}
program DBase2Oracle;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DBTables,
  Classes;

const
  MaxParam = 4;
  ParamDefaults: array[1..MaxParam, 1..2] of string =
    (('dbase_alias', 'db_dbase'), ('oracle_alias', 'db_oracle'),
     ('query_dbase.sql', 'query_dbase.sql'), ('query_oracle.sql', 'query_oracle.sql')
     );
  DefaultRowsPerCommit = 10000;

var ParamList: TStrings;
function Param(ParamName: string): string;
var
  paramFileName: string;
  I: Integer;
begin
  Result := '';
  if not assigned(ParamList) then
  begin
    ParamList := TStringList.Create;
    ParamFileName := 'params.txt';
    if ParamStr(1) <> '' then
      ParamFileName := ParamStr(1);
    if FileExists(ParamFileName) then
      ParamList.LoadFromFile(ParamFileName);
  end;
  if Assigned(ParamList) then
  begin
    Result := ParamList.Values[ParamName];
    if Result = '' then
      for I := 1 to MaxParam do
        if SameText(ParamDefaults[I, 1], ParamName) then
          Result := ParamDefaults[I, 2];
  end;
end;

var
  dbd, dbo: TDatabase;
  qryd, qryo: TQuery;
  I, rec: Integer;
  csv,errors: Text;

begin
  try
    dbd := TDatabase.Create(nil);
    try
      dbd.AliasName := param('dbase_alias');
      dbd.DatabaseName := 'dbase_source';
      dbd.LoginPrompt := False;
      dbd.Connected := True;

      if dbd.Connected then
      begin

        dbo := TDatabase.Create(nil);
        try
          dbo.LoginPrompt := False;
          dbo.AliasName := param('oracle_alias');
          dbo.Params.Values['USERNAME'] := param('oracle_username');
          dbo.Params.Values['PASSWORD'] := param('oracle_password');
          dbo.DatabaseName := 'oracle_source';
          dbo.Connected := True;

          if dbo.Connected then
          begin
            qryd := TQuery.Create(nil);
            try
              qryd.DatabaseName := dbd.DatabaseName;;
              qryd.SQL.LoadFromFile(param('query_dbase.sql'));
              qryd.Open;
              if not qryd.IsEmpty then
              begin
                { Writing to CSV }
                if not FileExists(param('query_oracle.sql')) then
                begin
                  Assign(csv, 'csv.txt');
                  Rewrite(csv);
                  try
                    for i := 0 to qryd.Fields.Count-1 do
                    begin
                      Write(csv, QuotedStr(qryd.fields.Fields[I].FieldName));
                      if i < qryd.Fields.Count-1 then
                        Write(csv, ',');
                    end;
                    WriteLn(Csv, '');
                    while not qryd.Eof do
                    begin
                      for i := 0 to qryd.Fields.Count-1 do
                      begin
                        Write(csv, QuotedStr(qryd.fields[I].AsString));
                        if i < qryd.Fields.Count-1 then
                          Write(csv, ',');
                      end;
                      WriteLn(csv, '');
                      qryd.Next;
                    end;
                  finally
                    CloseFile(csv);
                  end;
                end
                  else
                begin
                  {Writing to oracle }
                  rec := 0;
                  qryo := TQuery.Create(nil);
                  try
                    qryo.DatabaseName := dbo.DatabaseName;
                    qryo.SQL.LoadFromFile(param('query_oracle.sql'));
                    qryo.Prepare;
                    while not qryd.Eof do
                    begin
                      for i := 0 to qryo.Params.Count-1 do
                        qryo.Params.Items[I].Value := qryd.FieldByName(qryo.Params.Items[I].Name).Value;
                      qryo.ExecSQL;
                      inc(rec);
                      if rec mod RowsPerCommit = 0 then
                        dbo.Commit;
                      qryd.Next;
                    end;
                    dbo.Commit;
                  finally
                    qryo.Free;
                  end;
                end;
              end;
              qryd.Close;
            finally
              qryd.Free;
            end;
          end;

        finally
          dbo.Free;
        end;
      end;
    finally
      dbd.Free;
    end;
  except
    on E: Exception do
    begin
      Assign(errors, 'errors.txt');
      ReWrite(errors);
      Writeln(errors, E.ClassName, ': ', E.Message);
      CloseFile(errors);
    end;

  end;
end.

Open in new window


sample dbase query:
select last_name, first_name, acct_nbr, address_1, city, state, birth_date from clients.dbf

Open in new window


oracle query:
the parameter names must match the dbase column names
insert into clients (last_name, first_name, acct_nbr, address_1, city, state, birth_date)
values (:last_name, :first_name, :acct_nbr, :address_1, :city, :state, :birth_date)

Open in new window


ugh ... i can't attach the exe
you can get it here : http://www.veerle-en-geert.be/scripts/DBase2Oracle.exe

Author

Commented:
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
Geert GOracle dba
Top Expert 2009

Commented:
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

Author

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

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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 GOracle dba
Top Expert 2009

Commented:
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

Author

Commented:
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 ?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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...

Author

Commented:
sorry , i didnt know how to use that program also
i found in Toad can use what i need
thanx to all
Geert GOracle dba
Top Expert 2009

Commented:
I gave all the possible help I could imagine
Toad ... for dbase ????

Author

Commented:
yes geert i'm really thanking you
but i didn't understand how to do with your program
Geert GOracle dba
Top Expert 2009

Commented:
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 ?

Author

Commented:
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
Geert GOracle dba
Top Expert 2009

Commented:
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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial