Rails - Postgres - importing data


#1

I am writing a application where there is a lot of data in an old
Filemaker program that I am going to need to do a 1 time import…1 main
table and 2 related tables. I can get the data from Filemaker into a dbf
or csv or Excel file without problem.

Is anyone aware of a utility for postgresql or a rails plug-in that
might make this easier? I am worried about Rails import since I can’t
see a way of importing the related tables while keeping the ‘id’ from
the primary table.

Any suggestions would be appreciated

Craig


#2

How much is a lot? - The easiest way to go with pgsql is to try and
export to tab separated format for each table and use COPY to squirt it
into postgres. You may need to do some munging of the original data to
clean it up, for which you can use Ruby or Perl but other than that you
should have no problems. If it is a LOT of data then excel will puke at
about 65,000 rows… which is at least 935,000 rows short of “a lot”
IMHO :slight_smile:

Matt.


#3

Craig W. wrote:

I am writing a application where there is a lot of data in an old
Filemaker program that I am going to need to do a 1 time import…1 main
table and 2 related tables. I can get the data from Filemaker into a dbf
or csv or Excel file without problem.

Is anyone aware of a utility for postgresql or a rails plug-in that
might make this easier? I am worried about Rails import since I can’t
see a way of importing the related tables while keeping the ‘id’ from
the primary table.

Any suggestions would be appreciated

Craig

checkout “psql \copy”
(http://www.postgresql.org/docs/8.1/interactive/app-psql.html)


#4

OK - a little :wink:

The main db has 150 or so columns but the number of records is small by
that yardstick.

I was thinking that I would do it exactly as you stated but wanted to
canvas for other possibilities.

Thanks

Craig


#5

also note there is a difference in the permissions required with
postgres’s “COPY” command and psql’s “\copy” option.

The former requires server level privs (which, if you are in a shared
hosting environment, not likely to have).

see the “notes” section here:
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html