Extra blank row in CSV report



  • 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



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



  • 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()


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


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


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

Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.