1

I am trying to split up the data in a csv file to use in my script. I usually split the data by newline then split that line by comma.

like this:

 var i;
 var fileData = [];
 var id = 'csv file';
 var file = DriveApp.getFileById(id).getAs('text/plain').getDataAsString();
 var newline = dataFile.split('\n');
 for(i in newLine){
  var splitLine = newline[i].split(',');
  fileData.push(splitLine);
};

It works well to split up a csv into an array. I am finding out now that it does not work with quoted newlines or commas in cells.

I usually deal with csv data like this 'value1,value2\n value3,value4'. Now I have a csv file that has data like this '"value1\nvalue1",value2\n value3,value4'.

I found a regex way of spliting data. str.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)"); here

I addapeted it to '\n' like this. var newline = dataFile.split("\n(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");

Now it works, but it is very slow. I know nothing about regex, is there a way to edit this regex split to work faster? all I want to do is ignore the newline in double quotes.

EDIT:

This does not answer the question, how to make the regex split faster, but I have found a workaround for my particular data.

My data has empty space before a newline, so it will have a comma before it like this ",\n" but the newline that is in quotes have two words between it no commas. "value\nvalue". Do to this I wrote the split like this.

var newline = dataFile.split(',\n')

Only splitting the lines I want. This works a lot faster than the regex I had, but it would not work for all data, so I don't consider it an answer to my question. It is what I will be using in lieu of a better solution.

Community
  • 1
  • 1
Mullenb
  • 651
  • 6
  • 20
  • Have you tired to replace/remove the quotes like so str.replace(/\"/gi, "") , before parsing using split or CSV parser? – Jack Brown Feb 21 '17 at 16:42
  • Thanks, but the same thing happens. Both CSV parse and split, split into rows at newline, no matter if in quotes or out. – Mullenb Feb 21 '17 at 18:57

1 Answers1

0

The utilities function to parse CSV doesn't work for you? CsvParser Doc

Juan Diego Antezana
  • 902
  • 4
  • 16
  • I could for the regular CSV file, that I am not having problems with. I just tried it on the file I am working on, and it treated the newline in quotes as if it were a newline. Same problem I have with my example. – Mullenb Feb 21 '17 at 15:56