I often use LibreOffice or Excel for their ease of use when writing tables, but then I want to import these tables in TiddlyWiki…
Copy/paste doesn’t work out of the box (imports an image, not a table), and the excellent official XLSX Utils plugin is very powerful (I use it for some projects) but quite tedious to configure.
Here is a quick hack to copy/paste tables from a spreadsheet to TiddlyWiki, crudely converting the table to WikiText.
Thank you FRed. It works good for me.
You are right XLSX Utils plugin is very powerful but quite tedious to configure.
So I have modified this plugin, and delete all settings from it https://heeg.ru/heeg_2.4_excel_php.zip . You can drop any Excel table and all it’s colons will become fields in Tiddlywiki without any settings. In my variant
id=title
name=caption
hide_ prefix in colon skip this colon
I just uploaded a new version (see original post) to fix incorrect name for temporary tiddler, no more <<qualify "$:/temp/inText">> tiddlers in your history…
SK’s plugin works well and comes with added benefits of tables styling and headers.
I’ve built my own solution because I didn’t want javascript, and most of all because in the current TW version when you paste a table from Excel / LibreOffice Calc into a tiddler in edit mode, TW imports a new image instead of pasting TSV text.
There are workarounds to circumvent this, either using an intermediary text editor (copy-pasting from the spreadsheet to the text editor first, then copy-pasting from text editor to TW), or using an intermediary <$edit-text> widget in a tiddler in View mode.
I chose the last option, with the <$edit-text> widget in the editor toolbar button dropdown, and a button to convert TSV pasted text to WikiText and insert the resulting table.
I think this solution can be extended for other use cases like pasting a URL to create a nice bookmark at the bottom of target tiddler or pasting a piece of html content copied from a webpage and this converts it into a nice block of wikitext.
Also Shiraz quick table can create tables for anything contains columns of data separated with a delimiter, no matter what it is. One example is copied Excle/OpenOffice/LiberOffice/… sheet or piece of data
Depending on the use and structure of data in a spreadsheet importing csv to tiddlywiki is quite good and joshuas JSON mangler is very powerful allowing you to generate a tiddler for each row and package it in a plugin.
However thanks for sharing. It is clear we are “spoilt for choice”.
1 – How can I ensure I can SORT the column values? (Without having to redo the whole thing)
2 – Any possibility that NUMERIC columns could, on import, continue to dynamically auto-TOTAL? (What I mean is that I use excel for simple addition/subtraction; the figures mean little without the totals??).
These are only thoughts! The basic thing is neat.
TT
I don’t know how to make sort-enabled tables, nor dynamic table formulas in TW, but if a plugin provides these functionalities I might be able to adapt my code to convert a spreadsheet TSV to whatever syntax the plugin needs to render them.
Thanks for your reply.
There have been a few different solutions to both those issues in plugins.
I’m not that familiar with them or how they work.
On sort, though, it should be possible to have a simple Javascript sorter that would be auto applied to the rendered table. To activate it all you’d need do is add a CSS class to the table. See short discussion at: [tw5] Re: TW5 Sortable Tables? Just a thought!
Hi @tw-FRed, I tried your solution, and it works well. After understanding your implementation, I tried to adapt it to my use case, but I failed. My target is to generate multiple Tiddlers from an Excel sheet instead of just a table.
Adding some more detail, I want to transfer a huge Excel table (filled with requirements) into my TiddlyWiki. In the first row of my Excel table the names of the fields where content hast to be saved is listed. Each following line includes content which should be stored in a new Tiddler.
Here a simplified example:
This is the table
title
caption
text
field_1
field_2
name/0001
Anton
Ich bin …
AAA
123
name/0002
Fred
Ich war …
BBB
456
And the result should be these two Tiddlers
caption: Anton
field_1: AAA
field_2: 123
tags:
title: name/0001
Ich bin …
caption: Fred
field_1: BBB
field_2: 456
tags:
title: name/0002
Ich war …
Is there a way to adapt your solution or should I start a new discussion for converting a worksheet into multiple Tiddlers?
Others will have more details. But what you want is not mere “tables” so much as actual JSON from your excel, with one tiddler per “row”. Check this thread:
There are various online tools for getting an excel spreadsheet into JSON as well, if your task is more of a one-off. Some tinkering may be needed, especially because you need certain fields (title in particular!) before you can import in the standard way…
This quick hack is based on text transformation, wrapped in an edit-mode toolbar button. As such, it’s not really adapted to your use case, it doesn’t even create a single tiddler.
If your table column titles are always the same, I would advise trying the official “XLSX Utils” tiddlywiki plugin, or @Siniy-Kit 's variant they introduced previously in this thread.
Also, @TW_Tones often quotes CSV import using JSON mangler plugin. Although I never tried it personally, it might be of help to you.
If neither solution works, no wonder you’ll find help -or even specially crafted code- by the community, but I suggest you create a new thread for your question to gain more visibility.
The last reply to this topic was 8 months ago. Your reply will bump the topic to the top of its list and notify anyone previously involved in the conversation.
Are you sure you want to continue this old conversation?
Yes!
If you copy and paste from LibreOffice Calc, you’ll get:
But have you tried drag and drop from LibreOffice Calc to a TW??
Decorate your cells in LibreOffice first (text and paragraph styling/justifying and cell colouring, cell borders, etc… ). Add a <center>tag to the whole html and change the <colgroup span="2" width="128">to say, width="50%"or something.
Has this already been brought up? LibreOffice Draw and LibreOffice Writer work the same way. Marry TW use with LibreOffice or OpenOffice and possibly Excel and Word.
editor toolbar button
!