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

9 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

#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