Export a view from postgres to oocalc : wrong column names

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
alexisb
Posts: 3
Joined: Tue Apr 29, 2008 9:59 am

Export a view from postgres to oocalc : wrong column names

Post by alexisb »

Hello,

I have a little problem : I'm working with a postgresql database with oobase using the postgres connector. I've noticed that it was possible to save data in csv by copy/paste table/views in oocalc, which is a very good thing.
Unfortunately, there is a view I'd like to export, but which causes problems : it's a result of a join of several subqueries, and when pasting in oocalc, some column names are not et the right place.

To be more explicit, here is what my view definition looks like :

Code: Select all

SELECT t.id,t.a,t.b,t.c,t.d,t.e,temp1.x as r1,temp2.x as r2, ... , temp60.x as r60
from t, ( SELECT id,x from t2 where y=1) as temp1, ( SELECT id,x from t2 where y=2) as temp2, ( SELECT id,x from t2 where y=3) as temp3, ...
WHERE t.id=temp1.id,t.id=temp2.id, ... , t.id=temp60.id;
So the table I'd like to get should look like :
  • my view
id | a | b | c | d | e | r1 | r2 | r3 | r4 | r5 | ... | r60
And this is what I get :
  • my view
id | a | r1 | r2 | r3 | b | r4 | r5 | r6 | c | r7 | r8 | r9 | d | ... | r60
And here there seems to be some logic : the columns of the first table are equally spaced among the orther columns ( here I put 3 spaces, but in fact it's more, so that the five columns are finely distributed among the 60 columns )

Independantly of the column names, the data in the columns are not affected by this, and so the column name doesn't match anymore the column contains.

Does anybody has this problem before ?

Thanks

Alexis
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Export a view from postgres to oocalc : wrong column names

Post by Villeroy »

When you run the query within Base, do you get the expected result?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
alexisb
Posts: 3
Joined: Tue Apr 29, 2008 9:59 am

Re: Export a view from postgres to oocalc : wrong column names

Post by alexisb »

Villeroy wrote:When you run the query within Base, do you get the expected result?
Yes, in Base I get the right result. It's just when copy/pasting to oocalc that the problem occurs.

Alexis
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Export a view from postgres to oocalc : wrong column names

Post by Villeroy »

I'm more or less clueless. Try this:
Tools>Options...OOo Base>Databases
Register your database.
Now open a spreadsheet, hit F4, browse <your database>Queries and drag the query from the left pane onto a sheet-cell.
If this way of import works properly, the registration of the database gives you two more advantages:
1. You don't have to open the database to use the data in Writer or Calc
2. You can create refreshable links. In Calc: Data>Refresh while having the import range selected or a single cell within.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
alexisb
Posts: 3
Joined: Tue Apr 29, 2008 9:59 am

Re: Export a view from postgres to oocalc : wrong column names

Post by alexisb »

Great, this works fine, and will simplify the usage.

Thanks
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Export a view from postgres to oocalc : wrong column names

Post by Villeroy »

Calc can create data pilots (aka cross tables, aka pivot tables) from datasources as well. Data>Pilot>Start... [X]From registered source ...
Your query looks as if you are after that.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply