Thread: importing dbase files daily

importing dbase files daily

From
"Juliet May"
Date:
I'm going to need to add data daily from a dbase file to my postgresql database. I'm trying to come up with the simplest method possible.
 
My idea was to have the user use pgAdmin II with the database migration wizard to copy the files into postgres and then run an insert query off of the newly updated tables. The way the migration wizard works is it first drops the table then re-creates it and imports the data.
 
I was thinking I could set up a rule or a trigger that would cause the insert query to fire instead of having the user manually start it?
 
Am I way off base in the whole way I am setting this up? Would there be an easier way?
 
I'm trying to change a system and if I require the user to do too much I will face resistance and the project will be stalled. I will be working with five different locations/users who will all be updating data daily.
 
Thanks for any help you can give me.

Re: importing dbase files daily

From
"Patrick Hatcher"
Date:
Juliet, I do something similar each day.  What I did was to  setup a cron
job to call a perl script that uploads the new data to a scratch table that
mimicks the table(s) I want to import into and then using either a
stored_proc or SQL in my perl script, do the update to my production table

Patrick Hatcher




|--------+--------------------------------->
|        |          "Juliet May"           |
|        |          <jmay@speark.com>      |
|        |          Sent by:               |
|        |          pgsql-novice-owner@post|
|        |          gresql.org             |
|        |                                 |
|        |                                 |
|        |          08/20/2002 10:58 AM    |
|--------+--------------------------------->
  >------------------------------------------------------------------------------------------------------------|
  |                                                                                                            |
  |      To:     <pgsql-novice@postgresql.org>                                                                 |
  |      cc:                                                                                                   |
  |      Subject:     [NOVICE] importing dbase files daily                                                     |
  >------------------------------------------------------------------------------------------------------------|




I'm going to need to add data daily from a dbase file to my postgresql
database. I'm trying to come up with the simplest method possible.

My idea was to have the user use pgAdmin II with the database migration
wizard to copy the files into postgres and then run an insert query off of
the newly updated tables. The way the migration wizard works is it first
drops the table then re-creates it and imports the data.

I was thinking I could set up a rule or a trigger that would cause the
insert query to fire instead of having the user manually start it?

Am I way off base in the whole way I am setting this up? Would there be an
easier way?

I'm trying to change a system and if I require the user to do too much I
will face resistance and the project will be stalled. I will be working
with five different locations/users who will all be updating data daily.

Thanks for any help you can give me.