Bienvenido, Invitado
Recordarme

TEMA:

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262781

  • Autor del tema
  • hoekbrwr
  • Fuera de línea
  • Senior Breezer
  • Senior Breezer
  • Mensajes: 104
  • Gracias recibidas: 5
When I send an admin email to Gmail with an attachment in .csv format the filetype is unknown! Hence this will be shown as application/octet-stream and is binary. The header of the attachment file is not set to mime-type "text/csv".
Any attempt to read this, which is in origin a text file, will only give gibberish when parsed as CSV in GmailApp.
I am not sure that the format will change when you somehow define the output as CSV(not only in name!). Now I have no idea what the format is to retrieve the original data.
I can import the file into Excel for example and the data are of course correct. However, I cannot process the data in the GMailApp script.
Possibly you can point me to a conversion tool that I can use in GMailApp script (as is done in Excel) ?
I have attached your file with a .txt extension, but actually it was .csv to be able to upload.

I did a check of the CSV file on csvlint.io/ which signalled some problems. Hopefully, this makes sense to you.
This is the start of the file: '��"ID";"SUBMITTED";"USER_ID"; ' which gives the problem : Structural problem: Unexpected whitespace on row 1
Every row gives an error: Structural problem: Unexpected whitespace.
A general error is shown also.
Your CSV appears to use LF line-breaks. While this will be fine in most cases, RFC 4180 specifies that CSV files should use CR-LF (a carriage-return and line-feed pair, e.g. \r\n). This may be labeled as "Windows line endings" on some systems.

To give you a bit of a headstart I give you the GAS code I use
function importCSV() {

  var threads = GmailApp.search('in:inbox after:2021/08/14 to:"example@gmail.com" subject:"Form record received" has:attachment');
  var msgs = GmailApp.getMessagesForThreads(threads);  
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbox");
  sheet.clear();
  var r=1;

  for (var i = 0 ; i < msgs.length; i++) {
    for (var j = 0; j < msgs[i].length; j++) {
      var attachments = msgs[i][j].getAttachments();
      for (var k = 0; k < attachments.length; k++) {  
        var attachment = attachments[k].setContentTypeFromExtension();
        var csvData = Utilities.parseCsv(attachment.getDataAsString(), ";");
        sheet.getRange(r, 1, csvData.length, csvData[0].length).setValues(csvData);
        r++;
        Logger.log('Message "%s" with attachment "%s" (%s bytes)',
                msgs[i][j].getSubject(), attachments[k].getName(), attachments[k].getSize());
      }
    }
  }
}

You have to use your own gmail account and send a few form data files to your account.
Login to that account make an empty sheet with a tab Inbox and copy the code to the Script environment.
Adjuntos:

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

Última Edición: Post by hoekbrwr.

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262782

  • Avatar de tihana.krivic
  • tihana.krivic
  • Ausente
  • Moderator
  • Moderator
  • Mensajes: 11812
  • Karma: 3
  • Gracias recibidas: 763
Hi,

Possibly you can point me to a conversion tool that I can use in GMailApp script (as is done in Excel) ?

Hmm..I will investigate this, because I usually use Excel, please give me some time

Regards,
Tihana

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262792

  • Autor del tema
  • hoekbrwr
  • Fuera de línea
  • Senior Breezer
  • Senior Breezer
  • Mensajes: 104
  • Gracias recibidas: 5
I edited my message with some extra info. So be aware of that, it might help a bit!

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262799

  • Avatar de tihana.krivic
  • tihana.krivic
  • Ausente
  • Moderator
  • Moderator
  • Mensajes: 11812
  • Karma: 3
  • Gracias recibidas: 763
Hi,

we will check it, and come back to you

Regards,
Tihana

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262817

  • Avatar de Mirec
  • Mirec
  • Fuera de línea
  • Ultimate Breezer
  • Ultimate Breezer
  • Mensajes: 3262
  • Karma: 2
  • Gracias recibidas: 160
Hi,


could you please use this code below:
function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
  var convertedSheet = ss.getSheetByName("Inbox");//insert name of your sheet
  if (convertedSheet != null) {
  
    var sheetName = convertedSheet.getName();
    Logger.log("Sheet Name:", sheetName); // logs correct sheet name
    
    // this approach to constructing url is suggested in a similar post instead of using "getUrl()
    var url = 'https://docs.google.com/spreadsheets/d/'+ SpreadsheetApp.getActiveSpreadsheet().getId();
    url += '#gid=';
    url += convertedSheet.getSheetId();
    url += "/export?exportFormat=csv&format=csv";
   
    
    Logger.log("URL: ", url); //this brings up correct sheet when I paste the url in the browser. It does not trigger a  download of it in the browser - should it be?
  
    var email = Session.getActiveUser().getEmail();
    Logger.log(email); //correct

    var subject = "New Submission is Attached! ";
    var body = "You have a new submission!";

    var requestData = {method:"GET", headers:{"authorization":"Bearer "+      ScriptApp.getOAuthToken()}};
  var result = UrlFetchApp.fetch(url, requestData).getBlob();


    MailApp.sendEmail(email,
                      subject, 
                      body,
                      {
                          attachments: [{
                          fileName: sheetName + ".csv",
                          content: result.getBytes(),
                          mimeType: "text/csv"
                        }]
                      });
    
    }
}

Let me know the result!
Regards,
Mirko

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

Exported csv file does not have a header with mime type text/csv. 1 año 3 meses antes #262819

  • Autor del tema
  • hoekbrwr
  • Fuera de línea
  • Senior Breezer
  • Senior Breezer
  • Mensajes: 104
  • Gracias recibidas: 5
I will look into it later on the day. I am not at my desk this afternoon.

Por favor, Identificarse o Crear cuenta para unirse a la conversación.

  • Página:
  • 1
  • 2
  • 3
Tiempo de carga de la página: 0.056 segundos