Extra blank row in CSV report

djbrody

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

This would mean you have problem in your input data all_funding_trans.
Check it out.

djbrody

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

I don't see an issue in your code, but try to log the sql output to isolate the possible source of the problem.

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(req.data)
}

djbrody

+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
    },

djbrody

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}}