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
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 :-) Matt. Craig White 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 > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >
Craig White 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 > > Craigcheckout "psql \copy" (http://www.postgresql.org/docs/8.1/interactive/app-psql.html) -- Posted via http://www.ruby-forum.com/.
OK - a little ;-) 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 On Tue, 2006-05-30 at 16:25 +0100, Matthew Law wrote:> 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 :-) > > Matt. > > Craig White 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 > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
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 -- Posted via http://www.ruby-forum.com/.
Craig, I would just use the following command from within the Postgres command prompt... "copy tablename from ''/dir/filename'';" It works well and is fast for even large amounts of data. Your file should have the contents in the same order as the columns in your table and can be delimited with any character as long as you specify it as part of the command. I believe the default is tab-delimited. See "\h copy" from within the Postgres prompt for more details. Hope this helps. Nathan -----Original Message----- From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Craig White Sent: Tuesday, May 30, 2006 10:14 AM To: rails@lists.rubyonrails.org Subject: [Rails] Rails - Postgres - importing data 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 _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails