AJAX request to database - download .csv file

Question

I have a button which calls an exportCSV function. This function preforms a select statement against my postgres database and returns the results via AJAX request.

How can I get the contents of the response served back through the browser to the client as a .csv file? Currently the code only serves a response back in the browsers console. Any advice would be greatly appreciated. Once the php kicks off there is a smarthandler class which takes care of the database interaction.

exportCSV function

exportCSV: function () {
    var me = this;
    var message = 'Are you sure you want to generate the CSV?';
    var icon = Ext.Msg.QUESTION;

    Ext.Msg.show({
        title: 'Confirm Execution',
        message: message,
        buttons: Ext.Msg.YESNO,
        icon: icon,
        fn: function(btn) {
            if(btn === 'yes') {
                // TODO: Probably should handle an error here.
                Ext.Ajax.request({
                    url: 'data.php',
                    async: true,
                    params: {
                        action: 'export-csv',
                        id: me.currentRecord.data.id,
                        type: 'Carrier'
                    },
                    success: function(response,opts) {
                        var data = Ext.decode(response.responseText);
                        var csvContent = "data:text/csv;charset=utf-8,";
                        data.forEach(function(infoArray, index){
                            dataString = infoArray.join(",");
                            csvContent += index < data.length ? dataString+ "\n" : dataString;
                        });
                        var encodedUri = encodeURI(csvContent);
                        var link = document.createElement("a");
                        link.setAttribute("href", encodedUri);
                        link.setAttribute("download", "carrier_profile.csv");
                        document.body.appendChild(link); // Required for FF

                        link.click(); // This will download the data file named "carrier_profile.csv".
                    }
                });
            }
        }

    });
}

PHP

else if($action == 'export-csv') {
// Create an array of filters.
$parsedFilters = array();
// TODO: Sanitize the properties better.
if(isset($_REQUEST['filter'])) {
    $filters = json_decode($_REQUEST['filter']);
    foreach($filters as $record)
        array_push($parsedFilters,array($record->property => $record->value));
}

// Convert a request for a specific ID into a filter.
if(isset($_REQUEST['id']))
    array_push($parsedFilters,array('id' => $_REQUEST['id']));

$objectHandler = new SmarterHandler($typeDefinition);
$records = $objectHandler->read($type,$parsedFilters);

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=file.csv');

if(count($records) > 0) {
    $record = $records[0];
    $headings = array();
    foreach($record as $key => $value) {
        if(is_scalar($value)) {
            $headings[] = $key;
            echo $key.",";
        }
    }

    echo "\r\n";

    foreach($records as $record) {
        foreach($headings as $key) {
            echo $record[$key].",";
        }

        echo "\r\n";
    }
}

exit();

}

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

I think what you're looking for can be accomplished by following the steps in the accepted answer for this question.

Assuming you have your data formatted correctly (including data:text/csv;charset=utf-8 before the csv data), to download it you can make a hidden link and emulate a click

var encodedUri = encodeURI(csvContent);
var link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download", "my_data.csv");
document.body.appendChild(link); // Required for FF

link.click(); // This will download the data file named "my_data.csv".

edited May 23 '17 at 10:30

Community

1
1

answered Dec 21 '16 at 21:23

T Patrick

255
4
13

Thanks for the response Patrick. This works like a charm. However - how do i populate data with my SQL query to postgresql? It works perfectly using the hardcoded values provided in the example var data = [["name1", "city1", "some other info"], ["name2", "city2", "more info"]]; – Jukebox Dec 21 '16 at 21:37
@Jukebox I'm not sure I understand your question...are you having a problem running the SQL query on your database? Or a problem parsing the response of the query? – T Patrick Dec 22 '16 at 14:41
@T Patrick ... I run the SQL query fine and I can see the response in the browser inspect window. In the example you had provided the "data" variable which populates the csv is set statically - is there anyway to set the "data" variable to the information coming back from my SQL query – Jukebox Jan 01 '17 at 12:33
@Jukebox From your example, you should be able to get your response from the `success` callback, for example: `Ext.Ajax.request({ url: 'data.php', async: true, params: { action: 'export-csv', id: me.currentRecord.data.id, type: 'Carrier' }, success: function(d) { var data = d; // Then proceed with csv code } });` – T Patrick Jan 03 '17 at 15:21
@T Patrick I've added the success function however now when I execute it via my GUI it returns the following error - do you have any ideas? "Ext.JSON.decode(): You're trying to decode an invalid JSON String:" followed by a comma separated list of values that my SQL returned – Jukebox Jan 03 '17 at 20:38

AJAX request to database - download .csv file

1 Answers1