1

I have a datatable with a few columns. One column is a timestamp with seconds for example 9/8/21 8:06:10 PM. When I export the data to a csv file, the seconds are truncated that is 10 is not showing up in the file. Is there any way to specify the format of the timestamp in the csv file when exporting?

 dom: '<lBfrtip>', 
        buttons: [
            { extend: 'csv' },
        ]
Pooja
  • 35
  • 5
  • I cannot recreate your problem. The CSV file contains exactly the same value as your example above: `9/8/21 8:06:10 PM`. – andrewJames Jan 21 '23 at 00:59
  • How are you actually _opening_ your CSV file? Using a text editor (because a CSV file is just a text file)? Or are you perhaps using Excel (because Excel is capable of displaying CSV files)? If you are using Excel, then select the cell and look at the raw data (in the cell editor textbox) - is the `10` (seconds) displayed? And when you look at the data in the spreadsheet cell, is the `10` (seconds) _not_ displayed? If that is the case, this has nothing to do with the `10` being "truncated". It's just how Excel is choosing to _display_ your CSV data. – andrewJames Jan 21 '23 at 01:00
  • You want a custom display for Excel? That is a completely different question. But, as you can see I am making lots of guesses here - so can you clarify your specific situation? – andrewJames Jan 21 '23 at 01:00
  • @andrewJames opening csv in excel. The timestamp looks ok when the file is in .xlsx format but issues while opened in .csv – Pooja Jan 23 '23 at 18:12
  • @andrewJames as you mentioned the raw data looks correct format but the cell depicts wrong format missing the seconds in csv – Pooja Jan 23 '23 at 18:14
  • It is not possible for data which is missing from the CSV file to be displayed by Excel, when Excel opens that same CSV file. – andrewJames Jan 23 '23 at 18:40
  • Regardless of that, if you want to open the file using Excel, then create an Excel export from DataTables, not a CSV export (or create both). – andrewJames Jan 23 '23 at 18:48
  • @andrewJames there is nothing missing on the csv file. The data is in correct format with the seconds on the CSV. – Pooja Jan 23 '23 at 19:42
  • @andrewJames I am looking for customization of the datetime format during the export on csv which is possible then why use excel. – Pooja Jan 23 '23 at 19:43
  • My apologies, but I am finding it difficult to understand your comments. I also need to repeat my very first comment back to you: **I cannot recreate your problem. The CSV file contains exactly the same value as your example above: `9/8/21 8:06:10 PM`** - so what is the problem you are trying to solve? Your question is not clear (to me). I don't understand your "why use excel" comment. Don't you _want_ to use Excel? – andrewJames Jan 23 '23 at 19:50
  • @andrewJames While exporting the CSV after hitting CSV button on the datatable, the csv file gets downloaded with .csv extension. When I double click the file, it opens up in the ms excel. The datetime in the timestamp column shows MM/DD/YYYY hh:mm whereas the timestamp format on the original datatable data is 'MM/DD/YYYY hh:mm:ss A'. When I click the cell, i see the raw data as original data, however on the cell in the sheet it looks short data. I want the data in the sheet must match original data. Does this makes sense? – Pooja Jan 23 '23 at 22:39
  • We are going round in circles, I think. You can't "format" your Excel file's datetime cell data display in a CSV file - the CSV data is just plain text. If you want to format the output for display in Excel, then export your data to Excel, not to CSV. See also [JQuery Datatable Excel export number format](https://stackoverflow.com/q/67331618/12567365) - because if the default Excel display is not what you want, then you will also need to provide your own custom Excel datetime format. – andrewJames Jan 23 '23 at 23:06

1 Answers1

2

You can try formatting function on that field when exporting. From the docs I adapted this code:

buttons: [{
      extend: 'csv',
      exportOptions: {
        format: {
          body: function(data, row, column, node) {
            // return cast to string if column #5 ? 
            return column === 5 ? ("" + data) : data;
          }
        }
      },
    ]
IT goldman
  • 14,885
  • 2
  • 14
  • 28