Remove optional hyphens from CSV export for Excel

[sts-ryan-holton]

Hi, I'm using DataTables 1.12.1 in my project, along with the ability to export via a CSV. I've modified the code to be able to include child row details as part of my CSV and exporting works well, my issue I'm facing is when importing the CSV into Excel, I'm seeing optional hyphens at the start of currencies.

These optional hyphens aren't part of the CSV contents, and when importing into a cloud solution like Google sheets, everything looks fine.

I'm thinking of switching to the Export as Excel option in DataTables because of this, but wondered am I missing something that could resolve this issue by removing the optional hyphen?

Scripts used

// jQuery
echo $this->Html->script('https://code.jquery.com/jquery-3.5.1.min.js');

// DataTables
echo $this->Html->script('https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js');
echo $this->Html->script('https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min.js');
echo $this->Html->script('https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min.js');

JS for exporting CSV:

var table = $('#commissions-table').DataTable({
  dom: 'Blrtip',
  buttons: [
    {
      extend: 'csv',
      text: 'Export to CSV',
      filename: 'payout',
      customize: function (csv) {
        try {
          let formattedCsvAsString = ''
          const plainCsv = []

          // table doesn't seem to exist
          if (!table) {
            throw "Table can't be found."
          }

          // columns that we can remove
          const columnsToAdd = getCommissionCsvExportRowFormats(ROLE_ID)

          // get headers and table rows
          let tableHeaders = []

          // remove headers for certain users
          for (const [headerIndex, header] of table.buttons.exportData().header.entries()) {
            if (ROLE_ID != 2) {
              if (header == 'Revenue' || header == 'Profit') {
                continue
              }
            }

            tableHeaders.push(header)
          }

          // table data doesn't exist
          if (!table.rows() || !table.rows().data().toArray()) {
            throw "No data found in table."
          }

          const tableRows = table.rows().data().toArray()

          plainCsv.push(tableHeaders)

          // add user rows
          for (const [rowIndex, row] of tableRows.entries()) {
            let rowToAdd = {
              username: row.username
            }

            for (const [rowColumnIndex, column] of columnsToAdd.entries()) {
              if (row[column.key] == null || column.is_disabled) {
                continue
              }

              rowToAdd[column.key] = getFormattedValue(row[column.key], column.type)
            }

            plainCsv.push(Object.values(rowToAdd).map(String))

            if (!row.affiliates) {
              continue
            }

            // add affiliate rows to CSV
            for (const [childRowIndex, childRow] of row.affiliates.entries()) {
              let childRowToAdd = {
                affiliate: childRow.affiliate
              }

              for (const [childColumnIndex, childColumn] of columnsToAdd.entries()) {
                if (childRow[childColumn.key] == null || childColumn.is_disabled) {
                  continue
                }

                childRowToAdd[childColumn.key] = getFormattedValue(childRow[childColumn.key], childColumn.type)
              }

              plainCsv.push(Object.values(childRowToAdd).map(String))
            }
          }

          // format the formatted value
          for (const [index, csvRow] of plainCsv.entries()) {
            const formatted = csvRow.map(row => `"${row}"`)
            formattedCsvAsString += `${formatted.join(',')}\r\n`
          }

          if (!formattedCsvAsString) {
            throw "No data set."
          }

          // return csv
          return formattedCsvAsString
        } catch (err) {
          if (err.message) {
            console.warn(`Unable to convert CSV (${err.message})`)
          } else if (typeof err === 'string') {
            console.warn(`Unable to convert CSV (${err})`)
          }
        }
      }
    }
  ]
}

Here's what the optional hyphen looks like, how can I resolve this?

[AllanJard]

Hi,

Apologies if the issue template text wasn't clear. For support requests could you ask them in the DataTables forums in future please?

Also, please link to a test case showing the error.

Thank you.

[sts-ryan-holton]

@AllanJard No worries, also now asked https://datatables.net/forums/discussion/74235 so others can see