Home / GitHub Page

Rough Method to Include Spreadsheets in Joplin

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.
3 Likes

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

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

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

4 Likes

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

4 Likes

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

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

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

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

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

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

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

2 Likes

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?

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

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.