exporting mysql to excel

period character is parsed as a tab

[kuper20]

Hello, I found a script in one of the older posts on this forum that allowed me to export a certain table to an excel file. Only problem is then whenever there is a period, it skips to the next box(or sometimes skips more than one box)...and I can't seem to figure out why. Here is the script I'm using. Thanks.

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=testInvitationReport.xls");
header("Content-Transfer-Encoding: BINARY"); 
//include("dbconnect.inc.php");
mysql_connect("$host","$user", "$pass");
@mysql_select_db("$db") or die("Unable to select database");
$select = "SELECT * FROM testTable";
$export = mysql_query($select);
$count = mysql_num_fields($export);
for ($i = 0; $i < $count; $i++) 
{
$header .= mysql_field_name($export, $i)."\t";
}
while($row = mysql_fetch_row($export)) 
{
$line = '';
foreach($row as $value) 
{
if ((!isset($value)) OR ($value == "")) 
{
$value = "\t";
} 
else 
{
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") 
{
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";

[eelixduppy]

Have you checked the textual output from this script? Are the tabs and quotes where they should be? Check the syntax to make sure that it's correct in the output.

[StoutFiles]

Look up Navicat...it's the easiest program to convert excel to mysql and back again.

[kuper20]

@eelixduppy: so I looked at the textual output and found out it was linebreaks that were screwing it up, not periods. The code looks like it should work, but maybe that trim() function isn't doing what it's supposed to. I added two lines right before the trim() function and it worked:

$line = str_replace("\r", "", $line);
$line = str_replace("\n", "", $line);

I also took out this line:
$data = str_replace("\r", "", $data);
becuase it wasn't doing anything after the two lines above.

[eelixduppy]

Nice. Glad you resolved your issue :)

[djbuddhi]

u don't have to d like that .display it in a html form while running the above query and top of the form put

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=testInvitationReport.xls");
header("Content-Transfer-Encoding: BINARY");

it works fine , i am using like that 4 my reports

[eelixduppy]

Hello djbuddhi and Welcome to WebmasterWorld! :)

I'm not sure what you are suggesting here. Do you mean for kuper20 to add those headers to the top of the script? If so, if you check the original post you'll see that that is already the case. :)