DataTables, export datatable to CSV file where column values have commas

I’m using datatable in my laravel app, with 2 columns (id, address) wanted to export contents to a csv file. However some of the values in the address column have commas in them. So, when the user open the csv file the value is split into columns. For example the datatable looks like this:

id, address 1, Chicago, IL

however, when user opens csv file in excel: A, B, C id, desc 1, Chicago, IL

So the value: “Chicago, IL” gets split into 2 columns, B and C when opening in excel. I was expecting only excel columns A and B (id, address) to be populated.

Is there something that I can do to escape the comma or format the cell so that when the user opens the file there are only 2 columns populated and the address column has the value: Chicago, IL

I tried to find the solution everywhere but no luck so please this question is not duplicated Thanks in advance.

<table class="table" id="sample_1">
<thead>
<tr>
<th class="all export"> id </th>
<th class="all export">address</th>
</tr>
</thead>
 <tbody>
 @foreach ($data as $item) 
 <tr>                                  
 <td >{{ $item->id}} </td>
 <td >{{ $item->address}} </td>
 </tr>
@endforeach
</tbody>
</table>

   TableDatatablesEditable = function() {
    var e = function() {
        var e = $("#sample_1"),
            t = e.dataTable({
                dom: "Bfrtip",
                buttons: [{
                    extend: "print",

                    className: "btn dark btn-outline"
                }, {
                    extend: "pdf",
                    className: "btn green btn-outline"
                }, {
                    extend: 'csvHtml5',
               exportOptions: {
                     columns: ['.export'],
                              },
               className: "btn-outline"
                }],
                language: {
                    lengthMenu: " _MENU_ records"
                },
                columnDefs: [{
                    orderable: !0,
                    targets: [0]
                }, {
                    searchable: !0,
                    targets: [0]
                }],
                order: [
                    [1, "desc"]
                ],
                lengthMenu: [
                    [5, 10, 15, 20, -1],
                    [5, 10, 15, 20, "All"]
                ],
                pageLength: 20,
                dom: "<'row' <'col-md-12'B>><'row'<'col-md-6 col-sm-12'l><'col-md-6 col-sm-12'f>r><'table-scrollable't><'row'<'col-md-5 col-sm-12'i><'col-md-7 col-sm-12'p>>"
            });

Answer

As a workaround you can print the value in quotes inside the table. The text inside quotes won’t break into columns by Excell.

<td >"{{ $item->id}}"</td>

Advertisement

Answer