Exporting Web Data to Spreadsheets

The biggest problem with being able to export data from a web application for use in a spreadsheet application such as Excel is that all the spreadheet applications use proprietary formats and so obtaining complete information on what your export file should contain is impossible. Also while anyone with web access will be able to use your web application you have no way of knowing which spreadsheet program that they might have available for them to use with the exported data.

The simplest solution to this problem is to export your data in a format that all the spreadsheet programs will be able to read. The most common such formats are comma separated value and tab separated value formats where each field is separated by a comma or tab and each record is separated by a new line character. Of these the comma separated value format is the more useful since a comma is a visible character whereas a tab looks no different from a space and if someone needs to edit the file as plain text for any rerason they may accidentally corrupt the file. Tabs do have the advantage over commas though in that they are far less likely to be part of the legitimate input of a field.

I suggest that commas are still the better format of the two to use as we can ensure that those commas that are a part of the content can be distingished from those which are field separators by surrounding the fields that can contain commas in double quotes. The following is readily able to be split into its separate fields by your speadsheet program.

George,""The Landing" 27 Happy St, Somewhere",06 9999 9999

Any comma not enclosed in " " is a field separator while any that are belong with the surrounding content. Any " within the content itself are also easily distinguished as being a part of the content as long as they are not immediately followed by a comma.

So how do we create a comma separated values file from our PHP? Well all we need to do is to write out two header recorda with the first identifying that we are creating a comma separated values file and the second identifying that it is to be offered for download and supplying a file name.

header('Content-type: text/comma-separated-values');
header('Content-Disposition: attachment; filename="filename.csv"');

Those are then followed by our comma separated value content with each record being terminated with a linefeed "\n".

When a PHP page containing that data is called the Save dialog box will be opened by the browser offering the opportunity to either run or save the file. If run is selected then the installed spreadsheet program will be opened and the data loaded into that with commas being used to determine where the column breaks should be and linefeeds identifying the rows. If save is selected then the file will simply be saved as a CSV file to the local computer allowing it to be opened in whichever program the person chooses at a later time.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow