CSV export with only one header line, when multiple are present (docs?)

kc5151

Hey there.

Since this is a general question, I'm not including an example.

I found https://datatables.net/extensions/buttons/examples/html5/complex-header.html

This site is showing how multiple headers lines are being rendered in (CSV) exports. What I want to do is omitting my second header line from the export altogether.

As far as docs are concerned, I only found:

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

I assume I should be able to install a callback for "header", but I cannot find any documentation (googling it does turn some ancient stuff up, but I'd rather work with the current (>= 2) Datatables API.

Thanks a bunch in advance.

rf1234

Just take a look at the export options that will have already seen under the second link above.

Here is an abbreviated example from my own coding:

exportOptions: {
    modifier: { selected: null }, //make sure all records show up
    format: {            
        header: function ( data, column ) {
            //replace html tags with one space
            return data.replace(/<[^>]*>/g, ' ');
        }
    }
}

I replace html tags in the header with something else. Just take a look at the "data" parameter in your debugger. You'll probably be able to manipulate that.

Another option would be to use "customize". I use both options. If you want a full button definition from my own coding, just let me know. I can post it here if you like.

kc5151

Sorry for being daft, but I don't quite get your solution. I have two header lines in my datatable. I want to omit the second header line altogether. I don't see how doing a regexp replace on one of those lines (or both?) would help. Also, I cannot seem to find the docs for the header callback, so maybe this is a site bug?

Thanks again for your time

rf1234

I don't see how doing a regexp replace on one of those lines (or both?) would help.

Of course it doesn't help with that ... It is just a code example. As I said: Just take a look at the "data" parameter and manipulate it. I guess you will need to experiment somewhat.

Also, I cannot seem find the docs for the header callback, so maybe this is a site bug?

I don't know that. I am just a user like you who figured out how to do what I needed to do. I didn't find any specific documentation about the header callback either.

kc5151

You mentioned "customize" before. Are there any docs available for that? Thank you so much .

rf1234

I am just checking whether I can get it done myself. Experimenting a little. Will get back to you in a moment.

First thing I did is to return space from export options. that completely empties the header line. It also deletes the formatting (I had a colored background; it is Excel). All I need to figure out now is how to delete that line completely. Maybe I can get that done with "customize". Not sure.

rf1234

I just found out that I never used "customize" for csv. My users want Excel :-).

The Excel docs say the following about "customize":

I use it simply like this because I don't need the other parms. I do all kinds of manipulations of the xlsx file there.

customize: function( xlsx ) {
...
},

Looks like it is the same for CSV.

You could do something like this, I guess.

customize: function( csv ) {
   return csv.substr(200);
},

200 being the header length - or whatever it really is in your case. It's all under the link you posted above.
https://datatables.net/reference/button/csvHtml5

kc5151

Thank you, I'll look into it next week. It really stumps me though that something that should be easy requires so much diving into . Maybe a suggestion for the "headers" api option: instead of "true"/"false" also accept an array with header lines that should be included. Like "[0]" for "first header line only". @allan

rf1234

Well, manipulations of Excel exports etc. are complex. I am comparing this with what banks in this country offer: The poorest export you can imagine: Numbers that are formatted like text and cannot be used for calculations. I have never seen anything better from any bank. Apparently it is really hard to manipulate all of that stuff - so they don't do that for you. But I do it for my users using "customize" and that is really, really cumbersome.

Just tested this - worked

All you would need to do is determine the length of your header - and cut it off.

//custom button for cashflow csv generation
$.fn.dataTable.ext.buttons.csvCashFlow = {
    extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
    filename: function () { return cfFilename; },
    customize: function( csv ) {
       return csv.substr(200);
    }
};

kc5151

I think I get it, but I'm kind of reluctant to install a callback for all possible export formats. Modifying the raw export data seems very messy. I currently have exports for copy+paste/csv/excel/pdf and this seems very cumbersome for what I actually want to achieve.

rf1234

Yes it is cumbersome! That's why most people don't do it.

Here is a really complex example of an Excel export which makes sure that my users have nicely formatted texts and numbers that allow immediate calculations without the need to reformat everything manually.
https://datatables.net/forums/discussion/comment/167672/#Comment_167672

kc5151

Well, your use case is way more complex than mine. I just want to omit the second header line I have in my table, and in my opinion this should not require modifying the raw export data . Maybe allan has time to take a look, I highlighted him before and won't do so again .

rf1234

Built something that cuts out the line specified as "rowToDelete". You could put this into a little helper function and pass "rowToDelete" as a parameter..

This one deletes your 2nd header line:

//custom button for cashflow csv generation
$.fn.dataTable.ext.buttons.csvCashFlow = {
    extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
    filename: function () { return cfFilename; },
    customize: function( csv ) {
        var rowToDelete = 1; //row number to delete starting with 0
        var matchArr = [...csv.matchAll(/\r\n/g)];
        if ( rowToDelete >= matchArr.length ) {
            return csv;
        }
        for ( var ix=0; ix < matchArr.length; ix++ ) {
            if ( ix === rowToDelete ) {
                if ( ix === 0 ) {
                    csv = csv.substr(matchArr[ix].index + 3);
                } else {
                    csv = csv.substr(0, matchArr[ix-1].index + 4) + csv.substr(matchArr[ix].index + 4);
                }
                return csv;
            } 
        }
    }, ....

allan

Hi,

This example might help. Basically you use the customizeData callback to tweak the data structure that will be used to build the exported file (in this case removing a single row from the header).

At some point I'll add a headerRowSelector (or something like that) which I think will be useful, but haven't yet got around to doing!

Allan

kc5151

Thank you so much Allan, did not find that one