Apps Script Utilities.parseCsv assumes new row on

Question

When using Utilities.parseCsv() linebreaks encased inside double quotes are assumed to be new rows entirely. The output array from this function will have several incorrect rows.

How can I fix this, or work around it?

Edit: Specifically, can I escape line breaks that exist only within double quotes? ie.

/r/n "I have some stuff to do:/r/n Go home/r/n Take a Nap"/r/n

Would be escaped to:

/r/n "I have some stuff to do://r//n Go home//r//n Take a Nap"/r/n

Edit2: Bug report from 2012: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1871

Answer 1

Retrieved and slightly modified a regex from another reply on another post: https://stackoverflow.com/a/29452781/3547347

Regex: (?=["'])(?:"[^"\\]*(?:\\[\s\S][^"\\]*)*"|'[^'\\]\r\n(?:\\[\s\S][^'\\]\r\n)*')

Code:

  var dataString = myBlob.getDataAsString();
  var escapedString = dataString.replace(/(?=["'])(?:"[^"\\]*(?:\\[\s\S][^"\\]*)*"|'[^'\\]\r\n(?:\\[\s\S][^'\\]\r\n)*')/g, '\\r\\n');

Answer 2

I had this same problem and have finally figured it out. Thanks Douglas for the Regex/code (a bit over my head I must say) it matches up nicely to the field in question. Unfortunately, that is only half the battle. The replace shown will simply replaces the entire field with \r\n. So that only works when whatever is between the "" in the CSV file is only \r\n. If it is embedded in the field with other data it silently destroys that data. To solve the other half of the problem, you need to use a function as your replace. The replace takes the matching field as a parameter so so you can execute a simple replace call in the function to address just that field. Example...

Data:

"Student","Officer

RD

Special Member","Member",705,"2016-07-25 22:40:04 EDT"

Code to process:

var dataString = myBlob().getDataAsString(); 
var escapedString = dataString.replace(/(?=["'])(?:"[^"\](?:\[\s\S][^"\])"|'[^'\]\r\n(?:\[\s\S][^'\]\r\n)')/g, function(match) { return match.replace(/\r\n/g,"\r\n")} ); 
var csvData = Utilities.parseCsv(escapedString);

Now the "Officer\r\nRD\r\nSpecial Member" field gets evaluated individually so the match.replace call in the replace function can be very straight forward and simple.