Creating CSV files

This How-To guide describes the steps required to get FormMail to append to a CSV (Comma Separated Values) file.

A CSV file is a type of database that allows you to keep a record of your form submissions. Many applications will read or import data from a CSV file. CSV files are particularly useful for spreadsheet applications such as Microsoft Excel.

You can use a CSV file on your server as a holding place before importing into a full relational database such Microsoft Access or MySQL.

Getting Started

The first step is to get FormMail working and sending you your form submissions via email. Once you've got that working, you know that your form and FormMail are configured correctly.

Later, if you only want the CSV file and don't want the email anymore, you can simply remove the "recipients" field from your form.

Alerts

Ensure that you've got alerts working from FormMail too. The easiest way to do this is to set the DEF_ALERT setting in FormMail.

You can test alerts by using your browser to open your FormMail like this:

http://www.yoursite.com/formmail.php?testalert=1

Alerts are the only way FormMail can communicate errors to you. For example, suppose it can't open your CSV file; FormMail will send you an alert to tell you that's the problem.

Create a Directory

You need to create a directory on your server to hold CSV files. (A directory is also called a "folder" - they are two names for the same thing).

You can use an existing directory, if you want, but we recommend creating a separate directory.

The directory can be outside your Web server's document root. This means that no browser can access your CSV files.

For example, you could create this directory:

/home/your-site/public_html/csvdir

which is inside your server's document root and potentially accessible from a browser, or this directory:

/home/your-site/csvdir

which is above your server's document root and not accessible from a browser.

It's important that you know the full path on your server to the directory you create. If you don't know the full path, then you need to ask your hosting provider for this information.

If you have Telnet or SSH access to your server, and your server is Unix-based, you can find out the full path by logging into your server and typing this command:

pwd

We recommend that the permission modes for the directory do not let anyone (other than yourself) from creating or removing files in the directory.

Recommended modes:

rwxr-xr-x

or, for more security:

rwxr-x--x

Create a File

Next, you create a file to store your CSV data in the directory you created above. For security reasons (and, specifically, the modes you've set for the directory above), FormMail cannot create the file for you.

The file can be called anything, but we recommend:

formdata.csv

or

formdata.txt

The file needs to be writable by your web server software (which executes FormMail for you).

These are the modes you need:

rw-rw-rw-

Note that the file must be empty. This means it must be zero length. On UNIX-like systems (including Linux), you can create an empty file using this command (using Telnet or SSH access):

cp /dev/null formdata.csv

Configuring FormMail

The basic configuration for CSV files in FormMail is one setting called $CSVDIR. You'll find it near line 290 in version 8.05.

You can read the instructions in formmail.php for information about this setting.

Simply put the full path to the directory you created above inside the quotes for this setting. Like this:

$CSVDIR = "/home/your-site/csvdir"; // directory for csv files...

Configuring Your HTML Form

The last step is to create two hidden fields in your HTML form. These fields tell FormMail where to store the CSV data and what to store.

The hidden field csvfile is the file name of the file you created above. FormMail looks for the file in the directory specified in the $CSVDIR setting, so don't try to specify a path name:

<input type="hidden" name="csvfile"
    value="formdata.csv" />

The hidden field csvcolumns tells FormMail which fields from your form you want stored. Each field becomes a column in the CSV file. Each form submission becomes a row in the CSV file.

The order of the fields you specify in csvcolumns is the order they are stored in the CSV file.

Here's an example:

<input type="hidden" name="csvcolumns"
    value="email,realname,address,phone" />

Importing CSV Files

The interpretation of a CSV file is often dependent on specific features in the program you're importing the CSV file into.

For example, Microsoft Excel doesn't like carriage returns in column values, and, more importantly, insists on interpreting values containing numbers as being numbers.

This means, for example, it's very difficult to get Excel to read in a phone number or similar value without interpreting it as some sort of number, thereby making the phone number unreadable.

OpenOffice spreadsheet has the same problem with phone numbers.

To get these spreadsheets to interpret string values containing numbers as strings, not numbers, you need to use a "string formula". When you do this, however, the CSV file can only be interpreted by the spreadsheet program.

Normally, a phone number would appear in the CSV file something like this:

"Jack Smith","555-1234","jack@somesite.co"

To get this to work when importing into a spreadsheet, you need it to look like this:

"Jack Smith",="555-1234","jack@somesite.co"

The = before the value tells the spreadsheet that the value is a formula, not a value, and the formula's result is the given string.

Just to repeat, such a CSV file is actually not valid CSV and can only be imported into programs that know how to deal with this special format.

From version 8.23 of FormMail, you can now control this formatting on a per-field basis. The csvcolumns field contains a list of field names. From version 8.23, each field name can be followed by a format specification. Like this:

<input type="hidden" name="csvcolumns"
    value="email:c,realname:c,phone:cs,comments:r" />

These format specifications are documented in Creating HTML Forms.

More Information

• The first time FormMail writes to the CSV file, it puts the names of the columns on the first line. Note: this only works if the file is actually empty. "Empty" means zero length. A file containing a single blank line is not an empty file and will prevent the column names from being written to the file.
• Once you've written to a CSV file you must not change your csvcolumns value. If you do, this will change the format of the file part way through and you'll have trouble importing or processing your CSV file. If you need to change the columns in the CSV file, you need to create a fresh CSV file to contain the new format.
• There are other configuration values you can provide to control the format of your CSV files. For more information read the FormMail settings documentation.
• $CSVSEP - specifies the separator between columns in the CSV file (default is comma).
• $CSVINTSEP - specifies the separator between values within a column. This is used for checkbox and multi-selection list boxes. (Default is semicolon).
• $CSVQUOTE - every column is quoted with this character (default is double-quote). You can set this to empty string for no quoting.
• $CSVOPEN - see the documentation for details.
• $CSVLINE - the line termination to use (see the documentation for details).