Getting Data In

Export to csv on click of button

riya1
Engager

Hi everyone. I am just new to splunk and i am trying to create a function where I can export a table results to a csv file using a javascript. can someone tell me what can i do?

Any help will be much appreciated!!

1 Solution

vnravikumar
Champion

Hi

Check this sample

<dashboard script="multi.js">
  <label>csv</label>
  <row>
    <panel>
      <html>
        <div id="tableplain"></div>
      </html>
    </panel>
  </row>
  <row>
    <html>
      <button id="export">Export</button>
    </html>
  </row>
</dashboard>

js:

require([
    "jquery",
    "splunkjs/mvc/searchmanager",
    "splunkjs/mvc/tableview",
    "splunkjs/mvc/simplexml/ready!"
], function ($, SearchManager,TableView) {
    $(document).ready(function()
 {
    var search1 = new SearchManager({
        id: "search1",
        preview: true,
        cache: true,
        search: "index=_internal | stats count by sourcetype, source, host" 
    });

    var myplaintable = new TableView({
        managerid: "search1",
        el: $("#tableplain")
    }).render();

    $("#export").click(function(){
        var myResults = search1.data("results");
        myResults.on("data", function () {
             var data = myResults.collection().toJSON();
             console.log(data);
             DownloadJSON2CSV(data);
         });
      });


      function DownloadJSON2CSV(objArray)
      {
        var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
        var str = '';
        var headers = new Array();
        for (var i = 0; i < array.length; i++) {
            var line = '';
            var data = array[i];
            for (var index in data) {
                headers.push(index);
                if (line != '') {
                    line += ','
                }
                line += '"' + array[i][index] + '"';
                console.log('line: ' + line);
            }
            str += line + ((array.length>1) ? '\r\n' : '');
            line = '';
        }

        headers = getHeaders(headers);
        str = headers + '\r\n' + str;
        console.log('final : ' + str);
        var hiddenElement = document.createElement('a');
        hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(str);
        hiddenElement.target = '_blank';
        hiddenElement.download ='download.csv';
        hiddenElement.click();
    }

    function getHeaders(a) {
        var temp = {};
        for (var i = 0; i < a.length; i++)
            temp[a[i]] = true;
        var r = [];
        for (var k in temp)
            r.push(k);
        return r;
    }

});
});

alt text

View solution in original post

gaurav_maniar
Builder

Hi Riya,

Check the below example without use of JS file,

<form>
  <label>Download CSV file</label>
  <row>
    <panel>
      <html depends="$export_button$">
       <a class="btn btn-primary" role="button" href="/api/search/jobs/$export_sid$/results?isDownload=true&amp;timeFormat=%25FT%25T.%25Q%25%3Az&amp;maxLines=0&amp;count=0&amp;filename=$filename_token$&amp;outputMode=csv">Download CSV</a>
      </html>
      <table>
        <search>
          <query>index=_internal | head 1000  | stats count by sourcetype, source, host</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
          <done>
            <set token="export_sid">$job.sid$</set>
            <set button="export_button">1</set>
          </done>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="link.visible">0</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

It will display the Download CSV button, once query execution is completed.
At line 6 $filename_token$ - you can set dynamic filename name or replace it with static filename.

Please upvote and accept the answer if it helps.

vnravikumar
Champion

Hi

Check this sample

<dashboard script="multi.js">
  <label>csv</label>
  <row>
    <panel>
      <html>
        <div id="tableplain"></div>
      </html>
    </panel>
  </row>
  <row>
    <html>
      <button id="export">Export</button>
    </html>
  </row>
</dashboard>

js:

require([
    "jquery",
    "splunkjs/mvc/searchmanager",
    "splunkjs/mvc/tableview",
    "splunkjs/mvc/simplexml/ready!"
], function ($, SearchManager,TableView) {
    $(document).ready(function()
 {
    var search1 = new SearchManager({
        id: "search1",
        preview: true,
        cache: true,
        search: "index=_internal | stats count by sourcetype, source, host" 
    });

    var myplaintable = new TableView({
        managerid: "search1",
        el: $("#tableplain")
    }).render();

    $("#export").click(function(){
        var myResults = search1.data("results");
        myResults.on("data", function () {
             var data = myResults.collection().toJSON();
             console.log(data);
             DownloadJSON2CSV(data);
         });
      });


      function DownloadJSON2CSV(objArray)
      {
        var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
        var str = '';
        var headers = new Array();
        for (var i = 0; i < array.length; i++) {
            var line = '';
            var data = array[i];
            for (var index in data) {
                headers.push(index);
                if (line != '') {
                    line += ','
                }
                line += '"' + array[i][index] + '"';
                console.log('line: ' + line);
            }
            str += line + ((array.length>1) ? '\r\n' : '');
            line = '';
        }

        headers = getHeaders(headers);
        str = headers + '\r\n' + str;
        console.log('final : ' + str);
        var hiddenElement = document.createElement('a');
        hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(str);
        hiddenElement.target = '_blank';
        hiddenElement.download ='download.csv';
        hiddenElement.click();
    }

    function getHeaders(a) {
        var temp = {};
        for (var i = 0; i < a.length; i++)
            temp[a[i]] = true;
        var r = [];
        for (var k in temp)
            r.push(k);
        return r;
    }

});
});

