Excel recognize currency column but CSV is exporting currency column as string

anujeet

If you look at the examples in datatables website itself, you can see that excel export currency columns correctly with currency symbol. But in CSV if I pass the currency symbol, it export it as string.

Any solution to this?

Please see the below url to test

https://datatables.net/extensions/buttons/examples/initialisation/export.html

Thanks

kthorngren

Not sure I understand the question. In the example I export to CSV and the Salary column as the $ included. A CSV file is a text file and opening into Excel will result in text columns. To get Excel formatting then you need to export as Excel.

Please explain further if I'm not answering your question.

Kevin

anujeet

@kthorngren you did not understand my question.

The 'Salary column' is exported as string in CSV file version. But in the excel , it is exported as formatted currency column. Why is the 'salary column' exported as type string in CSV format?

kthorngren

The definition of CSV files are that they are plain text files.
https://en.wikipedia.org/wiki/Comma-separated_values

No matter the source of the file when opened in Excel it will read each column as text.

Kevin

anujeet

But @kthorngren , when I right click on the cell, go to 'format cell' and choose currency , then it applies correct formatting. Can't I just do this setting while export and not manually after export?

Thanks for your patience.

kthorngren

Can't I just do this setting while export and not manually after export?

To get the Excel formatting you need to use the Excel export button.

Excel is going to treat any CSV file as plain text and import as plain text. However you can import text files into Excel and set some formatting options. These steps might vary depending on Excel version but you can go to Data > Get External Data > Import Text File. Within those dialog boxes you can set some formatting options for each column. I know this is not the answer you are looking for

Is there a reason you want to use CSV over Excel exporting to import into Excel?

Kevin

anujeet

No we are using both exports CSV and Excel , but client is saying that gets different formats of the currency column by using Excel and CSV button. He wants both of them to work efficiently for currency columns

kthorngren

The CSV export would typically be used for importing into applications other than Excel. You are limited to the way Excel handles importing CSV / text files not by the way Datatables exports CSV files.

Kevin

allan

Going to jump in here to completely agree with what Kevin has said. By its very definition CSV is a plain text file which doesn't have type information.

The Excel file however does and the Buttons export makes use of that.

When you import your CSV file into Excel it is now in the Excel domain, so yes you can add formatting. If you were to export it to CSV again, then of course you would loose any formatting.

There is simply no way to make the CSV file have type information for columns. That's not a limitation of Buttons' export abilities, but rather simply how CSV as a format is designed to work.

Allan