Thread: exporting join results
I have some tables that I am gathering data in. Some of the data gathered goes into personal table and the survey results goes into the survey table. I can do a join with no problem to see my results with something like: select * from personal,survey WHERE personal.pid=survey.pid; I can do a dump of either table again with no problem. What I want is to get a dump of the join as a comma separated text file so that I can import it into my statistical software. Any ideas on how to do it? Thanks for the help. -- Dr. Roy F. Cabaniss Associate Professor of Marketing University of Arkansas Monticello http://cabanisspc.uamont.edu
Either create a view and access it from an spreadsheet via ODBC, or SELECT INTO another table, then dump it. Good luck. Fernando. En un mensaje anterior, Roy Cabaniss escribió: > I have some tables that I am gathering data in. Some of the data > gathered goes into personal table and the survey results goes into the > survey table. > > I can do a join with no problem to see my results with something like: > > select * from personal,survey WHERE personal.pid=survey.pid; > > I can do a dump of either table again with no problem. > > What I want is to get a dump of the join as a comma separated text file > so that I can import it into my statistical software.
On 29 May 2003, Roy Cabaniss wrote: > I have some tables that I am gathering data in. Some of the data > gathered goes into personal table and the survey results goes into the > survey table. > > I can do a join with no problem to see my results with something like: > > select * from personal,survey WHERE personal.pid=survey.pid; > > I can do a dump of either table again with no problem. > > What I want is to get a dump of the join as a comma separated text file > so that I can import it into my statistical software. > > Any ideas on how to do it? join into another table, then export that table.
On Thu, May 29, 2003 at 10:54:37 -0500, Roy Cabaniss <rcaban@cabanisspc.uamont.edu> wrote: > I have some tables that I am gathering data in. Some of the data > gathered goes into personal table and the survey results goes into the > survey table. > > I can do a join with no problem to see my results with something like: > > select * from personal,survey WHERE personal.pid=survey.pid; > > I can do a dump of either table again with no problem. > > What I want is to get a dump of the join as a comma separated text file > so that I can import it into my statistical software. > > Any ideas on how to do it? You can concatenate the columns you want with commas and quotes as necessary. Also you should use coalesce to handle any null values.
If you don't want to deal with another table that holds all of your data, you could export the data using a query with a few psql commands. \a -- unaligned output, i.e. without space padding \f ',' -- specify comma field separator \t -- tuples (i.e. data) only, if that is what you want \o filename -- destination file for your data Now run your query, and you should have what you want. Shouldn't be too hard to make a script out of that. __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com
On Thu, 2003-05-29 at 11:07, Fernando Schapachnik wrote: > Either create a view and access it from an spreadsheet via ODBC, or SELECT INTO > another table, then dump it. > > Good luck. > The select into worked quite well. It would have been cool to have gotten the joined structure along with the csv dump but I can deal with it as it came. (the invocation of psql gave an unknown -- error and I was sure that the select into would do the trick so I didn't try to track down the error) Thanks to all that helped. -- Roy Cabaniss <rcaban@cabanisspc.uamont.edu> Cabaniss Class