Quick hack to import tables from a spreadsheet

Tips & Tricks
, ,
#1

Hi there,

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.

Spreadsheet2WikiText-EN.json (2.8 KB)
Edit: added focus.
Edit 2: fixed temp tiddler qualify macro use
Edit 3: headers toggles. Numbers and dates alignment

Howto:

  1. Backup your data!
  2. Download the attached JSON file and import it in your wiki (100% WikiText solution, no reload required)
  3. Design your table in Excel / LibreOffice / whatever
  4. Select and copy the table in the clipboard
  5. In your wiki, edit the destination tiddler. Note the new image editor toolbar button
  6. Click on the image toolbar button, paste the table and click on Convert and insert

Disclaimer:

  • Tested on Linux with LibreOffice, and Windows with Excel.
  • I don’t and won’t use Google Sheets, so YMMV…
  • Backup your data before importing this code, and use at your own risk!
  • This code is free for anyone to use, study, copy, build upon, modify, improve and spread the world.

Fred

11 Likes
#2

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

1 Like
#3

Great, thanks for the solution @tw-FRed. How does it compare to SK’s table helper plugin ?

#4

@All

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…

Fred

1 Like
#5

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.

Fred

#6

There is also a table helper in Tobias Beer collection

tb5 — convertable (tobibeer.github.io)

1 Like
#7

@tw-FRed
Great solution. I like it.

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.

Thank you

#8

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

1 Like
#9

@fastfreddy
I think few of codes/plugins for context menu developed by @saqimtiaz, @Yaisog and @ahanniga have a great potential to be used here.

#10

Although it’s javascript, I like Tobias’ solution, simple, efficient, elegant… and his regexp is better than mine! :laughing:

Thanks for the link @Mohammad

Fred

#11

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”.

#12

I like your solution more, as I can hack it without any JS knowledge :sweat_smile:

#13

Whoa, very nice! Works!

As usual I am always asking awkward questions :upside_down_face: !

Either via import or after I have two queries …

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

#14

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.

Do you know of such a plugin ?

Fred

1 Like
#15

@tw-FRed

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!

Best wishes
TT

#16

@All

I just uploaded a new version (see original post) with new features:

  • Checkboxes to define wether 1st row and/or 1st column is a header
  • Crude detection of numbers and dates, to align them to the right. Other values are centered.

Fred

1 Like
#17

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?

1 Like
#18

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…

2 Likes
#19

Hi @y0qurt

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.

Hope this helps,

Fred

3 Likes