Remove optional hyphens from CSV export for Excel

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?

kthorngren

Since there is no hyphen in the CSV but Excel is displaying a hyphen then I would first take a look at the cell to see what format Excel is applying to the cell.

Does this happen only with the child rows?

Please provide a link to your page or a test case so we can take a look at the source data and what your CSV customize function is doing.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin