Twitter: raymondcamden


Address: Lafayette, LA, USA

Dynamically Creating CSV Files on the Client

04-03-2014 8,629 views JavaScript, HTML5 26 Comments

A reader posed an interesting question on one of my ColdFusion posts recently. The post (Creating an Excel File) demonstrated how to take form data on the front end and create an Excel file via ColdFusion when the form is submitted. The user wanted to know if this could be done entirely client-side.

The latest versions of Office use an XML based file format contained within a zip file. There is a good JS library for working with zips and as it turns out - there is a JS library for XLSX as well: https://github.com/stephen-hardy/xlsx.js. While this would possibly work, I thought there might be a simpler way using CSV, or comma-separated value files.

About two years ago I wrote a piece on exporting data from IndexedDB. It used string data and a data url to create a link to a virtual representation of the data. Combining this with the download attribute of the anchor tag, it is possible to push a download of a fake file to the user.

Therefore - all we need to do is create a CSV string. I built a simple, but ugly demo, that consists of a few rows of text fields.

The user can then enter values into the columns and get a CSV file by clicking the button. Let's look at the JavaScript code.

$(document).ready(function() {
	var $link = $("#dataLink");
	var $nameFields = $(".nameField");
	var $cookieFields = $(".cookieField");
	
	$("#downloadLink").on("click", function(e) {
		var csv = "";
		//we should have the same amount of name/cookie fields
		for(var i=0; i<$nameFields.length; i++) {
			var name = $nameFields.eq(i).val();
			var cookies = $cookieFields.eq(i).val();
			if(name !== '' && cookies !== '') csv += name + "," + cookies + "\n";	
		}
		console.log(csv);
		
		$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();
	});
});

The code begins with the click handler for the download button. All I have to do is generate my CSV string by looping over the rows of fields. To be fair, my CSV handling could be a bit nicer. A name can include a comma so I should probably wrap the value in quotes, but I think you get the idea.

Once we have the CSV, we then use the same method I used on the IndexedDB post and force the download. On my system, this creates a file that opens in Excel just fine.

It also worked in OpenOffice once I told it to use commas. Want to try it yourself? Hit the demo link below.

Edit: Big thanks to @Laurence below who figured out that I didn't need a particular hack to get my click event working for downloads. That cut out about 50% of the code I had before. Thanks Laurence!

26 Comments

  • Laurence #
    Commented on 04-03-2014 at 11:24 AM
    I think you can just chain a click() event to your link attribute setting.
    $link.attr("href",'data:Application/octet-stream,'+encodeURIComponent(csv)).click();
    http://jsfiddle.net/L54tm/
  • Laurence #
    Commented on 04-03-2014 at 12:04 PM
    Never mind. I just realized that I copied document.ready into the fiddle twice, but the second one was off screen so didn't see it.
  • Commented on 04-03-2014 at 12:46 PM
    Nice work Ray. Can definitely find some use of this snippet of code you created. Not sure just when yet, but I am sure I can think of something to make use of this code.
  • Commented on 04-03-2014 at 1:26 PM
    @Laurence: So are you saying there IS a better way for my hack or there isn't?
  • Laurence #
    Commented on 04-03-2014 at 1:42 PM
    I thought just adding .click() to the end of your $link call was working but then realized I had the call $(document).ready() twice and the second instance still referenced your fakeClick() function, and so I what I though was working was not.

    So I played around with the code a bit more and if you change to the following, it seems to work (at least in Chrome and FF)

    $link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();

    http://jsfiddle.net/L54tm/8/
  • Commented on 04-03-2014 at 1:49 PM
    Damn tootin - yeah - that worked. Will update the post now.

    Then I'll delete your comments, block anyone with your name, take credit, sell to Facebook, become rich, AND BUY SHARKS WITH LASER BEAMS!
  • Laurence #
    Commented on 04-03-2014 at 2:10 PM
    This is not the comment you are looking for ;-) Call off the sharks. You're still referencing fakeClick() in the code above, the demo looks correct. Thanks Ray!
  • Commented on 04-03-2014 at 2:37 PM
    Oops, fixed for reals this time.
  • matharoo #
    Commented on 04-05-2014 at 12:01 AM
    thats a sleek solution for csv files via jquery.. small and powerful.. thanks for sharing! :)
  • Commented on 04-06-2014 at 9:15 PM
    Get some pet dolphins if you need to call off the sharks. :)
  • Gaurav Mishra #
    Commented on 08-11-2014 at 12:31 AM
    Not working in IE
  • Commented on 08-11-2014 at 5:44 AM
    What version of IE? When you open up IE's dev tools, what error do you see? If no error, use debugging and figure out where it is failing.
  • ahsan #
    Commented on 09-10-2014 at 7:32 AM
    what if i want to run that code on android device
  • Commented on 09-10-2014 at 9:10 AM
    Did you try it?
  • Mohsen #
    Commented on 10-06-2014 at 12:47 AM
    Can we use this code in phonegap?
  • Commented on 10-06-2014 at 5:30 AM
    Yes.
  • Mohsen #
    Commented on 10-08-2014 at 12:32 PM
    This code work well in browsers, but in phonegap nothing happended. I used :

    <a href=# class="ui-btn" id="save"> Save to a text file </a>
    <a href="" id="dataLink" download="data.csv"></a>
    in index.html file of www folder, and:
    $("#save").on ("tap", function (event)
    {
    var $link = $("#dataLink");
    var csv = "";
          
    db.transaction (function (transaction)
    {
    var sql = "SELECT * FROM datastorage";
    transaction.executeSql (sql, undefined,
       function (transaction, result)
       {
       if (result.rows.length)
       {
          for (var i = 0; i < result.rows.length; i++)
          {
          var row = result.rows.item (i);
          var id = row.id;
          var data = row.data;
          var book = row.book;
          var page = row.page;
                      
       csv += id + "," + data + "," + book + "," + page + "\n";   
          }
       console.log(csv);
       $link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();
          }
       }, error);
    });
    });
    in a .js file.
  • Commented on 10-08-2014 at 12:57 PM
    Did you see anything when you debugged? Use Remote Debugging.
  • sarika #
    Commented on 10-13-2014 at 1:18 AM
    if this workes in mobile app?
  • Commented on 10-13-2014 at 5:26 AM
    Yes.
  • sarika #
    Commented on 10-13-2014 at 7:23 AM
    As i tried in my App ,its working Great on Browser But not downloading any file in App.
  • Commented on 10-13-2014 at 9:30 AM
    When you check with remote debugging, do you see anything in the console?
  • Commented on 10-13-2014 at 9:40 AM
    Ok, so I tested in both Android/iOS, and it doesn't work. Of course, if you use Cordova/PhoneGap you could easily save it.
  • Mohsen #
    Commented on 10-14-2014 at 4:17 AM
    Excuse me for delay,
    I tested my app (created by phonegap) in an android 4.2.2 device. Chrome Remote Debugging is used with an Android 4.4 device. I should test the app again with this device.
    Thank you a lot,
    Mohsen
  • Commented on 10-14-2014 at 5:08 AM
    You don't really need to - it fails. :) But in PhoneGap I'd just use the FileSystem API.
  • Mohsen #
    Commented on 10-15-2014 at 3:12 PM
    Hello,

    I test the app with File plugin for phonegap. It works well.

    code:
    $("#save").on ("tap", function (event)
    {
    /*var $link = $("#dataLink");*/
    var csv = "";

    db.transaction (function (transaction)
    {
    var sql = "SELECT * FROM datastorage";
    transaction.executeSql (sql, undefined,
    function (transaction, result)
    {
    if (result.rows.length)
    {
    for (var i = 0; i < result.rows.length; i++)
    {
    var row = result.rows.item (i);
    var id = row.id;
    var data = row.data;
    var book = row.book;
    var page = row.page;
                
    csv += id + "," + data + "," + book + "," + page + "\n";   
    }
    console.log(csv);
    /*$link.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csv))[0].click();*/
       
    // request the persistent file system
       window.requestFileSystem(LocalFileSystem.PERSISTENT, 0, gotFS, fail);
       function gotFS(fileSystem) {
       console.log(fileSystem.name);
       fileSystem.root.getFile("data.csv", {create: true, exclusive: false, append: true}, gotFileEntry, fail);
       }
                
       function gotFileEntry(fileEntry) {
       fileEntry.createWriter(gotFileWriter, fail);
       }
             
       function gotFileWriter(writer) {
       writer.onwriteend = function(evt) {
       console.log("write success");
       };
       writer.seek(writer.length);
       writer.write(csv);
       }

       function fail(error) {
       console.log(error.code);
       alert (error.code);
       }
                
       }
    }, error);
    });
    });

    Thank you,
    Mohsen

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty