CSV Export tables that contain commas in the cells

jLinux

I have a table that has a column that has multiple values that are separated by commas, this is a result of the jQuery x-Editable plugin, so obviously that causes issues when I try to export the table via CSV.
Screenshot: http://d.pr/i/1ee8v
I was wondering, does anyone have a way to fix that? Is there a way to have DataTables escape the commas when it exports? Or does it just have to throw the column value in quotes or something?

jLinux

delete me

jLinux

Ugh, wish I could delete my own thread.. again...

I found that the csvHtml5 export will enclose the values in quotes, as opposed to just the csv which wont.

http://datatables.net/reference/button/csvHtml5

Why would anyone use csv instead of csvHtml5? When the latter is so much better, or so it seems

jLinux

I noticed this on the excel:

This is provided as an option since the HTML5 button type requires a reasonably modern browser which may not always be available to the end user.
The order of selection, in priority order is:

excelHtml5
excelFlash

I inject my buttons manually, like so:

// Configure Export Buttons
new $.fn.dataTable.Buttons( $assets_dt, {
    buttons: [
        {
            extend: 'copy',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-copy'
        }, {
            extend: 'csvHtml5',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-csv',
            title: export_filename,
            extension: '.csv'
        }, {
            extend: 'pdf',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-pdf',
            title: export_filename,
            extension: '.pdf'
        }, {
            extend: 'print',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-print'
        }
    ]
} );

// Add the Export buttons to the toolbox
$assets_dt.buttons( 1, null ).container().appendTo('#export-assets');

Will DataTables still hide the buttons? If not, how can I detect/check for the requirements for both buttons? So that i may hide them manually. The buttons have classes which will set the width, so if one wont work, I need to hide it, and change the classes on the others so they fit the full width of the menu they are in.

stevevance

I've set the fieldBoundary option to use a double quotation mark, and DataTables does add these to enclose cells.

However it doesn't seem to be good enough for Excel or OpenRefine to read it properly – some cells are still being divided into more than one column.

My code:

{
                            extend: 'csvHtml5',
                            text: "<i class='fa fa-file-text-o'></i> Export as CSV",
                            fieldBoundary: '"',
                            exportOptions: {
                                columns: exportColumns
                            }
                        }

This results in a good and bad row like this:

"Easy permit process","427 E 42nd Pl","08/25/2015","08/25/2015","$8,000.00","Open up bricked windows on first and second floor. window on 1st floor bathroom may be glass block with vent. work to correct natural light and vent requirements. duplex down to basement and remove bedroom wall in basement to create open space. enclosed bathroom and mechanical room in basement to remain. work to address violations per owner plans.","41.817447261","-87.615434895"

"New construction","440 E 43rd St","08/25/2015","08/25/2015","$35,000.00","Collocation of 3 antennas on existing crown castle tower, installation of 3'x6' concrete pad for new charles cabinet and new fiber box mounted to new 5'-0" wide f-frame, also new comed approved meter and disconnect, locating 3 antennas, 1rru's and 1rrul on existing monopole at 30' rad center.","41.816777788","-87.614883667"

The second row has multiple columns enclosed by the fieldBoundary option of double quotation mark.

Screenshot of how Excel splits the cell into multiple columns:
https://www.dropbox.com/s/5q80sqpwuwk53zk/Screenshot%202015-10-12%2012.31.43.png?dl=0

Any idea what's happening here?
Do I need to write a function that escapes the commas?

jLinux

Thats very weird, in my example, i used csvHtml5 as well, without having to specify a field separator... have you tried NOT specifying one? Also, what browser are you uaing. I stated above that if its an old browser, DT will switch it from csvHtml5 to just csv, which would be causing this

stevevance

When I tried it without specifying a fieldBoundary then no field separator appeared. I am using Chrome (latest version, 45), and DataTables Buttons 1.0.1. When I upgraded to the latest version of Buttons then more things went wrong (no CSV download was possible).

jLinux

Well you see whats weird, is you're specifying the fieldBoundry option as a double quote, but if you look at the csvHtml5 page, it shows that as the default..

You're getting NO errors in your console? Can you show all of the DataTables relevant code?

stevevance

I made an error in all my comments. Where I wrote fieldSeparator I meant fieldBoundary!

I can do one better. I can show you the live code.

I've removed the fieldBoundary option so you can see what happens.

The versions used in that code can be seen in this CDN script source: https://cdn.datatables.net/r/bs/jszip-2.5.0,pdfmake-0.1.18,dt-1.10.8,b-1.0.1,b-flash-1.0.1,b-html5-1.0.1,b-print-1.0.1/datatables.min.js

jLinux

PM'd you a login for my site so you can see the demo.

Its very weird, im pretty confused to be honest.. lol.

Heres my (relevant) code for the page I linked you to:

