[SOLVED] Horrible Zip Code Problem - Excel drops leading zero for NorthEast Zips

aprillougheed · February 14, 2007

I really need some help. :'( We need to export from MySQL through PHP to Excel. We then need to print Mailing Labels.

Everything is working great - EXCEPT the leading zero on NorthEast Zip Codes gets dropped by Excel.

We tried to force Excel to view the field as Text by using a ' - but Excel just shows the ' in the cell

concat(\"'\", r_Zip) as Zip

So now we are trying to force Excel to view the field as Formula by using something like ="01776", but we can't figure out how to escape the quotes.

concat(\"=\", r_Zip) as Zip

Can you help me write the concat or would you suggest I do something else with the code below.

Many, many thanks, April

 
$query_rs_addressOnly = "SELECT od_id, r_cat_id as Division, Company, r_First_Name as First, r_Last_Name as Last, r_Address as Address, Address2, r_City as City, r_State as State, concat(\"'\", r_Zip) as Zip, r_Ordered_By as Ordered, dateline as Date FROM tbl_orderNEW WHERE Ship_Choice = 'CUSTOMER' ORDER BY od_id DESC";
$rs_addressOnly = mysql_query($query_rs_addressOnly, $ONE) or die(mysql_error());
$row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly);
$totalRows_rs_addressOnly = mysql_num_rows($rs_addressOnly);


//Export to Excel Server Behavior ADDRESS
if (isset($_POST['Address'])&&($_POST['Address']=="2")){
$lang=(strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],",")===false)?$_SERVER['HTTP_ACCEPT_LANGUAGE']:substr($_SERVER['HTTP_ACCEPT_LANGUAGE'],0,strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],","));
$semi_array=array("af","zh-hk","zh-mo","zh-cn","zh-sg","zh-tw","fr-ch","de-li","de-ch","it-ch","ja","ko","es-do","es-sv","es-gt","es-hn","es-mx","es-ni","es-pa","es-pe","es-pr","sw");
$delim=(in_array($lang,$semi_array) || substr_count($lang,"en")>0)?",":";";
$output="";
$include_hdr="1";
if($include_hdr=="1"){
$totalColumns_rs_addressOnly=mysql_num_fields($rs_addressOnly);
for ($x=0; $x<$totalColumns_rs_addressOnly; $x++) {
	if($x==$totalColumns_rs_addressOnly-1){$comma="";}else{$comma=$delim;}
	$output = $output.(ereg_replace("_", " ",mysql_field_name($rs_addressOnly, $x))).$comma;
}
$output = $output."\r\n";
}

do{$fixcomma=array();
    foreach($row_rs_addressOnly as $r){array_push($fixcomma,ereg_replace($delim,"¸",$r));}
    $line = join($delim,$fixcomma);
    $line=ereg_replace("\r\n", " ",$line);
    $line = str_replace(',',' , ',$line);  //make a space around each ","
    $line = ucwords($line); //make each 
    $line = str_replace(' , ',',',$line); //take the space away
    $line = "$line\n";
    $output=$output.$line;}while($row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly));
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=addresses.csv");
header("Content-Type: application/force-download");
header("Cache-Control: post-check=0, pre-check=0", false);
echo $output;
die();
}

effigy · February 14, 2007

Without formatting the spreadsheet, you can turn the value into a formula like so:

echo '="' . $value . '"';

Source: clicky.

aprillougheed · February 14, 2007

Thanks for helping. I'm quite a newbie.

How would I put the code

echo '="' . $value . '"';

in to this code

$query_rs_addressOnly = "SELECT od_id, r_cat_id as Division, Company, r_First_Name as First, r_Last_Name as Last, r_Address as Address, Address2, r_City as City, r_State as State, concat(\"'\", r_Zip) as Zip, r_Ordered_By as Ordered, dateline as Date FROM tbl_orderNEW WHERE Ship_Choice = 'CUSTOMER' ORDER BY od_id DESC";
$rs_addressOnly = mysql_query($query_rs_addressOnly, $ONE) or die(mysql_error());
$row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly);
$totalRows_rs_addressOnly = mysql_num_rows($rs_addressOnly);

effigy · February 14, 2007

Change concat(\"'\", r_Zip) as Zip to concat('="', r_Zip, '"') as Zip.

aprillougheed · February 14, 2007

I sure appreciate your help. This is my third day using trial and error to figure this out. So thanks so much.

Here is what I get now ...

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/netnet/public_html/form/cs-literature-allinone-formsBack-Mine/Record_Sets/rs_orderNEW.php on line 56

using this:

$query_rs_addressOnly = "SELECT od_id, r_cat_id as Division, Company, r_First_Name as First, r_Last_Name as Last, r_Address as Address, Address2, r_City as City, r_State as State, concat('="', r_Zip, '"') as Zip, r_Ordered_By as Ordered, dateline as Date FROM tbl_orderNEW WHERE Ship_Choice = 'CUSTOMER' ORDER BY od_id DESC";
$rs_addressOnly = mysql_query($query_rs_addressOnly, $ONE) or die(mysql_error());
$row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly);
$totalRows_rs_addressOnly = mysql_num_rows($rs_addressOnly);

Below is the whole code - I don't see anything else that could be a problem do you?

I'm beginning to wonder if a different Header and/or Content type would make a difference. Probably not, as Excel is so set on stripping the zeros.

