I'm really confused because this was working earlier and I don't know why it is broken now. I've got CSV files that I'm trying to parse. I'm accessing them using the following code:
var file = DriveApp.getFileById(fileID);
var csv = file.getBlob().getDataAsString();
var data = Utilities.parseCsv(csv);
It crashes on that 3rd line. When debugging, I see everything looks normal... file is an object, csv is a string and based on the preview I think the format looks correct for a CSV ('header1,header2,header3 data1,data2,data3 data4,data5,data6...' etc). Then, however, data is undefined. What could be causing this error? I'm not finding much online and debugging didn't provide me with any good insight.
Are there particular symbols in the CSV that would break this function perhaps? There are ' and " symbols in the CSV data... but I don't know why that should matter.
UPDATE:
My file in question has about 16000 lines. I'm currently going through it in segments trying to narrow down the problem. But it isn't quotes; I ran a test with the parser as follows and it worked as expected:
function myFunction() {
var a = 'test""123,456\n';
var b = "abc' 'def,ghi\n";
var h = 'x,y\n';
var str = h+a+b;
var csv = Utilities.parseCsv(str);
Logger.log(str);
Logger.log(csv);
}
The log file looked like this (expected):
x,y
test""123,456
abc' 'def,ghi
[[x, y], [test""123, 456], [abc' 'def, ghi]]
If you have any more ideas to test as to what might be causing this, I can test them.
I tried testing your function slight modified and it looks like it works okay. Here's the one I used. If you can share the data file with us, I'd be glad to take a look at the problem.
function prrrrse()
{
var br = '<br />';
var a = 'test""123,456\n';
var b = "abc' 'def,ghi\n";
var h = 'x,y\n';
var str = h+a+b;
var delimiter = ',';
var csv = Utilities.parseCsv(str,delimiter);
var s = 'str=' + str + br + 'delimiter=' + delimiter + br;
for(var i = 0;i<csv.length;i++)
{
s+='csv[' + i + ']=';
for(var j=0;j<csv[i].length;j++)
{
if(j>0)s+=' , ';
s+=csv[i][j];
}
s+= br;
}
dispStatus('Parsing',s, 800, 600);
}
function dispStatus(title,html,width,height,modal)
{
var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
var width = typeof(width) !== 'undefined' ? width : 400;
var height = typeof(height) !== 'undefined' ? height : 300;
var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
var modal = typeof(modal) !== 'undefined' ? modal : false;
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(width)
.setHeight(height);
if(!modal)
{
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
else
{
SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}
}
For what it's worth, I just had a similar problem crop up, and after trying to find an errant comma, ended up with a simpler solution. Line 18,000-something (out of 20,000+) in the csv somehow had an extra line break (\n) in it. Eliminating the line break solved the problem.
I was dealing with the same problem today and discovered that trailing spaces at the end of some lines were causing the parser to fail. I resolved this with this regular expression: csvString.replace(/[ \t]+$/gm, '')
