Writer: how to use a spreadsheet as bibliographic database?

English
, ,
#1

LO 7.5.3.2, Fedora 38 (linux), KDE Plasma desktop (Qt widgets)

I want to use a spreadsheet as bibliography source instead of the built-in DB.

What I did with success:

  • change data source to the spreadsheet with Edit>Exchange Database
  • point the bibliography to this spreadsheet with Tools>Bibliography Database
    • switch to the spreadsheet with Data Source button
    • associate bibligraphy fields with spreadsheet columns through Column Arrangement button

At this point, the bibliography is correctly displayed and I can also edit the rows.

Back to the Writer document, I want to insert a bibliography entry Insert>TOC & Index>Bibliography Entry, but in the dialog the Short name menu is empty. Apparently the link between Writer and this spreadsheet bibliography is not effective though Base (if it is this application in charge of management) correctly displays everything.

Is there a limitation in the Bibliography feature? Have I missed a step?

#2

When I open Tools>Bibliography with the original dBase table in charge and click button [Column Arrangement], I see that the “Short name” property is mapped to the table column “Identifier” with values like ARJ00, AVV00, DUD00, …
Your spreadsheet should have some column like this, and you should take care that the values are unique, e.g. by means of conditional formatting. Then you can add a bibliographic reference by picking some book by its unique short name.

The same window has a button [Data Source] which lets me choose any registered data source name as a bibliography.

It’s a rather long chain of links and mappings:
[Anything tabular] → [Database.odb] → [Registered Name] → [Table or Query] —> [Bibliography columns]

You changed the link between the first 2 elements.
[my_bibliography.ods] → [biblio.odb] → [Bibliography] → [Sheet or DBRange “biblio”] → [Identifier->Short Name etc.]


A spreadsheet linked to a new Base doc, registered as “Sheet Biblio” with the right mappings between column labels and Bibliography columns would look like this:
[my_bibliography.ods] → [sheet_biblio.odb] → [Sheet Biblio] → [“Sheet1” or “DBRange”] → [Identifier->Short Name etc.]

  1. You can switch the first link in the database menu:Edit>Database>Connection
  2. You can switch the second link (the registered name) via Tools>Options>Databases or right-click in data source window.
  3. You can switch the 3rd link in Writers bibliographic window by choosing another table from the list box on top.
  4. You can switch the bibliographic mappings (which column means what) by pushing the button [Column Arrangement]

One thing I did not notice until now is that I can not choose a query as a bibliographic source which makes the whole thing unusable with a relational database where I store details on authors, publishers, fields of knowledge in separated but related tables. Work-around: Use a view instead of a table. Views appear in the list of tables.
If LO were “my program”, I would drop the entire column mapping and allow for queries with alias names instead. SELECT "Identifier" AS "Short Name", "Name" AS "Book Title", ... FROM "anywhere". This would be similar to the mail merge feature where you redefine differing column names in switched connections by means of query aliases.

#3

Thanks @Villeroy but I already did nearly all this. I have already used Column Arrangement to map spreadsheet column to bibliography fields Short Name, Author, Publisher, … This works well because Bibliography Database displays everything as expected.

I don’t understand your second paragraph about DB connection. IMHO, this is unnecessary. The connection would effectively create a “mirror” .odb from the spreadsheet. Since I can interact with the spreadsheet from the *Bibliography Database, the bibliography engine seems to have taken notice of the data source.

What doesn’t work is tha application to a Writer document. With the standard bibliography DB, when you Insert>TOC & Indexes>Bibliography Entry, the dialog has a Short name menu with all the known identifiers. With the bibliography spreadsheet, this menu is empty, making impossible to reference an entry.

So, why is the spreadsheet recognised as a valid source by Tools>Bibliography Database but returns nothing from Insert>…

Do you really think I should connect to the spreadsheet, thus create a new .odb? My goal is to have a unique source. I’m not sure that a “connection” will forward all updates. In addition when I connect, I must define a full schema, which I try to avoid, relying on column headings as proxy TABLE name.

#4

As far as I know, any biblio or mail merge connection is always a connection to some odb document. You can not register any ods file as a database. A database registration always points to an odb file. A database document connected to some spreadsheet is a not a copy of the spreadsheet. It is a representation of a spreadsheet. It’s a link. Likewise the connection to a dBase directory or to some true database server does not save any data within the database document. It is always a link to some external database. The one and only exception are embedded databases (HSQL or Firebird).
I tested the dBase bibliography copied into a spreadsheet, connected that spreadsheet with a new, registered Base document and used that one as Writer’s bibliography.
The status bar of the database looks like this:
Bildschirmfoto von 2023-05-31 17-02-36
which is the first link.
The second link is the registration of the database document in the options dialog:
Bildschirmfoto von 2023-05-31 17-05-26
Now I can call Writer menu:Tools>“Bibliographic Database…” pick “biblio_sheet” as bibliographic data source and map the columns of this pseudo-database to the required bibliographic fields.

At this point I found a serious bug in the user interface. Let’s concentrate on 3 database columns starting with letter “I”: “Identifier”, “Institutn” and “ISBN”. Assign “Identifier”, then focus the listbox labeled “Institution” and type an “I”. This event pre-selects “Identifier” in the dropped down listbox and removes the previous assignment from the first listbox. The selection change event of that box should not remove anything before the value is actually assigned. This way you lose existing assignments while navigating through the drop-down.
https://bugs.documentfoundation.org/show_bug.cgi?id=155615

Second bug: The form controls are writable although you can not edit the underlying spreadsheet through this form. Storing the modified record fails silently.

#5

I went through the whole connection procedure, switching to the resulting .odb database but this does not change the insertion menu: it remains empty. Apart from the insertion failure, everything works the same as previously with direct access to spreadsheet. Any clue to what I overlooked?

#6

This changes the source of mail merge fields (existing and the default source of new ones).

After clicking the [Data Source] button, the list of available data sources is a list of Base documents. There is no direct connection to spreadsheets.
Tools>Options>Base>Databases is a list of database documents (*.odb). You can not add anything else to that list.
Spreadsheet connections are read-only. The database component can not edit spreadsheet documents which is the reason why dBase a much better choice. dBase is the most common and most simple database format.

#7

Indeed you can. I was not aware of the limitation, so I tried it and it worked. I first added the spreadsheet with Edit>Exchange Database (yes, I know its primary role is for mail merge record but bibliography entries are very similar to mail merge records). It then becomes visible in Tools>Bibliography Database Data Source. The remaining problem is retrieval of “Short name” field to build the menu.
The reason why I insist on spreadsheet is a solution attempt for a question asked by another user who prepared a long bibliography with Calc and does not want to transfer it to dBase (because he – and I – doesn’t know how to convert a spreadsheet to dBase; and it is too long to proceed manually).

#8

Have a look at the registered databases. They all refer to some Base document.
You can easily convert a spreadsheet to dBase via menu:File>Save As…
Save to a dedicated directory and connect a Base document to the dBase directory.
The database will be editable and you do not have to restart the entire office suite in order to update any changes to the database.

#9

Just try to open the existing .dbf directly in Calc. Edit and save.
.
Conversions would be done via SaveAs.
.
There is a second file in the biblio-folder. I didn’t check details, but IMHO it contains the contents for Memo-Type in some columns.
.
Then all you’d have to do is re-ordering your columns to the existing ones. It should also be possible to use the existing Column-Heads, copy them accordingly to your spreadsheet and save. Type of the database-columns is deducted from the appeded codes like ,C, 70
.
If you like upload a shortened .ods, so we can try. 5 lines should be sufficient to see, if the basics work.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.