Skip to content

Instantly share code, notes, and snippets.

@torjusb
Created February 25, 2015 07:59
Show Gist options
  • Save torjusb/7d6baf4b68370b4ef42f to your computer and use it in GitHub Desktop.
Save torjusb/7d6baf4b68370b4ef42f to your computer and use it in GitHub Desktop.
Handle pasted text from Excel, and split it into arrays of rows and columns
var clipboardData = window.clipboardData || event.clipboardData || event.originalEvent && event.originalEvent.clipboardData;
var pastedText = clipboardData.getData("Text") || clipboardData.getData("text/plain");
if (!pastedText && pastedText.length) {
return;
}
// Parse the pasted text from Excel into rows.
// Pasted text is usually separated by a new line for each row,
// but a single cell can contain multiple lines, which is what
// we pars out in the first `replace`.
//
// We find all text within double-quotes ('"') which has new
// lines, and put the text within the quotes into capture
// groups. For each match, we replace its contents again, by
// removing the new lines with spaces.
//
// Then lastly, once we've joined all the multi line cells, we
// split the entire pasted content on new lines, which gives
// us an array of each row.
//
// Since Windows usually uses weird line-endings, we need to
// ensure we check for each of the different possible
// line-endings in every regexp.
//
// It also handles cells which contains quotes. There appears
// to be two ways this is handled. In Google Docs, quotes within
// cells are always doubled up when pasting, so " becomes "".
// In Libre Office, the quotes are not normal quotes, some
// other character is used, so we don't need to handle it any
// differently.
var rows = pastedText.replace(/"((?:[^"]*(?:\r\n|\n\r|\n|\r))+[^"]+)"/mg, function (match, p1) {
// This function runs for each cell with multi lined text.
return p1
// Replace any double double-quotes with a single
// double-quote
.replace(/""/g, '"')
// Replace all new lines with spaces.
.replace(/\r\n|\n\r|\n|\r/g, ' ')
;
})
// Split each line into rows
.split(/\r\n|\n\r|\n|\r/g);
@thi-bondle
Copy link

Thank you, that's what I'm looking for

@tonytee88
Copy link

Life saver! Used this to get ChatGPT responses on different rows. Amazing :) Thanks!

@ianthedev
Copy link

ianthedev commented Nov 1, 2023

Thanks. It's useful in most cases. However, it doesn't work if a cell contains single-line data with a tab character, such as the following data:

	"test	"	

The above code represents a 1-row, 3-column data. The first and the last cells are empty, and the second cell contains test .

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment