2

Browser unable to find file when attempting to download results as CSV from SQL database query.

Hello all. I have the following function in my HTML script. I would like to be able to download the results of the user-query as a CSV file. When I render the webpage and query the database, the toolbar shows up; however, when I try and download the CSV of the results my browser cannot find the file. Any pointers as to where I am going wrong would be greatly appreciated. Thank you!

function google_piechart() {
            
            var class_choice = $('#class_choice').val();
            if (class_choice == null) {
                alert('Please select a class.');
                return false;
                }
            console.log('Selected class:', class_choice);

            // get data from output of Python script
            $.get("https://bioed.bu.edu/cgi-bin/students_23/Team_H/class_cgi.py",
                {selector: 'piechart',  class_choice:class_choice},
                // need to use the Google charts pie chart function here to generate from data...
                function (res){
                    console.log('Received data:', res);
                    create_piechart(res);
                },
                "json"
            );
        }
        
        
        // define that function here, now
        function create_piechart(res, class_choice) {
            console.log('Processed data:', res, class_choice);
            global_data1 = res

            // use strict equality operator here...don't want weird results from just `==`

            if (res.length === 0)
                $('#piechart').html('There was no data for the class choice: ' + class_choice);
         

            let formatted_results = res.map(item => item.slice(0, 2));
            console.log(formatted_results);
            // adding a header
            formatted_results.unshift(["Transposon Order", "Count"]);

            // get the total count of results
            let total_count = res.reduce((sum, item) => sum + item[1], 0);
            console.log('Total count:', total_count)
    
            // return total count to the screen for the user
            $('#result_count').html('Total number of results: ' + total_count);


            // loading Google charts info
            google.charts.load('current', {'packages': ['corechart', 'table', 'gauge']});
            google.charts.setOnLoadCallback(function () {
                // format processed data for use w/ Google charts
                var chart_data = google.visualization.arrayToDataTable(formatted_results);
                console.log('Chart data:', chart_data);
                // options for the piechart
                var options = {

                    title: 'Transposon Orders by Class',
                    fill: 'transparent'
                    is3D: true,
                    colors: ['#0072B2', '#E69F00', '#009E73', '#F0E442', '#CC79A7', '#D55E00', '#56B4E9', '#0072B2', '#F0E442', '#009E73', '#D55E00', '#CC79A7']
                };

                // finally creating the pie chart
                var chart = new google.visualization.PieChart(document.getElementById('piechart'));
                chart.draw(chart_data, options);

                // create a table with the same data
                var table = new google.visualization.Table(document.getElementById('table1'));
                // execute here
                table.draw(chart_data, {
                    title: 'Transposon Orders by Class',
                    showRowNumber: false, 
                    width: '50%'
                });
        
                // convert data table to CSV format
                var csv_data = google.visualization.dataTableToCsv(chart_data);
                console.log('CSV data:', csv_data)

                // draw the toolbar
                var components = [
                {type: 'csv', datasource: csv_data}
                ];


                var toolbarContainer = document.getElementById('toolbar_div1');
                google.visualization.drawToolbar(toolbarContainer, components);
            });
        }
thecatsmilk
  • 31
  • 4
  • 1
    the toolbar will not work in that manner, see [Usage](https://developers.google.com/chart/interactive/docs/gallery/toolbar#usage) on the toolbar page --> _To use a toolbar, your visualization must get its data from a URL; you cannot pass in hand-populated DataTable or DataView objects._ -- instead, you can create the download manually, similar to this [old answer](https://stackoverflow.com/a/49306136/5090771) – WhiteHat May 01 '23 at 20:28

1 Answers1

1

I found my own solution from another thread on Stack Overflow. You can download the data being passed into the Google charts piechart with a simple function like this:

$('#Export').click(function () {
    var csvFormattedDataTable = google.visualization.dataTableToCsv(chart_data);
    var encodedUri = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvFormattedDataTable);
    this.href = encodedUri;
    this.download = 'orders_by_class.csv';
    this.target = '_';
});

Where the Export ID is an anchor.

thecatsmilk
  • 31
  • 4