alt text

View solution in original post

ThuHuongLE1
Observer

Hi @vnravikumar 
Thanks for great export function. I have a bug, my search1 is listen to a token so everything the token has updated, it generate another export. 
token.set("original","new value")
Thanks in advance

0 Karma

shariinPH
Contributor

the code works however if the data on the table is not yet populated, you will get infinite number of export file until the data are completed. that's the time that the export file will stop and will give a final export file.

0 Karma

vnravikumar
Champion

Hi

Try this, the Export button will get enable after the data loaded completely in the table.

<dashboard script="multi.js">
  <label>csv</label>
  <row>
    <panel>
      <html>
         <div id="tableplain"/>
       </html>
    </panel>
  </row>
  <row>
    <html>
       <button id="export" disabled="true">Export</button>
     </html>
  </row>
</dashboard>

js:

require([
    "jquery",
    'splunkjs/mvc',
    "splunkjs/mvc/searchmanager",
    "splunkjs/mvc/tableview",
    "splunkjs/mvc/simplexml/ready!"
], function ($,mvc, SearchManager,TableView) {
    $(document).ready(function()
 {
    var tokens = mvc.Components.get("default");

    var search1 = new SearchManager({
        id: "search1",
        preview: true,
        cache: true,
        search: "index=_internal | stats count by sourcetype, source, host" 
    });

    search1.on('search:done', function() {
        $('#export').prop('disabled', false);
     });

    var myplaintable = new TableView({
        managerid: "search1",
        el: $("#tableplain")
    }).render();

    $("#export").click(function(){
        var myResults = search1.data("results");
        myResults.on("data", function () {
             var data = myResults.collection().toJSON();
             console.log(data);
             DownloadJSON2CSV(data);
         });

      });


      function DownloadJSON2CSV(objArray)
      {
        var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
        var str = '';
        var headers = new Array();
        for (var i = 0; i < array.length; i++) {
            var line = '';
            var data = array[i];
            for (var index in data) {
                headers.push(index);
                if (line != '') {
                    line += ','
                }
                line += '"' + array[i][index] + '"';
                console.log('line: ' + line);
            }
            str += line + ((array.length>1) ? '\r\n' : '');
            line = '';
        }

        headers = getHeaders(headers);
        str = headers + '\r\n' + str;
        console.log('final : ' + str);
        var hiddenElement = document.createElement('a');
        hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(str);
        hiddenElement.target = '_blank';
        hiddenElement.download ='download.csv';
        hiddenElement.click();
    }

    function getHeaders(a) {
        var temp = {};
        for (var i = 0; i < a.length; i++)
            temp[a[i]] = true;
        var r = [];
        for (var k in temp)
            r.push(k);
        return r;
    }

});
});

afranciiine
Engager

This worked! Thanks!

0 Karma

shariinPH
Contributor

Hello Vnravikumar, thanks for this.. hmm, do you happen to encounter to export without max lines? becausse this one limits to 100 results only

vnravikumar
Champion

Hi

replace this line in js

var myResults = search1.data("results",{count: 0, output_mode: 'json_rows'});

manikanthkoti
Loves-to-Learn Everything

Hi @vnravikumar 

Thank you for giving this code.

We are also working on this same future But when you click on export button it is giving more number of csv files instead of 1 (more than 5)

I tried the same code in search done as well .

Can you please help us to resolve this issue .

Thanks & Regards,

Manikanth

0 Karma

shariinPH
Contributor

thanks for all the help for the csv.. is there a way to implement this also in export xls?

gcusello
SplunkTrust
SplunkTrust

Hi riya1,
if you are asking to export a table in a csv from the search form, see in on the top right part of the screen, under the time picker and near the print button: there's a button to export search results.
If instead you are in a dashboard panel in the bottomof the panel, there's a button with a magnifying glass that does the same thing.

Bye.
Giuseppe

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!