Rough Method to Include Spreadsheets in Joplin

Features
1

Hey,

I created a rough method to include spreadsheets easily in Joplin - feel free to improve it. Here is how it works:

1. Create a spreadsheet in whatever software you use and then export the spreadsheet as a .csv with delimiters set to "|" (the pipe symbol). Your output should look something like this:



csv_example

2. Then you can run the following script on your spreadsheet file (save it to a python file first):

    import sys
    filename = sys.argv[1]
    out_file = sys.argv[1][:-3] + "md"

    with open(filename) as f:
        with open(out_file, "w") as of:
            i = 0
            for line in f:
                if i == 0:
                    line = line.strip("\n")
                    line = line.split("|")
                    columns = len(line)
                    of.write("|".join([str(x) for x in line]) + "\n")
                    of.write("|:---: " * columns + "|\n")
                    i = 1
                else:
                    line = line.strip("\n")
                    line = line.split("|")
                    of.write("|".join([str(x) for x in line]) + "\n")

An example of how to run this is: 

program.py [YOUR_FILE_NAME].csv

3. You should now have a file called [YOUR_FILE_NAME].md. This markdown is now rendered in GitHub flavored Markdown and you can simply copy the contents of this file and place it into your Joplin notebook. :slight_smile:

Notes:

  • The script makes the assumption that the first line contains the name of your columns
  • The output table has each cell centered.
5 Likes
2

This looks nice. How about going 2 steps further?

  1. create a function create_table_from_csv (or createTableFromCSV) in javascript and then
  2. use a menu item to open a dialog to load a csv file and add the table to the current note at the cursor position

That would be a nice feature!

1 Like
3

Hey tessus,

Thanks for the feedback. That would be amazing! But I do not have any expertise in javascript and do not have much band width to take on learning the skill - does anyone in the community have the skill?

Just wondering, why would it have to be javascript?

In regards to point 2, how would I do this? I apologize - just not very familiar with the architecture of Joplin.

I feel this would be a great feature to add.

Thanks!

EDIT:

I attempted converting my Python script to JavaScript - can you tell me if it works as a JS function? It should function exactly as the Python one did.

I used Transcrypt to convert my Python to JS - I am not JS programmer but it looks rather messy.

'use strict';
var sys = {};
import {
  AssertionError,
  AttributeError,
  BaseException,
  DeprecationWarning,
  Exception,
  IndexError,
  IterableError,
  KeyError,
  NotImplementedError,
  RuntimeWarning,
  StopIteration,
  UserWarning,
  ValueError,
  Warning,
  __JsIterator__,
  __PyIterator__,
  __Terminal__,
  __add__,
  __and__,
  __call__,
  __class__,
  __envir__,
  __eq__,
  __floordiv__,
  __ge__,
  __get__,
  __getcm__,
  __getitem__,
  __getslice__,
  __getsm__,
  __gt__,
  __i__,
  __iadd__,
  __iand__,
  __idiv__,
  __ijsmod__,
  __ilshift__,
  __imatmul__,
  __imod__,
  __imul__,
  __in__,
  __init__,
  __ior__,
  __ipow__,
  __irshift__,
  __isub__,
  __ixor__,
  __jsUsePyNext__,
  __jsmod__,
  __k__,
  __kwargtrans__,
  __le__,
  __lshift__,
  __lt__,
  __matmul__,
  __mergefields__,
  __mergekwargtrans__,
  __mod__,
  __mul__,
  __ne__,
  __neg__,
  __nest__,
  __or__,
  __pow__,
  __pragma__,
  __proxy__,
  __pyUseJsNext__,
  __rshift__,
  __setitem__,
  __setproperty__,
  __setslice__,
  __sort__,
  __specialattrib__,
  __sub__,
  __super__,
  __t__,
  __terminal__,
  __truediv__,
  __withblock__,
  __xor__,
  abs,
  all,
  any,
  assert,
  bool,
  bytearray,
  bytes,
  callable,
  chr,
  copy,
  deepcopy,
  delattr,
  dict,
  dir,
  divmod,
  enumerate,
  filter,
  float,
  getattr,
  hasattr,
  input,
  int,
  isinstance,
  issubclass,
  len,
  list,
  map,
  max,
  min,
  object,
  ord,
  pow,
  print,
  property,
  py_TypeError,
  py_iter,
  py_metatype,
  py_next,
  py_reversed,
  py_typeof,
  range,
  repr,
  round,
  set,
  setattr,
  sorted,
  str,
  sum,
  tuple,
  zip
} from "./org.transcrypt.__runtime__.js";
import * as __module_sys__ from "./sys.js";
__nest__(sys, "", __module_sys__);
var __name__ = "__main__";
export var filename = sys.argv[1];
export var out_file = sys.argv[1].__getslice__(0, -3, 1) + "md";
var f = open(filename);
try {
  f.__enter__();
  var of = open(out_file,
    "w");
  try {
    of .__enter__();
    var i = 0;
    for (var line of f)
      if (i == 0) {
        var line = line.strip("\n");
        var line = line.py_split("|");
        var columns = len(line); of .write("|".join(function() {
          var __accu0__ = [];
          for (var x of line) __accu0__.append(str(x));
          return __accu0__
        }()) + "\n"); of .write("|:---: " * columns + "|\n");
        var i = 1
      } else {
        var line = line.strip("\n");
        var line = line.py_split("|"); of .write("|".join(function() {
          var __accu0__ = [];
          for (var x of line) __accu0__.append(str(x));
          return __accu0__
        }()) + "\n")
      } of . __exit__()
  } catch (__except0__) {
    if (! of .__exit__(__except0__.name,
        __except0__, __except0__.stack)) throw __except0__;
  }
  f.__exit__()
} catch (__except0__) {
  if (!f.__exit__(__except0__.name, __except0__, __except0__.stack)) throw __except0__;
};

