@jan_blaha I thought I would be clever and add this to the email script

Error while executing user script. Unexpected token ':'
evaluate-user-script.js:7
  "template": { "name": csv_all_funding_trans}
            ^

the script looks like this

const axios = require('axios')

async  function beforeRender(req, res)  {
  const resData =  await axios.get('http://jsonplaceholder.typicode.com/posts')
  console.log(resData.data)
  req.data.posts = resData.data
  "template": { "name": csv_all_funding_trans}
  "options": {  
    "reports": {
      "save": true
      "blobName": "/optimove/csv_all_funding_trans.csv"    
    }
  } 
}

Changed report to handlebars and that resolved the extra empty line

{{#each all_funding_trans}}
{{PunterId}},{{PaymentID}},{{UCTRegDate}},{{PSTRegDate}},{{Method_Type}},{{Status}},{{DepOrWD}},{{Amount}}
{{/each}}

+0      Starting rendering request 22 (user: admin)
+1      Rendering template { name: csv_all_funding_trans, recipe: text, engine: jsrender, preview: true }
+3      Data item not defined for this template.
+4      Resources not defined for this template.
+5      Executing script dbo_all_funding_transactions using http-server strategy
+101    {
  all_funding_trans: [
    {
      PunterId: '5680',
      PaymentID: '19839',
      UTCRegDate: '2021-05-02 00:02',
      PSTRegDate: 2021-05-01T17:02:29.000Z,
      Method_Type: 'NMI Credit Cards',
      Status: 'Settled',
      DepOrWD: 'LodgeFunds',
      Amount: 5
    },
    {
      PunterId: '5512',
      PaymentID: '19840',
      UTCRegDate: '2021-05-02 00:03',
      PSTRegDate: 2021-05-01T17:03:53.000Z,
      Method_Type: 'NMI Credit Cards',
      Status: 'Settled',
      DepOrWD: 'LodgeFunds',
      Amount: 10
    },

My data script when run in other tools returns the correct file layout.

What I think since this is happening to all my csv files is the script, I integrated for jsreport. creates the blank row.

// SQL DATA LINK
const sql = require("mssql");

const config = {
  server: "-----",
  port: 1433,
  authentication: {
    options: {
      type: "default",
      userName: "--------",
      password: "---------"
    }
  },
  options: {
    database: "------------",
    enableArithAbort: true,
    trustServerCertificate: true
  }
}
// END SQL LINK
// MAIN QUERY
const query = `
select
       FTMR.PunterId
        ,FTP.FundsTransferPaymentId as PaymentID
        ,FORMAT(FTP.FundsTransferPaymentDatetime, 'yyyy-MM-dd HH:mm') as UTCRegDate
        ,CONVERT(datetime2(0), SWITCHOFFSET(FTP.FundsTransferPaymentDatetime, DATEPART(TZOFFSET, FTP.FundsTransferPaymentDatetime AT TIME ZONE 'Pacific Standard Time'))) AS PSTRegDate
        ,FTM.FundsTransferMethodName AS Method_Type
        ,FTPS.FundsTransferPaymentStatusCode as Status
        ,FTTT.FundsTransferTransactionTypeCode as DepOrWD
        ,FTP.TransactionAmountNetCommission as Amount
FROM
        ELSReadOnly.dbo.FundsTransferMethodRegistration FTMR  (nolock)
        JOIN ELSReadOnly.dbo.FundsTransferPaymentStatus FTPS
            JOIN ELSReadOnly.dbo.FundsTransferPayment AS FTP (NOLOCK)
                JOIN ELSReadOnly.dbo.FundsTransferMethod FTM on FTP.FundsTransferTransactionTypeId = FTM.FundsTransferMethodTypeId on FTP.FundsTransferPaymentStatusId = FTPS.FundsTransferPaymentStatusId
                 on FTP.FundsTransferMethodRegistrationid = FTMR.FundsTransferMethodRegistrationid
            JOIN ELSReadOnly.dbo.FundsTransferTransactionType FTTT (nolock)
                 on FTTT.FundsTransferTransactionTypeId = FTP.FundsTransferTransactionTypeId

WHERE
      (FTP.FundsTransferPaymentStatusId = '4' or '10' = FTP.FundsTransferPaymentStatusId)
    AND
        CONVERT(CHAR, FTP.FundsTransferPaymentDatetime, 112) = CONVERT(CHAR, DATEADD(day,-1, GETDATE()), 112);
`
// END QUERY
// EXPORT FUNCTION
async function beforeRender(req, res) {
  await sql.connect(config);
  const sqlReq = new sql.Request();
  const recordset = await sqlReq.query(query)
  Object.assign(req.data, {
    all_funding_trans: recordset.recordsets[0]
  })
  // console.log(all_funding_trans)
}
sql.close()

@jan_blaha Thanks for the help so far.

Still battling S3 storage.

Log from the console:

2021-04-27T17:34:02.927Z - warn: Error during processing request at https://reports.luckiihhr.com:5489/api/report/csv_all_funding_trans
2021-04-27T17:34:51.284Z - info: Starting rendering request 14 (user: admin)
2021-04-27T17:34:51.284Z - info: Rendering template { name: csv_all_funding_trans, recipe: text, engine: jsrender, preview: true }
2021-04-27T17:34:51.285Z - debug: Data item not defined for this template.
2021-04-27T17:34:51.285Z - debug: Resources not defined for this template.
2021-04-27T17:34:51.285Z - debug: Executing script dbo_all_funding_transactions using http-server strategy
2021-04-27T17:34:51.954Z - debug: Executing script S3_csv_all_funding_trans using http-server strategy
2021-04-27T17:34:51.973Z - debug: Base url not specified, skipping its injection.
2021-04-27T17:34:51.974Z - debug: Rendering engine jsrender using http-server strategy
2021-04-27T17:34:52.029Z - debug: Compiled template not found in the cache, compiling
2021-04-27T17:34:52.031Z - debug: Executing recipe text
2021-04-27T17:34:52.031Z - debug: Executing script anonymous using http-server strategy
2021-04-27T17:34:52.093Z - info: Rendering request 14 finished in 809 ms
2021-04-27T17:34:52.093Z - debug: Skipping storing report. 

Report:

PunterId,PaymentID,UCTRegDate,PSTRegDate,Method_Type,Status,DepOrWD,Amount
{{for all_funding_trans}}
{{:PunterId}},{{:PaymentID}},{{:UCTRegDate}},{{:PSTRegDate}},{{:Method_Type}},{{:Status}},{{:DepOrWD}},{{:Amount}}
{{/for}}

this calls the data and this file store script

function afterRender(req, res) {   
    if (res.meta.reportsOptions) {
        res.meta.reportsOptions.blobName = '/optimove/csv_all_funding_trans.csv' + new Date().getTime()       

    }
  }

The report is creating an extra empty delimited row between records.

PunterId,PaymentID,UCTRegDate,PSTRegDate,Method_Type,Status,DepOrWD,Amount
{{for all_funding_trans}}
{{:PunterId}},{{:PaymentID}},{{:UCTRegDate}},{{:PSTRegDate}},{{:Method_Type}},{{:Status}},{{:DepOrWD}},{{:Amount}}
{{/for}}

Sample data from report csv
PunterId,PaymentID,UCTRegDate,PSTRegDate,Method_Type,Status,DepOrWD,Amount
,,,,,
7723,17912,,Sun Apr 25 2021 17:00:23 GMT+0000 (Coordinated Universal Time),NMI Debit Cards,Received,WithdrawFunds,30
,,,,,
7760,17913,,Sun Apr 25 2021 17:00:53 GMT+0000 (Coordinated Universal Time),NMI Credit Cards,Settled,LodgeFunds,5

@jan_blaha I appreciate the interaction and the learning. I am sure this will help others too.

That solves the subject line but not the file attachment

    {  
        filename: 'punter_account.csv' + new Date().getTime(),
        content: new Buffer(res.content)
    }],

const axios = require('axios')

async  function beforeRender(req, res)  {
  const resData =  await axios.get('http://jsonplaceholder.typicode.com/posts')
  console.log(resData.data)
  req.data.posts = resData.data
}

const nodemailer = require('nodemailer')
async function afterRender(req, res) {
    const transporter = nodemailer.createTransport({
      service: 'SendGrid',
      auth: {
          user: 'apikey',
          pass: 'SG.............................................................S20'
      }
    });

    await transporter.sendMail({
      from: "someone@someco.com",
      to: "me@someco.com",
      subject: "[DEV][Oregon] " + req.template.name,
      text: "Please see attached report CSV file  Private and Confidential.  Internal Use Only.  Not for Distribution.",
      html: "<p>Please see attached <b>Daily Player Account CSV </b> </p> <p>Private and Confidential.  Internal Use Only.  Not for Distribution.</p>",
          attachments: [
        {  
            filename: 'punter_account.csv',
            content: new Buffer(res.content)
        }],
    }
    try {
        await smtpTransport.sendMail(mail)
    } finally {
        smtpTransport.close()
    }
}

Guess not

TypeError [ERR_INVALID_ARG_TYPE]: The "path" argument must be of type string. Received function afterRender
    at validateString (internal/validators.js:124:11)
    at Object.parse (path.js:1283:5)
    at Object.detectMimeType (/home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mime-funcs/mime-types.js:2065:27)
    at Object.detectMimeType (/home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mime-funcs/index.js:490:44)
    at /home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mail-composer/index.js:95:66
    at Array.map (<anonymous>)
    at MailComposer.getAttachments (/home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mail-composer/index.js:86:66)
    at MailComposer.compile (/home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mail-composer/index.js:26:34)
    at /home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mailer/index.js:178:56
    at processPlugins (/home/ubuntu/jsreportapp/node_modules/nodemailer/lib/mailer/index.js:275:28)

@jan_blaha are you suggesting something like this?

    attachments: [
      {  
        filename: function afterRender(req, res) {
                  const templateName = req.template.name
                },
        content: Buffer.from(res.content)
      }],
    })

@jan_blaha

this is the report with the recommended script.

When you run the report, it does not post the file in the S3

However, when you run the report on the scheduler you get
it puts the file with its own name at the s3 root

I hope the clears up the question.