0

I'm looking for the functionality to process data from Google Sheets, Excel and plain HTML tables.

I'm using clipboard data on paste event to get the contents of clipboard.

var clipText = event.clipboardData.getData('text/plain');

Though I not quite sure how to process the data and which symbols are used to delimit the rows and the columns. Ideally I would like to have an array of rows with array of columns.

Holidays taken in the last six months ID Name July August September October November December 215 Abel 5 2 0 0 0 3 231 Annette 0 5 3 0 0 6 173 Bernard 2 0 0 5 0 0 141 Gerald 0 10 0 0 0 8 99 Michael 8 8 8 8 0 4

So e.g. a plain copy from HTML is results in this format, some cells are delimited with 3 spaces and some with 2 (Gerald 10 -> 0)

My desired result:

[
   [ID, Name, July, August, September, October, November, December],
   [215, Abel, 5, 2, 0, 0, 0, 3],
   [231, Annette, 5, 3, 0, 0, 0, 3]
    ...
]

Are there any special characters that can be used to break / delimit the tabular data from clipboard?

Runnick
  • 613
  • 2
  • 12
  • 30

1 Answers1

0

If you want to delimit by two or more spaces in a row, you can use a regular expression to do it:

const clipText = event.clipboardData.getData('text/plain')
const splitByLines = clipText.split(/\r\n|\n|\r/g, clipText)  // Split lines for Windows (\r\n) or Linux/OSX (\n).  See note below...
const splitBySpaces = splitByLines.forEach(line => line.split(/\s{2,}/g))  // Split each line everywhere there are 2 or more spaces in a row

If you want to support Unicode input or some really obscure systems, you should take a look at what ECMA considers an 'end-of-line' character: https://www.ecma-international.org/ecma-262/10.0/#sec-line-terminators

tenfishsticks
  • 452
  • 5
  • 13