join tables from different files

join tables from different files

[Uwe Brauer]

Hi 

I know I can either user 
(org-insert-dblock:aggregate)
(org-insert-dblock:join)

To join or aggregate tables with in the same files. 

But can  I join tables from different files?

Thanks

Uwe Brauer 

Re: join tables from different files

[tbanelwebmin]

Yes you can.
Use an org-id

Suppose you have a table in file x.org, under some title:

--------- x.org ----------------------
* title

| a  |  b |
|----+----|
| aa |  5 |
| bb |  7 |
| aa | 11 |
--------------------------------------


Add an ID by calling: M-x org-id-get-create. You end up with:

--------- x.org ----------------------
* title
  :PROPERTIES:
  :ID:       c8b8bb22-e42e-426f-afb0-4cb19aed27ad
  :END:

| a  |  b |
|----+----|
| aa |  5 |
| bb |  7 |
| aa | 11 |
--------------------------------------

Globally save the new ID by calling M-: (org-id-locations-save). You will see c8b8bb22-e42e-426f-afb0-4cb19aed27ad in the ~/.emacs.d/.org-id-locations file.


In another file, say y.org, you may now reference this remote table with the ID in an aggregated, transposed, or joined block:

--------- y.org ----------------------
#+BEGIN: aggregate :table "c8b8bb22-e42e-426f-afb0-4cb19aed27ad" :cols "a vsum(b)"
| a  | vsum(b) |
|----+---------|
| aa |      16 |
| bb |       7 |
#+END:
--------------------------------------

The wizard for creating the aggregated (or joined, or transposed) block does not (yet) complete remote tables names, only buffer-local ones. The wizard may be called with C-c C-x x (or C-x C-x i on older versions of Org Mode).


Note that those global IDs are also used by the spreadsheet remote() function. Example:

--------- z.org ----------------------
|  b |
|  5 |
|  7 |
| 11 |
#+TBLFM: $1=remote(c8b8bb22-e42e-426f-afb0-4cb19aed27ad,@@#$2)
--------------------------------------


Have fun
Thierry

Le 06/06/2020 à 22:40, Uwe Brauer a écrit :

> Hi 
>
> I know I can either user 
> (org-insert-dblock:aggregate)
> (org-insert-dblock:join)
>
> To join or aggregate tables with in the same files. 
>
> But can  I join tables from different files?
>
> Thanks
>
> Uwe Brauer 
>
>
>

[join rows] (was: join tables from different files)

[Uwe Brauer]

