How can I create a CSV file from a web form?

Can someone please tell me how I would create a PHP page with a form on it, and rather than having it email the form contents, it creates a CSV file (which Excel can use). Every time someone sumbmits a new entry on the form, I would like this to create a new row in the Excel spreadsheet. Is this possible?

Cheers…John

yup. Say you have three rows on the sheet: data1, data2 and data3. You can use fopen() to open the file, add a new row and add the data.

Set $del to the CSV delimeter ("\ " for tab, “,” for column), and add all of the data (in the correct CSV order) to the array $data, like below:


$del = "\	";
$data[1] = $_POST['data1'];
$data[2] = $_POST['data2'];
$data[3] = $_POST['data3'];
$file = fopen("data.csv", "a");
$data = "\\r\
".implode($del, $data);
fwrite($file, $data);
fclose($file);
?>

Hi mate,

Ive got 5 form fields named DistName, DistNumber, Period10, Period11, Period12 (see test form below), so I guess I’d need 5 variables?

http://www.ainewmedia.co.uk/form.php

Could you tell me how to integrate your script this into the form? I’ve only ever used form to send emails to excuse my ignorance! What do I need to put in the ‘action’ attribute of the form tag (if anything?)

Also, do I need to create a blank CSV or does your script create it for me?

Cheers…John

The file is automatically created.

Set the action to “handle.php”, or whatever file name you chose. Create that file, and in it put this PHP code:

<?
if(isset($_POST['DistName'])){
    $del = "\	";
    $data[1] = $_POST['DistName'];
    $data[2] = $_POST['DistNumber'];
    $data[3] = $_POST['Period10'];
    $data[4] = $_POST['Period11'];
    $data[5] = $_POST['Period12'];
    $file = fopen("data.csv", "a");
    $data = "\\r\
".implode($del, $data);
    fwrite($file, $data);
    fclose($file);
    echo "The data has been added successfully";
}else{
    header("location: form.php");
}
?>

Like any text format, you will have to ensure that there will be no characters that are intended to be part of a value, that a parser might misinterpret as a delimiter or other special character. In this case, values in CSV files are usually quoted, which means you will need to escape quotes in values.

By putting the values in quotes, Excel will know that a comma in a value, is not a delimiter. To escape quotes use the dreaded addslashes function. This is really the only appropriate use for that function that I know of.

and how to make it password protected?

file_put_contents(‘data.csv’, $data, FILE_APPEND) is a good way to append to a file. Again, it will be created if not already there.

rashidr - how are you downloading the file to get it into excel? If you don’t mind grabbing it through FTP the easiest way to stop the public seeing it would be to store it outside of your web servers document root.
i.e don’t store it anywhere under public_html

Alternatively an ad-hoc protection system might look something like this. The file is still stored above the doc root, but this script can be called by your browser to downoad it.
Change the file location to suit


<?php
$data_file = '/home/your_account_user_name/data.csv';

if(!file_exists($data_file)) {
  header("HTTP/1.1 404 Not Found");
  exit("No file");
}


if(!isset($_GET['password']) OR $_GET['password'] != 'abc') {
  header("HTTP/1.1 401 Unauthorized");
  exit("Bugger off you");
}

header("Content-Disposition: attachment;filename=data.csv");
header("Content-Type: text/csv");

echo file_get_contents($data_file);
?>

Save that, then just navigate to the file in your browser and add ?password=abc (or whatever you change it to) to the URL.

  1. Actually, i want to make excel file password protective.
  2. How to add column name in excel file.

Does this make any difference ?

header("Content-Disposition: attachment;filename=data.csv");
header("Content-Type: text/csv");

if we replace with:

header("Content-Type: text/csv");
header("Content-Disposition: attachment;filename=data.csv");

No difference