//# sourceMappingURL=test.map
4

To me, it seems rather more work than just using something like this

Tab in excel:
image

Copy-paste to that page:
image

Result:
image

5 Likes
5

Interesting. Since it's based on JavaScript, we could even add this functionality to Joplin.

4 Likes
6

Joplin is written in Javascript (It's a react app).

Did you see @zblesk's comment? This would make the workflow even easier.

7

Hey @tessus and @laurent,

Thanks for the info @zblesk! I was able to extract out the JavaScript as follows:

var editor = document.getElementById("editor")

function columnWidth(rows, columnIndex) {
  return Math.max.apply(null, rows.map(function(row) {
    return row[columnIndex].length
  }))
}

function looksLikeTable(data) {
  return true
}

editor.addEventListener("paste", function(event) {
  var clipboard = event.clipboardData
  var data = clipboard.getData('text/plain').trim()

  if(looksLikeTable(data)) {
    event.preventDefault()
  }else{
    return
  }

  var rows = data.split((/[\n\u0085\u2028\u2029]|\r\n?/g)).map(function(row) {
    console.log(row)
    return row.split("\t")
  })

  var colAlignments = []

  var columnWidths = rows[0].map(function(column, columnIndex) {
    var alignment = "l"
    var re = /^(\^[lcr])/i
    var m = column.match(re)
    if (m) {
        var align = m[1][1].toLowerCase()
        if (align === "c") {
          alignment = "c"
        } else if (align === "r") {
          alignment = "r"
        }
      }
    colAlignments.push(alignment)
    column = column.replace(re, "")
    rows[0][columnIndex] = column
    return columnWidth(rows, columnIndex)
  })
  var markdownRows = rows.map(function(row, rowIndex) {
    // | Name         | Title | Email Address  |
    // |--------------|-------|----------------|
    // | Jane Atler   | CEO   | jane@acme.com  |
    // | John Doherty | CTO   | john@acme.com  |
    // | Sally Smith  | CFO   | sally@acme.com |
    return "| " + row.map(function(column, index) {
      return column + Array(columnWidths[index] - column.length + 1).join(" ")
    }).join(" | ") + " |"
    row.map

  })
  markdownRows.splice(1, 0, "|" + columnWidths.map(function(width, index) {
    var prefix = ""
    var postfix = ""
    var adjust = 0
    var alignment = colAlignments[index]
    if (alignment === "r") {
      postfix = ":"
      adjust = 1
    } else if (alignment == "c") {
      prefix = ":"
      postfix = ":"
      adjust = 2
    }
    return prefix + Array(columnWidths[index] + 3 - adjust).join("-") + postfix
  }).join("|") + "|")

  // https://www.w3.org/TR/clipboard-apis/#the-paste-action
  // When pasting, the drag data store mode flag is read-only, hence calling
  // setData() from a paste event handler will not modify the data that is
  // inserted, and not modify the data on the clipboard.

  event.target.value = markdownRows.join("\n")
  return false
})

Moving on from there, @tessus and @laurent - how could I work on adding this functionality to Joplin (e.g. a copy and paste functionality that automatically converts the copied table to GFM)?

1 Like
8

We definitely have to change the following function first:

function looksLikeTable(data) {
  return true
}

We need some sort of algorithm that checks, if the text in the clipboard is really table data…
Disadvantage of this is that in such a case we can never paste a csv as is.

Or we create a new paste action and menu item, something like Paste Special.
Only then this algorithm is used.

This is a design decision thus @laurent has to sign off on it, before we can do anything.

1 Like
9

Considering this is a bit of a niche feature, I’d say a menu item like “Paste as table” might be OK. But I am very much against the idea of a paste doing anything else than pasting. If I paste text into a MD file, I expect to see text, not some weird whatever.
(I also generally dislike when apps try to preserve formatting, like pastes in Evernote do by default. Automatic table formatting would be like that, but worse. I like markdown’s simplicity, and am willing to use other tools on the off chance that I want the formatting kept.)

1 Like
10

Yes I agree with that, we shouldn’t even try to detect CSV since it’s too complicated a format anyway. I thought the above script would somehow read the Excel binary data and convert this to text but it looks like it just interpret CSV.

In general, it would be nice to have better tools to edit tables. Maybe we could add a new “Table” button in the toolbar, which would have two sub-items:

  • Create or edit table (opens some popup to allow adding or deleting rows and modifying cell)
  • Paste CSV as table (what we’re discussing here)
2 Likes
11

In fact there are plenty of table editor, so we should just use something like this: http://editablegrid.net/en

2 Likes
12

I really like the “Table” button idea.

Ok so http://editablegrid.net/en could be used for creating or editing tables.

What about pasting CSV as a table? @laurent were you thinking that we could use the script @zblesk found to create this feature?

13

For this, that would be the "Paste CSV as table" I mentioned above. Like if the user knows they have CSV data and want to create a table from it, we can provide the option. Of course those are just ideas but it's good to discuss them for the day we need them :wink:

2 Likes
14

Joplin is a Perfect .md editor and that’s enough. Why must the world be a spreadsheet? If anyone need the spreadsheet content the python solution works fine. Python is easy to use and free. I miss the working notification, alarm. Should work, but do not.

15

I, too, would like to transfer a spreadsheet into Joplin as a table but I have no coding skills whatsoever. Is there now any layman’s method of doing this?

16

I need the functionality as well. Currently I use ods files outside of joplin because I can’t easily work with spreadsheets, this is not ideal because an entire set of data I work with is disconnected from the rest of my notes.

2 Likes
17

Before picking this up again, has anybody come to a aolution addressing the issues we have noted during this thread?

18

EDIT:
It occurred to me after shortly after posting comment below that it'd be (relatively) trivial to add CSV processing to rest_uploader. So I did. Now if you dump a CSV into the monitored directory it'll utilize the standard library csv and tabulate module to turn it into a pretty markdown table.

Check out tabulate.
tabulate · PyPI

I just recently found this package while doing some data analysis, and it's really slick. I've been using it with Jupyter Notebooks and pandas but it works from the command line as well. It can handle csv, json, pandas dataframes, etc. Here's a quick example:

If your original file (mycsv.csv) looks like:
Date,Value,OtherValue
2018-01-02,-78,5
2018-01-03,-45,12
2018-01-04,-43,2.3
2018-01-05,-77,14

and you run
tabulate -1 -s , -f pipe -o newfile.md mycsv.csv
It will output:

| Date       |   Value |   OtherValue |
|:-----------|--------:|-------------:|
| 2018-01-02 |     -78 |          5   |
| 2018-01-03 |     -45 |         12   |
| 2018-01-04 |     -43 |          2.3 |
| 2018-01-05 |     -77 |         14   |

And (newfile.md) when rendered will look like:

Date Value OtherValue
2018-01-02 -78 5
2018-01-03 -45 12
2018-01-04 -43 2.3
2018-01-05 -77 14

if you have a whole workflow thing you need to set up you could use rest_uploader to scrape the newly created newfile.md into Joplin... :smiley:

3 Likes
19

If Joplin adds support for spreadsheets it would gain the potential to become a killer app for the office suite, at least for most people.

And when I say spreadsheets support I don't mean a full on excel replacement since excel is something of a database app, I think joplin just needs to be able to support basic personal tables with basics arithmetic and functions

21

Check out the JSheets plugin, it actually is a full excel like spreadsheet implementation! I use it for a few budgeting notes