[Resolved] Export to CSV questions

skeetley

Before I get into asking for help on exporting to csv, I have a question.

In my db I have the time as hh:mm:ss. How tough will it be to get this in the export as hh?

Now if that is possible, how do I get it to do this? My csv file will have 7 columns in it. I believe th hour field is the only one that needs additional formatting.

I did some searching for this and everyone has a differnet approach. http://www.phpbuilder.com/board/showthread.php?s=&threadid=10271147 looks good, but can I make it work?

swr

Shouldn't be hard. Just use [man]explode[/man].

$array = explode(':', 'hh:mm:ss');
echo $array[0]; // echos "hh"

freebie

Here's an example of export to csv using PHP

<?php
$cols=array('RequestBrochure'=>'BROCHURE',
			'EnrollMe'=>'RESPONSE1S',
			'CallMe'=>'CALL',
			'SchoolID'=>'SCHID',
			'SchoolName'=>'SCHOOL',
			'SchoolAddress'=>'ADDRESS1',
			'SchoolCity'=>'CITY',
			'SchoolState'=>'STATE',
			'SchoolZip'=>'ZIP',
			'Salutation'=>'CONTSALUTA',
			'FirstName'=>'CONT1STNAM',
			'LastName'=>'CONTLSTNAM',
			'Email'=>'CONTEMAIL',
			'Title'=>'CONTTITLE',			
			'Phone'=>'PHONE',
			'Ext'=>'EXT',
			'Fax'=>'FAX',
			'GradeLevel'=>'GRADELEVEL',
			'Enrollment'=>'ENROLLMENT',
			'Member'=>'MEMBER',
			'SendToSalutation'=>'REPORTSAL',
			'SendToFirstName'=>'REPORT1ST',
			'SendToLastName'=>'REPORTLAST',
			'SendToEmail'=>'REPORTEMAIL',
			'SendToTitle'=>'REPORTTITLE',	
			'DateEntered'=>'ENTERDATE',
			'TimeEntered'=>'ENTERTIME');
	 include('./mainadmin_config.php');
	 if(!isset($_POST['NewForms'])){
		 $SQL="select * from NewClients where Status='new' Order By DateEntered desc";
	}else{
		$SQL="select * from NewClients Order By DateEntered desc";
	}
	 $csv="";
	 $sizeof_cols=sizeof($cols);
	 $RS=mysql_query($SQL);

 //output column headers
foreach($cols as $key=>$value) {
	 $csv.='"'.$value.'",';
 }
 $csv.="\r\n";  // line break
 // output column data
 while($myrow=mysql_fetch_assoc($RS)){
	foreach($cols as $key=>$value) {
		  $csv.='"'.$myrow[$key].'",';
	}
	$csv.="\r\n";  // line break	
}
if (strstr($_SERVER["HTTP_USER_AGENT"],"MSIE 5.5")) { 
	 $att = "";
} else {
  	 $att = " attachment;"; 
} 
header('Content-Type: application/download; name="export_new.csv"'); 
header('Content-Disposition:'.$att.' filename="export_new.csv"');
header('Content-Transfer-Encoding: binary'); 
echo $csv;
	  ?>

The associative array at the top represents the field name mappings (ie, database to csv mappings). It writes out the first row as column headings.

-- hth

swr

freebie,
Your code won't properly handle strings that contain quotes. The quotes should be doubled-up, so " becomes "". Most parsers know how to handle this.

It's a pretty easy change; the example in the thread skeetley linked to shows how to do it (it's a simple str_replace).

I remember reading somewhere that there is an ISO specification for CSV, but I haven't been able to find it.

skeetley

swr,
I am using the code below, taken generally from what you offered in that other thread. I added the db connect, my sql query and I removed the " from the output file. It all works great.

Now I what I want to do is create a line that prints the column headers first. Now these are not the column headers in the db. I want the first line of the csv file to be: hourly,hour,temp,rh,precip,ws,wd

Secondly, at what step in this code do I change the data in the fields such as making the date dd/mm/yyyy from yyyy-mm-dd or the time from hh:mm:ss to hh? I can do these manipulations, I just don't know where to put the code for it.

<?php

if (!$link = mysql_connect('localhost', 'uuuu', 'ppppp'))
    exit("Unable to make a connection to the database");
if (!mysql_select_db("ops_db", $link))
    exit("Unable to select the database");

$delimiter = ','; // delimiter is sometimes ; or | instead 
$quote = ''; // quote is sometimes ' instead 
$your_query="SELECT Date_Valid, Time_Valid, Temp, RH, Rn_1, WSpd, DDir FROM Actual_Hourly_2004 WHERE `FMFP_ID` = '".$station."' ORDER by 'Date_Valid', 'Time_Valid' DESC";
$result = mysql_query($your_query) or die(mysql_error()); 

header("Content-type: text/csv"); 

while ($record = mysql_fetch_row($result)) { 
    $first = true; 
    foreach ($record as $field) { 
        if (!$first) echo $delimiter; 
        $field = str_replace($quote, $quote.$quote, $field); // escape quotes 
        echo $quote.$field.$quote; 
        $first = false; 
    } 
    echo "\n"; 
}
?>

skeetley

OK, I got the column headers to print, but I am still lost as to where and how to set up my data formatting. Ideas?

swr

Before the foreach, do something like this:

$time = explode(':', $record[1]);
$record[1] = $time[0];

That changes the contents of the $record array. When the foreach loop displays it as CSV, it's displaying the modified version.

skeetley

swr, thank you so much!! I recommend you for moderator status!

jasonn

I have taken the two scripts here and kind of combined them to this:

<?php
	$sql = "select DISTINCT CustomerFirstName, CustomerLastName, Address1, Address2, City, State, ZipCode from CatalogRequests where DateMailed is NULL order by CustomerLastName";
	$result = mysql_query($sql, $db) or die(mysql_error());

$delimiter = ",";
$quote = '"';

$csv = "";
while ($row = mysql_fetch_assoc($result)) {
	$first = true;
	foreach ($row as $field) {
		if (!$first) $csv .= $delimiter;
		$field = preg_replace("/\"/", "\\"", $field);

		$csv .= $quote.$field.$quote;
		$first = false;
	}
	$csv .= "\n";
}

if (strstr($_SERVER["HTTP_USER_AGENT"], "MSIE 5.5")) {
	$att = "";
} else {
	$att = " attachment";
}
header ('Content-Type: application/download; name="exportcsv.csv"');
header ('Contnet-Disposition: ' . $att . ' filename="exportcsv.csv"');
header ('Content-Transfer-Encoding: binary');
echo $csv;
?>

The only problem I have run into is that the downloaded file is being named export_csv.php (name of file loaded into browser) instead of export_csv.csv (name set in headers).

Does anybody have any ideas as to why I can't get this to work.

Thanks,
jasonn.