<?php
mysql_select_db($database_ONE, $ONE);
$query_rs_addressOnly = "SELECT od_id, r_cat_id as Division, Company, r_First_Name as First, r_Last_Name as Last, r_Address as Address, Address2, r_City as City, r_State as State, concat('="', r_Zip, '"') as Zip, r_Ordered_By as Ordered, dateline as Date FROM tbl_orderNEW WHERE Ship_Choice = 'CUSTOMER' ORDER BY od_id DESC";
$rs_addressOnly = mysql_query($query_rs_addressOnly, $ONE) or die(mysql_error());
$row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly);
$totalRows_rs_addressOnly = mysql_num_rows($rs_addressOnly);

//Export to Excel Server Behavior ADDRESS
if (isset($_POST['Address'])&&($_POST['Address']=="2")){
$lang=(strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],",")===false)?$_SERVER['HTTP_ACCEPT_LANGUAGE']:substr($_SERVER['HTTP_ACCEPT_LANGUAGE'],0,strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],","));
$semi_array=array("af","zh-hk","zh-mo","zh-cn","zh-sg","zh-tw","fr-ch","de-li","de-ch","it-ch","ja","ko","es-do","es-sv","es-gt","es-hn","es-mx","es-ni","es-pa","es-pe","es-pr","sw");
$delim=(in_array($lang,$semi_array) || substr_count($lang,"en")>0)?",":";";
$output="";
$include_hdr="1";
if($include_hdr=="1"){
$totalColumns_rs_addressOnly=mysql_num_fields($rs_addressOnly);
for ($x=0; $x<$totalColumns_rs_addressOnly; $x++) {
	if($x==$totalColumns_rs_addressOnly-1){$comma="";}else{$comma=$delim;}
	$output = $output.(ereg_replace("_", " ",mysql_field_name($rs_addressOnly, $x))).$comma;
}
$output = $output."\r\n";
}

do{$fixcomma=array();
    foreach($row_rs_addressOnly as $r){array_push($fixcomma,ereg_replace($delim,"¸",$r));}
    $line = join($delim,$fixcomma);
    $line=ereg_replace("\r\n", " ",$line);
    $line = str_replace(',',' , ',$line);  //make a space around each ","
    $line = ucwords($line); //make each 
    $line = str_replace(' , ',',',$line); //take the space away
    $line = "$line\n";
    $output=$output.$line;}while($row_rs_addressOnly = mysql_fetch_assoc($rs_addressOnly));
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=addresses.csv");
header("Content-Type: application/force-download");
header("Cache-Control: post-check=0, pre-check=0", false);
echo $output;
die();
}


?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Literature Orders - Addresses</title>
<link href="../../temp-template.css" rel="stylesheet" type="text/css" />

<style type="text/css">
<!--
.c_cell {background-color:#F3FAFE;
border: 2px solid #0B4994;
}
.stress1 {font-weight:bold;
}
.stress1 {font-weight:bold;}

h1 {
font-family: Tahoma, Arial, Helvetica, sans-serif;
font-size: 14px;
font-weight: bold;
color: #15357D;
margin: 0px;
}

.legend {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
color: #3263C6;
font-weight: bold;
padding=top: 5px;
padding: 10px;
}
select, option {

font-family : Arial, Helvetica, sans-serif;  font-size: 11px;

}
.style2 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
color: #3263C6;
font-weight: bold;
}


-->
</style>

</head>

<body>
<p> </p>
<p> </p>
      <h1>  Export to Excel functions</h1>
      <table border="0" cellpadding="10" cellspacing="0">
        <tr valign="top">
          <td width="240" height="65"> </td>
          <td width="240">
	  <form name="form2" method="post" action="" style="border: 1px dashed #999999">
<br />
            <span class="legend">Export Addressesz<br />
            <br /></span>

            <input name="Address" type="hidden" id="Address" value="2">
   <button type="submit" name="Submit">
		<img src="../../../images/excel.gif" width="16" height="16" align="absmiddle"> Export Address    </button>
	    <br />
	   <br />
	  </form></td>
        </tr>
</table>
<p>  <a href="rs_orderNEWWW.php" class="verdana">Wastewater Only Page</a> </p>
<br>
</body>
</html>
<?php

mysql_free_result($rs_addressOnly);


?>

effigy · February 14, 2007

You need to escape the double quotes; I missed those.

aprillougheed · February 14, 2007

Yes, that's what I'm having trouble understanding how to do.

Would it be this?

concat('=\""', r_Zip, \"'"') as Zip

I've tried dozens of combinations and I can't figure out where to put the \".

Hope you can help. April

Jessica · February 14, 2007

You use \ to escape a quote once you're already inside a string. So if your string starts with " you'd need a \ before any other " except the closing one.

So try:

$query_rs_addressOnly = "SELECT od_id, r_cat_id as Division, Company, r_First_Name as First, r_Last_Name as Last, r_Address as Address, Address2, r_City as City, r_State as State, concat('=\"', r_Zip, '\"') as Zip, r_Ordered_By as Ordered, dateline as Date FROM tbl_orderNEW WHERE Ship_Choice = 'CUSTOMER' ORDER BY od_id DESC";

aprillougheed · February 14, 2007

Oh my gosh - it worked!!!!

concat('=\"', r_Zip, '\"') as Zip

You have no idea how much I appreciate your help.

Thanks also for explaining why. I was trying to use \" to escape ". No wonder it didn't work.

I also didn't know where to put the '.

Man I hope something really really good happens to you.

Karma Baby!!!

April

Sign In

[SOLVED] Horrible Zip Code Problem - Excel drops leading zero for NorthEast Zips

Recommended Posts

aprillougheed

Link to comment

Share on other sites

effigy

Link to comment

Share on other sites

aprillougheed

Link to comment

Share on other sites

effigy

Link to comment

Share on other sites

aprillougheed

Link to comment

Share on other sites

effigy

Link to comment

Share on other sites

aprillougheed

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

aprillougheed

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information