[-- Attachment #1.1.1: Type: text/plain, Size: 1661 bytes --]

>>> "t" == tbanelwebmin  <tbanelwebmin@free.fr> writes:

   > Yes you can.
   > Use an org-id

Thanks! Nice!


I have a another question in this context:
can I join, say 4 tables, but row wise?

Say I have  


#+begin_src 

#+TBLNAME: RK
#+ATTR_HTML: :border 2 :rules all :frame border
| met | Q1 | Q2 |  Q3 | total |
|-----+----+----+-----+-------|
| RK  |  1 |  1 | 0.5 |   2.5 |
#+TBLFM: $5=$2+$3+$4



#+TBLNAME: SVD
#+ATTR_HTML: :border 2 :rules all :frame border
 | met | Q1: | Q2 | Q3 | total |
 |-----+-----+----+----+-------|
 | SVD | 0.5 |  1 |  1 |   2.5 |
#+TBLFM: $5=$2+$3+$4;f1

#+TBLNAME: MIN
#+ATTR_HTML: :border 2 :rules all :frame border
| met | Q1 |  Q2 | total |
|-----+----+-----+-------|
| MIN |  2 | 0.5 |   2.5 |
#+TBLFM: $4=$2+$3


#+TBLNAME: BDF
#+ATTR_HTML: :border 2 :rules all :frame border
| met |   Q1 |   Q2 | total |
|-----+------+------+-------|
| MIN | 0.75 | 1.75 |   2.5 |
#+TBLFM: $4=$2+$3
#+end_src


Since they don't have the same amount of columns, I'd like to join the 4 tables like this

#+begin_src 
#+TBLNAME: Total1
#+ATTR_HTML: :border 2 :rules all :frame border
| met |  Q1 | Q2 |  Q3 | total |
|-----+-----+----+-----+-------|
| RK  |   1 |  1 | 0.5 |   2.5 |
|-----+-----+----+-----+-------|
| SVD | 0.5 |  1 |   1 |   2.5 |
#+TBLFM: $5=$2+$3+$4;f1

#+TBLNAME: Total2
#+ATTR_HTML: :border 2 :rules all :frame border
| met |   Q1 |   Q2 | total |
|-----+------+------+-------|
| MIN |    2 |  0.5 |   2.5 |
|-----+------+------+-------|
| MIN | 0.75 | 1.75 |   2.5 |
#+TBLFM: $4=$2+$3
#+end_src

Is this possible?

regards

Uwe Brauer 

[-- Attachment #1.1.2: Type: text/html, Size: 2418 bytes --]

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [join rows] (was: join tables from different files)

[tbanelwebmin]

Le 07/06/2020 à 17:09, Uwe Brauer a écrit :

>             "t" == tbanelwebmin  <tbanelwebmin@free.fr> writes:
>
>     Yes you can.
>     Use an org-id
>
> Thanks! Nice!
>
>
> I have a another question in this context:
> can I join, say 4 tables, but row wise?
> Say I have  
>
> #+TBLNAME: RK
> #+ATTR_HTML: :border 2 :rules all :frame border
> | met | Q1 | Q2 |  Q3 | total |
> |-----+----+----+-----+-------|
> | RK  |  1 |  1 | 0.5 |   2.5 |
> #+TBLFM: $5=$2+$3+$4
>
> #+TBLNAME: SVD
> #+ATTR_HTML: :border 2 :rules all :frame border
>  | met | Q1: | Q2 | Q3 | total |
>  |-----+-----+----+----+-------|
>  | SVD | 0.5 |  1 |  1 |   2.5 |
> #+TBLFM: $5=$2+$3+$4;f1
>
> #+TBLNAME: MIN
> #+ATTR_HTML: :border 2 :rules all :frame border
> | met | Q1 |  Q2 | total |
> |-----+----+-----+-------|
> | MIN |  2 | 0.5 |   2.5 |
> #+TBLFM: $4=$2+$3
>
> #+TBLNAME: BDF
> #+ATTR_HTML: :border 2 :rules all :frame border
> | met |   Q1 |   Q2 | total |
> |-----+------+------+-------|
> | MIN | 0.75 | 1.75 |   2.5 |
> #+TBLFM: $4=$2+$3
>
> Since they don't have the same amount of columns, I'd like to join the 4 tables like this
> #+TBLNAME: Total1
> #+ATTR_HTML: :border 2 :rules all :frame border
> | met |  Q1 | Q2 |  Q3 | total |
> |-----+-----+----+-----+-------|
> | RK  |   1 |  1 | 0.5 |   2.5 |
> |-----+-----+----+-----+-------|
> | SVD | 0.5 |  1 |   1 |   2.5 |
> #+TBLFM: $5=$2+$3+$4;f1
>
> #+TBLNAME: Total2
> #+ATTR_HTML: :border 2 :rules all :frame border
> | met |   Q1 |   Q2 | total |
> |-----+------+------+-------|
> | MIN |    2 |  0.5 |   2.5 |
> |-----+------+------+-------|
> | MIN | 0.75 | 1.75 |   2.5 |
> #+TBLFM: $4=$2+$3
>
> Is this possible?
>
> regards
> Uwe Brauer 

I'm not aware of anything specific for this purpose. A starting point could be the `append' lisp function:

#+begin_src elisp :var table1=RK :var table2=SVD :colnames t
(append table1 table2)
#+end_src

#+RESULTS:
| met | Q1: | Q2 |  Q3 | total |
|-----+-----+----+-----+-------|
| RK  |   1 |  1 | 0.5 |   2.5 |
| SVD | 0.5 |  1 |   1 |   2.5 |

Re: [join rows] (was: join tables from different files)

[Jude DaShiell]

If what's wanted here is a horizontal join of tables paste(1) might be
used to start the process however the #TBLFMT: line for the new table
would need new formulas and column headers may need adjusting.
Something like this was possible in dBase III+ using modify structure if
memory serves.

On Mon, 8 Jun 2020, tbanelwebmin wrote:

> Date: Mon, 8 Jun 2020 02:18:45
> From: tbanelwebmin <tbanelwebmin@free.fr>
> To: emacs-orgmode@gnu.org
> Subject: Re: [join rows] (was: join tables from different files)
>
> Le 07/06/2020 ? 17:09, Uwe Brauer a ?crit?:
>
> >             "t" == tbanelwebmin  <tbanelwebmin@free.fr> writes:
> >
> >     Yes you can.
> >     Use an org-id
> >
> > Thanks! Nice!
> >
> >
> > I have a another question in this context:
> > can I join, say 4 tables, but row wise?
> > Say I have
> >
> > #+TBLNAME: RK
> > #+ATTR_HTML: :border 2 :rules all :frame border
> > | met | Q1 | Q2 |  Q3 | total |
> > |-----+----+----+-----+-------|
> > | RK  |  1 |  1 | 0.5 |   2.5 |
> > #+TBLFM: $5=$2+$3+$4
> >
> > #+TBLNAME: SVD
> > #+ATTR_HTML: :border 2 :rules all :frame border
> >  | met | Q1: | Q2 | Q3 | total |
> >  |-----+-----+----+----+-------|
> >  | SVD | 0.5 |  1 |  1 |   2.5 |
> > #+TBLFM: $5=$2+$3+$4;f1
> >
> > #+TBLNAME: MIN
> > #+ATTR_HTML: :border 2 :rules all :frame border
> > | met | Q1 |  Q2 | total |
> > |-----+----+-----+-------|
> > | MIN |  2 | 0.5 |   2.5 |
> > #+TBLFM: $4=$2+$3
> >
> > #+TBLNAME: BDF
> > #+ATTR_HTML: :border 2 :rules all :frame border
> > | met |   Q1 |   Q2 | total |
> > |-----+------+------+-------|
> > | MIN | 0.75 | 1.75 |   2.5 |
> > #+TBLFM: $4=$2+$3
> >
> > Since they don't have the same amount of columns, I'd like to join the 4 tables like this
> > #+TBLNAME: Total1
> > #+ATTR_HTML: :border 2 :rules all :frame border
> > | met |  Q1 | Q2 |  Q3 | total |
> > |-----+-----+----+-----+-------|
> > | RK  |   1 |  1 | 0.5 |   2.5 |
> > |-----+-----+----+-----+-------|
> > | SVD | 0.5 |  1 |   1 |   2.5 |
> > #+TBLFM: $5=$2+$3+$4;f1
> >
> > #+TBLNAME: Total2
> > #+ATTR_HTML: :border 2 :rules all :frame border
> > | met |   Q1 |   Q2 | total |
> > |-----+------+------+-------|
> > | MIN |    2 |  0.5 |   2.5 |
> > |-----+------+------+-------|
> > | MIN | 0.75 | 1.75 |   2.5 |
> > #+TBLFM: $4=$2+$3
> >
> > Is this possible?
> >
> > regards
> > Uwe Brauer
>
> I'm not aware of anything specific for this purpose. A starting point could be the `append' lisp function:
>
> #+begin_src elisp :var table1=RK :var table2=SVD :colnames t
> (append table1 table2)
> #+end_src
>
> #+RESULTS:
> | met | Q1: | Q2 |  Q3 | total |
> |-----+-----+----+-----+-------|
> | RK  |   1 |  1 | 0.5 |   2.5 |
> | SVD | 0.5 |  1 |   1 |   2.5 |
>
>
>
>

-- 

Re: [join rows]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 441 bytes --]

>>> "JD" == Jude DaShiell <jdashiel@panix.com> writes:

   > If what's wanted here is a horizontal join of tables paste(1) might be

I am not sure what paste(1) means here. Could you please explain?

   > used to start the process however the #TBLFMT: line for the new table
   > would need new formulas and column headers may need adjusting.
   > Something like this was possible in dBase III+ using modify structure if
   > memory serves.

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [join rows]

[tomas]

[-- Attachment #1: Type: text/plain, Size: 599 bytes --]

On Mon, Jun 08, 2020 at 01:09:21PM +0200, Uwe Brauer wrote:
> >>> "JD" == Jude DaShiell <jdashiel@panix.com> writes:
> 
>    > If what's wanted here is a horizontal join of tables paste(1) might be
> 
> I am not sure what paste(1) means here. Could you please explain?

Paste is a classical UNIX command to "join" (not really in the SQL sense,
just by sequence number) files consisting of lines. The "(1)" is the
customary way of hinting at the man section (1, aka "Executable programs
and shell commands") where the doc is to be found.

Just do "man paste" in a shell.

Cheers
-- t

[-- Attachment #2: Digital signature --]
[-- Type: application/pgp-signature, Size: 198 bytes --]

Re: [join rows]

[Jude DaShiell]

paste is a unix command outside of emacs used to concatenate files
horizontally as opposed to vertically as cat does.

On Mon, 8 Jun 2020, Uwe Brauer wrote:

> Date: Mon, 8 Jun 2020 07:09:21
> From: Uwe Brauer <oub@mat.ucm.es>
> To: emacs-orgmode@gnu.org
> Subject: Re: [join rows]
>
> >>> "JD" == Jude DaShiell <jdashiel@panix.com> writes:
>
>    > If what's wanted here is a horizontal join of tables paste(1) might be
>
> I am not sure what paste(1) means here. Could you please explain?
>
>    > used to start the process however the #TBLFMT: line for the new table
>    > would need new formulas and column headers may need adjusting.
>    > Something like this was possible in dBase III+ using modify structure if
>    > memory serves.
>

-- 

Re: [join rows]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 325 bytes --]

>>> "JD" == Jude DaShiell <jdashiel@panix.com> writes:

   > paste is a unix command outside of emacs used to concatenate files
   > horizontally as opposed to vertically as cat does.

Ok, but I was thinking of a lot of tables in one file, so that solution
looks complicated, while terry's lisp solution will do that 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [join rows]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 312 bytes --]


   > Le 07/06/2020 à 17:09, Uwe Brauer a écrit :

   > I'm not aware of anything specific for this purpose. A starting point could be the `append' lisp function:

   > #+begin_src elisp :var table1=RK :var table2=SVD :colnames t
   > (append table1 table2)
   > #+end_src

Thanks works like charm!

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]