new $.fn.dataTable.Buttons( $assets_dt, {
    buttons: [
        {
            text: 'Copy',
            extend: 'copy',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-copy',
            exportOptions: {
                columns: [ 0, ':visible' ]
            }
        }, {
            text: 'CSV',
            extend: 'csvHtml5',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-csv',
            title: export_filename,
            extension: '.csv',
            exportOptions: {
                columns: [ 0, ':visible' ]
            }
        }, {
            text: 'XLS',
            extend: 'excelHtml5',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-csv',
            title: export_filename,
            extension: '.xls',
            exportOptions: {
                columns: [ 0, ':visible' ]
            }
        }, {
            text: 'PDF',
            extend: 'pdf',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-pdf',
            title: export_filename,
            extension: '.pdf',
            exportOptions: {
                columns: [ 0, ':visible' ]
            }
        }
    ]
} );

// Add the Export buttons to the toolbox
$assets_dt.buttons( 0, null ).container().appendTo( '#export-assets' );

As you can see, I dont have to specify the separator or boundary for either csvHtml5 or excelHtml5

Have you tried the excelHtml5? See if that does the same thing? (Just curious if this exports it OK)

Also, I see you're using v1.0.1, I dont think thats the latest? Try to upgrade, just see if that does anything

jLinux

P.S. Very nice implementation of DataTables, it looks great

stevevance

I saw your PM. Thanks for the tips.

I think the problem with my implementation has something to do with the exportData settings I have:

This setting will export as CSV, with proper fieldBoundary ("). The fields with commas are showing properly. However, fields with double quotation marks are not being properly escaped. The first double quotation mark in the field will be escaped but not the following double quotation mark.

{
    extend: 'csvHtml5',
    text: "<i class='fa fa-file-text-o'></i> Export as CSV",
    exportOptions: {
        columns: [ 0, ':visible' ]
    }
}

The following setting fails to produce any kind of CSV:

var exportColumns = [ "permit_type:name", "address:name", "issue_date_normalized:name", "issue_date_actual:name", "estimated_cost:name", "work_description:name", "latitude:name", "longitude:name"];
{
    extend: 'csvHtml5',
    text: "<i class='fa fa-file-text-o'></i> Export as CSV",
    exportOptions: {
        columns: exportColumns
    }
}

I'll start narrowing down the problem from here.

jLinux

I see you were using columns: [ 0, ':visible' ] are you trying to export the first column and the visible columns? if so, thats the correct way, if you're trying to export only the visible columns, then change it to columns: ':visible'

And have you tried removing all of the configuration options in the button? and specifying the extend to just csv? Per the csv page..

This button type serves as a proxy that will automatically detect if the csvHtml5 or csvFlash buttons can be used based on the browser's capabilities and the libraries available on the page. The order of selection, in priority order is: csvHtml5 then csvFlash

stevevance

It's funny - even when I remove all of the configuration options for the 'csv' button, it doesn't work.

The console throws this JS error when I set the exportOptions:columns setting to a custom array of column names:

[Error] TypeError: a[e].replace is not a function. (In 'a[e].replace(d,"\\"+d)', 'a[e].replace' is undefined)

in datatables.min.js, line 300.
This problem doesn't occur when exporting as "copy to clipboard" or "print". Both of those functions use the array of column names and it works.

The exporting works if exportOptions:columns is set to columns: [ 0, ':visible' ] or columns: ':visible'.

I tested further. I changed exportOptions:columns from an array of column names to an array of column index numbers. It failed if I included columns 7 and 8, which are the columns for latitude and longitude (again, these columns will still be copied and printed).

Seeing as copying works, I decided to change to fieldBoundary: "" and fieldSeparator: "\t", just like the default settings for 'copy'. That opens the "blob" into a new window and I copied that text into Excel and Excel read it perfectly.

That working, combined with the [Error] above, means I'm beginning to think that DataTables is choking on my data (and not being able to escape the second quotation marks in the data, thus not escaping following commas).

I'm going to try a subset of the data that has no commas or quotation marks in it.

stevevance

okay, I don't think it's the commas and multiple quotation marks that are causing the problem. I think it's the latitude and longitude fields.

To test it I set them to an arbitrary string: "hey". Export as CSV worked on every Places page on my site that I tried. I changed the fields back to their source data.

Every Places page failed to be exported as CSV. The [Error] message in my previous comment seems to be trying to modify the text, so I'm making the columns data type explicit.

Nope, setting column:type: "num" and column:type: "string" had no effect. But, maybe if I reformat the field value...

Here's how I got the latitude and longitude columns to work: I used the Numeral.js library to format them:

return numeral(row.geometry.coordinates[0]).format("0.00000000");

I'm still having the problem of not all double quotation marks are being escaped, so Excel splits that field into multiple columns.