DataTables: Columns with dot (.) in header not displaying correctly

Question

DataTables seems to treat dots (.) as a special character and doesn't display the columns, which have them in the header.

Is there any solution how to keep the dots and use some sort of escape character?

The error is: Requested unknown parameter 'Doc.' for row 0, column 0

My JSON DataTable initializer:

{
    "columns": [
        {
            "data": "Doc.",
            "title": "Doc."
        },
        {
            "data": "Order no.",
            "title": "Order no."
        }
    ],
    "data": [
        {
            "Doc.": "564251422",
            "Order no.": "56421"
        },
        {
            "Doc.": "546546545",
            "Order no.": "98745"
        }
    ]
}

Restriction

It's perfectly fine to use dots in header title with title. However when data option is a string, dots . are treated the special way, see documentation for more details. Solution is to avoid using dots in data attribute. – Ogilvie

Answer 1

When there is dots in data property names dataTables will look for nested data properties. Like if there was as Doc.<something> property. When you have automated scripts - as you have - the server is distributing JSON not necessarily designed for dataTables, you can "sanitize" the data before passing it to dataTables.

I took the answer from yesterday and added two functions : sanitizeData and sanitizeColumns. sanitizeData removes all dots and whitespaces from keys, sanitizeColumns remove whitespace and dots from the columns.data definitions :

$.getJSON('https://api.myjson.com/bins/4z5xd', function(json) {
    //remove whitespace and dots from keys / attribute names    
    function sanitizeData(jsonArray) {
        var newKey;
        jsonArray.forEach(function(item) {
            for (key in item) {
                newKey = key.replace(/\s/g, '').replace(/\./g, '');
                if (key != newKey) {
                    item[newKey]=item[key];
                    delete item[key];
                }     
            }    
        })    
        return jsonArray;
    }            
    //remove whitespace and dots from data : <propName> references
    function sanitizeColumns(jsonArray) {
        var dataProp;
        jsonArray.forEach(function(item) {
            dataProp = item['data'].replace(/\s/g, '').replace(/\./g, '');
            item['data'] = dataProp;
        })
        return jsonArray;
    }    
    json.data = sanitizeData(json.data);
    json.columns = sanitizeColumns(json.columns);    

    $('#example').DataTable({
       data : json.data,
       columns : json.columns
    })
});  

demo -> http://jsfiddle.net/egpxdsq7/

Donar

The problem is not with DataTables not using json[propName]. When data option is a string, dots . are treated the special way, see documentation for more details. Otherwise, great answer! – Ogilvie

@Gyrocode.com, OK - thank you for that, you have a good point - but there is dozens of col.mData etc references all over the code. Have updated the answer so it reflects your point. – Donar

Answer 2

Try this:

"columns": [
    {
        "data": "Doc.",
        "title": "Doc."
    },
    {
        "data": "Order no.",
        "title": "Order no."
    }
],
"data": [
    {
        "Doc.": "564251422",
        "Order no.": "56421"
    },
    {
        "Doc.": "546546545",
        "Order no.": "98745"
    }
]

Though this also works:

"columns": [
    {
        "data": "Doc.",
        "title": "Doc."
    },
    {
        "data": "Order no.",
        "title": "Order no."
    }
],
"data": [
    {
        "Doc": "564251422",
        "Order no": "56421"
    },
    {
        "Doc": "546546545",
        "Order no": "98745"
    }
]

Mcgee

I'm getting Requested unknown parameter 'Doc.' for row 0, column 0 – Restriction

For your suggestion I'm getting an exception Uncaught SyntaxError: Unexpected token ILLEGAL in the browser console – Restriction

I think it may be because I'm using a JSONP object, which gets treated like jacascript it may be causing the error with .. Anyway the solution from the other answer worked. – Restriction

Answer 3

I know I am late to the party, but the way I resolved this was to replace all "." with "\\." in the "columns" value in the datatable options.

Datatables will still find the value with a period, and won't crash. I found this info here: https://datatables.net/reference/option/columns.data

All I did while adding items into the column object was run this:

for(var i =0; i < columnNames.length;i++){
    //replaces all "." with "\\." which datatables ignores
    columnNames[i] = columnNames[i].replace(/\./g,'\\.');
}

//other code goes here

var datatableOptions = {
        'data': myData,
        columns: tableColumns,
    };

Gallic