I am writing a google apps script that pulls the content from a CSV file in a gmail attachment. I need to split the csv data into several an array (with each row being it's own array). I've got all that down.

My problem is this: One of the values in the CSV is a "City, St" format. So when I split the array using string.split(',') I end up with an extra "column." My idea to fix this was to back up and kill that ma in the initial string. Here's the relevant portion of my code:

    var attachments = msgs[i][j].getAttachments();
    for (var k = 0; k < attachments.length; k++) {
      var attachmentData = attachments[k].getDataAsString();
      var regex = new RegExp('\,\s(?:[A-Z]{2}\")', 'gi');
      attachmentData.replace(regex,' ');
...

So what I'm trying to do is just find a ma, followed by a space, followed by exactly two letters, then a quotation mark. I want to just replace the ma with a space. I've also tried

      var regex = new RegExp('(\,\s)([A-Z]{2}\")', 'gi');
      attachmentData.replace(regex,$2);

with no luck. Here's a random sample of the (very long) data string I'm running this on:

Voice,Ining,(###) ###-####,(###) ###-####,,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00:27,$0.000,-,, ,Ining,(###) ###-####,,###,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00

Can anyone see what I'm not seeing as to why this isn't working? (Or have any ideas of a better way to do this?)

I am writing a google apps script that pulls the content from a CSV file in a gmail attachment. I need to split the csv data into several an array (with each row being it's own array). I've got all that down.

My problem is this: One of the values in the CSV is a "City, St" format. So when I split the array using string.split(',') I end up with an extra "column." My idea to fix this was to back up and kill that ma in the initial string. Here's the relevant portion of my code:

    var attachments = msgs[i][j].getAttachments();
    for (var k = 0; k < attachments.length; k++) {
      var attachmentData = attachments[k].getDataAsString();
      var regex = new RegExp('\,\s(?:[A-Z]{2}\")', 'gi');
      attachmentData.replace(regex,' ');
...

So what I'm trying to do is just find a ma, followed by a space, followed by exactly two letters, then a quotation mark. I want to just replace the ma with a space. I've also tried

      var regex = new RegExp('(\,\s)([A-Z]{2}\")', 'gi');
      attachmentData.replace(regex,$2);

with no luck. Here's a random sample of the (very long) data string I'm running this on:

Voice,Ining,(###) ###-####,(###) ###-####,,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00:27,$0.000,-,, ,Ining,(###) ###-####,,###,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00

Can anyone see what I'm not seeing as to why this isn't working? (Or have any ideas of a better way to do this?)

• javascript
• regex
• csv
• google-apps-script
• google-apps

2 Answers 2

The replace() method returns a new string with some or all matches of a pattern replaced by a replacement.

The str.replace does not change the string, where as returns a new string with the replace. Hence you may want to write something like

var regex = new RegExp('(,\\s)([A-Z]{2}")', 'gi');
var replacedData = attachmentData.replace(regex,'$2');

Note

You can drop the first capture group as

var regex = new RegExp(',\\s([A-Z]{2}")', 'gi');
var replacedData = attachmentData.replace(regex,'$1');

You can make use of a regex with this condition and then print back the block with $1 together with the space:

s = s.replace(/,( [A-Z]{2}")/, ' $1');
               ^^         ^    ^^^
           ma ^^^^^^^^^     print back replacing ma with space
                   |
                   catch the group of space + two letters + "

See it live:

var s = 'Voice,Ining,(###) ###-####,(###) ###-####,,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00:27,$0.000,-,, ,Ining,(###) ###-####,,###,,,"Dallas, TX",12/12/2014,06:26 PM,Phone Call,Voicemail,00:00';
s = s.replace(/,( [A-Z]{2})/, ' $1');
document.write(s)