Creating a CSV file with PHP
Dec 31, 2009 PHP Coding, Technology, Web Development
While working on several coding projects I find myself needing to export data in CSV format. All this basically means is a text file with many lines of text separated by commas(,) which can then be opened up in a spreadsheet application or imported into another program. So for an example you have the following saved in a file called export.csv:
first value, second value, third value fourth value, fifth value, sixth value
Every value before a comma is a new cell in the spreadsheet and everything after each new line is the start of a new row. Which means that the above values would produce something like the following in a spreadsheet application:
| first value | second value | third value |
| fourth value | fifth value | sixth value |
To accomplish this in PHP you can use arrays, joins and a few header functions to download the file. You could also save this output to a file instead of forcing the browser to download the content.
$row = array(); $row[] = 'first value'; $row[] = 'second value'; $row[] = 'third value'; $data .= join(',', $row)."\n"; // Join all values without any trailing commas and add a new line $row = array(); // We must clear the previous values $row[] = 'fourth value'; $row[] = 'fifth value'; $row[] = 'sixth value'; $data .= join(',', $row)."\n"; // Output the headers to download the file header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=log.csv"); header("Pragma: no-cache"); header("Expires: 0"); echo $data;
Notice the join function adding the commas between the values and then finally adding the new line with the \n before finally outputting the data as a downloadable file.
Now you may be noticing one problem with the above code. Sure the code works but what happens if there is a comma, quote or new line in one of the $row variables? If you tried to put a comma between the first and value in our first value like:
first, value, second value, third value fourth value, fifth value, sixth value
You would get:
| first | value | second value | third value |
| fourth value | fifth value | sixth value |
Obviously not the output we were looking for. To fix this you have to escape the commas and new lines with quotes like:
"first, value", second value, third value fourth value, fifth value, sixth value
Now if you are wanting to use quotes in your CSV file you have to escape those with double quotes such as:
"""first"" value", second value, third value fourth value, fifth value, sixth value
Now that you know the above information about escaping values you can use the below function to escape all of the values before joining them with commas.
function escape_csv_value($value) { $value = str_replace('"', '""', $value); // First off escape all " and make them "" if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines return '"'.$value.'"'; // If I have new lines or commas escape them } else { return $value; // If no new lines or commas just return the value } }
Usage:
$SafeValue = escape_csv_value('your value here');
Now you may also be wondering about an easy way to export data straight from your MySQL database into a CSV file for download. Using some basic PHP functions for working with databases we can easily create a new export based on the query you run. To change the export just change the query that you run.
$db = mysql_connect('localhost', 'user', 'password'); // Connect to the database $link = mysql_select_db('database name', $db); // Select the database name function escape_csv_value($value) { $value = str_replace('"', '""', $value); // First off escape all " and make them "" if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines return '"'.$value.'"'; // If I have new lines or commas escape them } else { return $value; // If no new lines or commas just return the value } } $sql = mysql_query("SELECT * FROM tablename"); // Start our query of the database $numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching if($numberFields) { // Check if we need to output anything for($i=0; $i<$numberFields; $i++) { $keys[] = mysql_field_name($sql, $i); // Create array of the names for the loop of data below $head[] = escape_csv_value(mysql_field_name($sql, $i)); // Create and escape the headers for each column, this is the field name in the database } $headers = join(',', $head)."\n"; // Make our first row in the CSV $data = ''; while($info = mysql_fetch_object($sql)) { foreach($keys as $fieldName) { // Loop through the array of headers as we fetch the data $row[] = escape_csv_value($info->$fieldName); } // End loop $data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row $row = ''; // Clear the contents of the $row variable to start a new row } // Start our output of the CSV header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=log.csv"); header("Pragma: no-cache"); header("Expires: 0"); echo $headers.$data; } else { // Nothing needed to be output. Put an error message here or something. echo 'No data available for this CSV.'; }
Please note that the above code also puts in a header row of what each of the columns is based on the database table fields. You can change what the headings are in your MySQL query by using the SQL as syntax. Examples:
SELECT fname, minitial, lname FROM users
Could be changed to:
SELECT fname as 'First Name', minitial as 'Middle Initial', lname as 'Last Name' FROM users
This would produce the desired output quickly and easily with a single MySQL query.
Enjoy working with CSV files that are properly escaped.
How To: Replace BlackBerry Screen Glass Protector
Dec 14, 2009 Cell Phones, Technology, Work
At my work we have many BlackBerry Curve 8330 phones. It is amazing what nurses and home health aids can do to their phones due to every day usage. One particular employee had fallen on her phone and scratched the screen and keyboard up really good. As a result of that unfortunate event I got the lucky job of replacing the clear plastic cover that protects the screen. Below I will outline what was done.
Tools:
- T5 Torques screwdriver
- Plastic pry tool – for opening the the case without leaving marks
- Knife
- Scissors
Tags: BlackBerry, Cell Phones, Fix, How To
Garage door only goes half way up
Dec 11, 2009 Home Improvement
This is the first year I have ever had a garage to park my truck in. Just as it started to get colder my garage door started not opening all the way. You could push the button to open the door and it would stop half way up, then when you put it down and then back up again it would open all the way. As it got colder the door just would not go any further than half way up no matter how many times you pushed the button. In one case just to make it to work on time I pulled the emergency release cord to let the door work without the opener. Once my truck was out I re-attached the opener and exited out another door once the garage door was all the way down. After taking the case of the opener apart to make sure none of the gears were stripped I had thought that the tracks should be lubricated. Not knowing how old the opener was (possibly original to the house 18 years ago) I picked up some lubrication from the local Sears store. After running the door up and down several times to lubricate the entire screw everything was working as expected. Even after a night of freezing temperatures the garage door opened smoothly without any problems the morning after the lubrication was applied.
When in doubt…. lubricate!
Outlook cannot find archive.pst after auto archive disabled
Dec 4, 2009 Technology, Work
Recently we have added a new Windows Terminal Server into network and network load balanced the two servers. Shortly after allowing users to login on the new server we started having issues with Microsoft Outlook (2003) on select users saying that it could not find the archive.pst file.
The file c:\Documents and Settings\(username)\Local Settings\Application Data\Microsoft\Outlook\archive.pst could not be found.