Cut and Paste from cell with Alt+Enter in cell from EXCEL gives extra rows

[vignette]

Firstly, this is a fantastic plugin. Not a criticism, but I believe this is a bug, where cells have Alt+Enter in them in EXCEL data when cut and paste from EXCEL it creates multiple rows one for each Alt+Enter and separates the data into new rows based on this. EXCEL users often do this to format a single cell of data with returns in the text and this is not a new row when present. If this was fixed our company would be ecstatic at what this grid does for us re allowing load of spread sheet data without spread sheets! This is a great plugin, if it does what we want I'd say we will definitely be willing to donate.

[vignette]

Firstly, please excuse the bad code below but it gives the basic idea of how to fix this. It works in all cases except where the column with the Alt+Enter's in it is the last column in each row - I haven't worked out how to fix it for that case but our users won't be doing that so it's fine for me. Basically, I just changed the parsePasteInput function to not split rows on /n where the /n is in a column produced with text wrap and so bounded with "". Instead of splitting rows for these /n I just post process these after row splitting to put the /n back. Below gives the idea (again please excuse the bad code).

  parsePasteInput: function (input) {
var rows, r, rlen;

//replace any \n instances in input bounded by "" with \\n so as not to split to new row then replace back after split 
//this stops EXCEL based pastes where data has returns within cells from being split into rows
//read all chars in input and from wherever a pattern of \t" or \n" or if start of input and " then replace all occurences of \n
//until next "\t or "\n or end of input
var input3 = input.split(/\t/g);
var input2 = "";
for (var c = 0, clen = input3.length; c < clen; c++)
{
  if ( input3[c][0] == '\"' && input3[c][input3[c].length-1] == '\"')
  {
    input2 = input2 + input3[c].substring(0, input3[c].length-1).substring(1).replace(/\n/g, '\\n')  + '\t'; 
  }
      else
  {
    input2 = input2 + input3[c] + '\t';
  }
}

    rows = input2.split(/\n/g);
    if (rows.length > 1 && rows[rows.length - 1] === '') {
      rows.pop();
    }
    for (r = 0, rlen = rows.length; r < rlen; r++) {
  // replace new lines within cells back after row splits
      rows[r] = rows[r].replace(/\\n/g, "\n");
      rows[r] = rows[r].split("\t");
    }
    return rows;
  }

[vignette]

my comments in the the last posting were a bit misleading so re-posting the code here:

  parsePasteInput: function (input) {
var rows, r, rlen;

//replace any \n instances in input bounded by \t column indicators and "" with \\n so as not to split to new row then replace back after split 
//this stops EXCEL based pastes where data has returns within cells from being split into rows
var input3 = input.split(/\t/g);
var input2 = "";
for (var c = 0, clen = input3.length; c < clen; c++)
{
  if ( input3[c][0] == '\"' && input3[c][input3[c].length-1] == '\"')
  {
    input2 = input2 + input3[c].substring(0, input3[c].length-1).substring(1).replace(/\n/g, '\\n')  + '\t'; 
  }
      else
  {
    input2 = input2 + input3[c] + '\t';
  }
}

    rows = input2.split(/\n/g);
    if (rows.length > 1 && rows[rows.length - 1] === '') {
      rows.pop();
    }
    for (r = 0, rlen = rows.length; r < rlen; r++) {
  // replace new lines within cells back after row splits
      rows[r] = rows[r].replace(/\\n/g, "\n");
      rows[r] = rows[r].split("\t");
    }
    return rows;
  }

[warpech]

Thanks vignette, I will look into it on Monday, if you allow.

[vignette]

Thanks warpech, any time you can spare to look at this would be great.

[warpech]

Thanks for the report and proposal solution. I found a lot of quirks about parsing CSV/TSV values that can come from Excel, LibreOffice or Google Docs. By the method of trial and error I found a function that seems to do the job of parsing input data very well. Could you please check again? I am at your service :)

[vignette]

Great work, I think you've nailed the solution! We will proceed to use this to implement a system for lodging contractor data with our organisation. After development and testing, before release (November) I will organise for a company donation. For now, I made a small personal donation myself of $50 because you've certainly saved me a lot more than that, even for an alpha concept prototype!

[warpech]

Thank you, I appreciate it! Please let me know if you need some special features.