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

[aprillougheed]

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]

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

 

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

 

Source: clicky.

[aprillougheed]

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]

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

[aprillougheed]

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]

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

[aprillougheed]

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]

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]

 

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