Question Marks Symbols when importing CSV

Question

I'm importing a CSV from an email with the following code:

function RetrieveAttachment() 
{
  var threads = GmailApp.search("Report*")
  var msgs = GmailApp.getMessagesForThreads(threads);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var today= new Date();today.setDate(today.getDate());
  var today= Utilities.formatDate(today, "GMT+1", "dd.MM.yyyy");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");
  
  for(var i = 0; i < msgs.length; i++)
  {
    for(var j = 0; j < msgs[i].length; j++)
    {
      var msgdate = Utilities.formatDate(new Date(msgs[i][j].getDate()), "GMT+1", "dd.MM.yyyy");
      
      if(msgdate == today)
      {
        var attachments = msgs[i][j].getAttachments();
        
        for(var k = 0; k < attachments.length; k++)
        {
          var attachmentName = attachments[k].getName();
          var stringValue = attachmentName.search("*");
          
          if(stringValue > -1)
          {
            var attachmentData = attachments[k].getDataAsString();
            var parseCsv = Utilities.parseCsv(attachmentData, ",");
            
            sheet.clearContents();
            sheet.getRange(1,1, parseCsv.length, parseCsv[0].length).setValues(parseCsv);
          }
        }
      }
    }
  }
}

Usually this works just fine, but with one specific csv I get via email only this two question marks are being written in the Spreadsheet:

I checked with the debugger and apparently the whole content of the csv is being read, however not correctly parsed:

I presume this is some coding problem, however I tried with the base64Encode/base64Decode utilities without any success.

Does someone have any clue on how to solve this?

This looks like a UTF8/16/something decoding error, perhaps, since there is some recognizable text in there. — General Grievance, Oct 12 '20 at 12:30
Does the answer here help? (Convert to blob, convert to utf16) https://stackoverflow.com/questions/11332816/google-apps-script-utf8 — General Grievance, Oct 12 '20 at 12:44
That was it, thank you very much. It turnes out, the file was UTF-16, so I solved it like this: var attachmentData = attachments[k].getDataAsString("UTF-16"); — willyloe, Oct 12 '20 at 13:19
Nice! A couple things: it would improve your answer if you comment above the line of code you changed so readers can easily find it, and I think you're allowed to accept your own answer. — General Grievance, Oct 12 '20 at 13:31

score 2 · Accepted Answer · edited Oct 13 '20 at 13:31

The original CSV file was UTF-16 and tab separated, I solved it specifying the encoding and different separator:

var attachmentData = attachments[k].getDataAsString("UTF-16");
var parseCsv = Utilities.parseCsv(attachmentData, "\t");

Here the new Code

function RetrieveAttachment() 
{
  var threads = GmailApp.search("Report*")
  var msgs = GmailApp.getMessagesForThreads(threads);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var today= new Date();today.setDate(today.getDate());
  var today= Utilities.formatDate(today, "GMT+1", "dd.MM.yyyy");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");
  
  for(var i = 0; i < msgs.length; i++)
  {
    for(var j = 0; j < msgs[i].length; j++)
    {
      var msgdate = Utilities.formatDate(new Date(msgs[i][j].getDate()), "GMT+1", "dd.MM.yyyy");
      
      if(msgdate == today)
      {
        var attachments = msgs[i][j].getAttachments();
        
        for(var k = 0; k < attachments.length; k++)
        {
          var attachmentName = attachments[k].getName();
          var stringValue = attachmentName.search("*");
          
          if(stringValue > -1)
          {
            var attachmentData = attachments[k].getDataAsString("UTF-16");
            var parseCsv = Utilities.parseCsv(attachmentData, "\t");
            
            sheet.clearContents();
            sheet.getRange(1,1, parseCsv.length, parseCsv[0].length).setValues(parseCsv);
          }
        }
      }
    }
  }
}

Question Marks Symbols when importing CSV

1 Answers1

Related