TOPIC:

Exported csv file does not have a header with mime type text/csv. 4 weeks 1 day ago #262781

  • Topic Author
  • hoekbrwr
  • Offline
  • Senior Breezer
  • Senior Breezer
  • Posts: 91
  • Thanks: 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.
Attachments:

Please Log in or Create an account to join the conversation.

Last edit: Post by hoekbrwr.

Exported csv file does not have a header with mime type text/csv. 4 weeks 1 day ago #262782

  • tihana.krivic's Avatar
  • tihana.krivic
  • Offline
  • Moderator
  • Moderator
  • Posts: 6926
  • Karma: 3
  • Thanks: 493
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

Please Log in or Create an account to join the conversation.

Exported csv file does not have a header with mime type text/csv. 4 weeks 19 hours ago #262792

  • Topic Author
  • hoekbrwr
  • Offline
  • Senior Breezer
  • Senior Breezer
  • Posts: 91
  • Thanks: 5
I edited my message with some extra info. So be aware of that, it might help a bit!

Please Log in or Create an account to join the conversation.

Exported csv file does not have a header with mime type text/csv. 4 weeks 11 hours ago #262799

  • tihana.krivic's Avatar
  • tihana.krivic
  • Offline
  • Moderator
  • Moderator
  • Posts: 6926
  • Karma: 3
  • Thanks: 493
Hi,

we will check it, and come back to you

Regards,
Tihana

Please Log in or Create an account to join the conversation.

Exported csv file does not have a header with mime type text/csv. 4 weeks 5 hours ago #262817

  • Mirec's Avatar
  • Mirec
  • Offline
  • Ultimate Breezer
  • Ultimate Breezer
  • Posts: 957
  • Karma: 2
  • Thanks: 50
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

Please Log in or Create an account to join the conversation.

Exported csv file does not have a header with mime type text/csv. 4 weeks 5 hours ago #262819

  • Topic Author
  • hoekbrwr
  • Offline
  • Senior Breezer
  • Senior Breezer
  • Posts: 91
  • Thanks: 5
I will look into it later on the day. I am not at my desk this afternoon.

Please Log in or Create an account to join the conversation.

Time to create page: 0.051 seconds

BreezingForms Pro 1.4.7 for WordPress Released!

Available in the membership section.

September Discount!

Massive discounts on all subscriptions!

Get Your Subscription Here

Quick Links

Downloads

BreezingForms

ContentBuilder

BreezingCommerce

Templates

Documentation

BreezingForms

ContentBuilder

BreezingCommerce

Apprendre BreezingForms (French Community)

Apprendre et maîtriser BreezingForms par des tutoriels et exemples, le tout en français

breezingforms.eddy-vh.com

Questions et réponses sur les forums de l'AFUJ

AFUJ

Special Offer

Summer Sale! All subscriptions at a special price!

Includes prio support, all of our current and future Joomla!® extensions and Joomla!® templates for the duration of your membership.

Get it from here

3rd Party Discount - 25% Off

We help you to keep your costs under control. If you are a new member and purchased a form building tool from a different form vendor, then you'll get a 25% discount on our subscription plans.

How to receive the discount:

Send us a quick email to sales@crosstec.org with a proof of purchase (for example a paypal receipt), await payment instructions and enjoy your membership!