Exporting to Excel creating error

multichild · January 16, 2018, 12:08pm

I have an option on my site to export a set of results to an Excel file, but Im getting an error that seems to relate to the file extension being .csv so i thought to change it to .xls and the same error appears.

This is my full code for that page

session_start();
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");

function cleanData(&$str)
  {
    if($str == 't') $str = 'TRUE';
    if($str == 'f') $str = 'FALSE';
    if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
      $str = "'$str";
    }
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
    $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
  }

  $filename = "website_data_" . date('Ymd') . ".csv";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: text/csv; charset=UTF-16LE");

  $out = fopen("php://output", 'w');

  $flag = false;

When I click export the first warning is -

The file format and extension of ‘website_data_20180116-21.csv’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, dont open it. Do you want to open it anyway.

So I click ‘Yes’ to open it anyway, and then I get

Excel has detected that ‘website_data_20180116-21.csv’ is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format.

So I click OK to open and if I then keep that empty Excel file open, I can then click Export again and the data will then create an excel file no problem. but I have to have excel open for it to load without these messages.

I read somewhere its because in the first column I have a field name called ‘ID’, but not sure and how would I change that to say ‘id’ just to output it to the excel file.

multichild · January 16, 2018, 12:57pm

I have found out how the array looks and you’ll see ID is the first row, by using the code below

print_r ($row);

[ID]=>3129															
[Contract_Number]=>DOM00242

There loads more after Contract_Number, was thinking something along the lines of

$row["ID"] = $row["id"];
unset($row["ID"]);
//print_r ($row);
fputcsv($out, array_values($row), ',', '"');

its not right I know, but I think I’m on the right path

droopsnoot · January 16, 2018, 1:02pm

$row["ID"] = $row["id"];

Other way around surely?

multichild · January 16, 2018, 1:05pm

I literally beat you to it by seconds lol.

Sorry droopsnoot, I noticed it late on, but it just deleted the value and didn’t actually change the field name from ID to id, took out ‘unset’ too and same, didn’t work.

For perspective, the whole bottom part as I have it

$result->execute();
  
  foreach ($result as $row) {

    if(!$flag) {
      fputcsv($out, array_keys($row), ',', '"');
      $flag = true;
    }
	foreach ($row as $key => $value)
    	{ 
    if ($value instanceof DateTime){ 
    $row[$key] = date_format($value, 'd/m/y'); 
	  }
    }
	$row = str_replace("&#44;", ",", $row);
	$row = str_replace("&#46;", ".", $row);

	$row["ID"] = $row["id"];
	//unset($row["ID"]);

	print_r ($row);

    fputcsv($out, array_values($row), ',', '"');
  }

  fclose($out);
  exit;

multichild · January 16, 2018, 1:15pm

No what am i doing, this is miles off, all I need to do is change the name from ID to id in the first column of the excel file, not the actual array that builds the results.

So back to the drawing board

multichild · January 16, 2018, 1:24pm

OK I think I know where the change has to be made, but what i have isnt working again

foreach ($result as $row) {

    if(!$flag) {
      fputcsv($out, array_keys($row), ',', '"');
      fputcsv($out, array_keys($row), 'ID', 'id');
      $flag = true;
    }

The line fputcsv($out, array_keys($row), ‘,’, ‘"’); was there and was working fine, I added the line below but it didnt do the job

droopsnoot · January 16, 2018, 1:29pm

That second fputcsv seems strange to me, as won’t that just output the same row again, but with “ID” as the separator and “id” as the field enclosure? I can’t picture why you’d want to do that.

multichild · January 16, 2018, 1:33pm

Ye your right, but I think its the place where it needs to happen (I’m probably wrong though judging by your thoughts).

I’m not quite sure on this problem in honesty, but changing ID to id will solve the original issue.

The Excel file output looks like this

ID	Contract_Number	Contract_Status	Date_Cancelled	Company_Name	Invoice_Street	Invoice_City	Invoice_State
3109	EGY00670	Active			P.O. Box: 277	Sahl Hasheesh	Hurghada
3111	MEX00223	Active			Avenida Sabalos Cerrito S/N, zona hotelera	Mazatlan	Sinaloa

droopsnoot · January 16, 2018, 1:34pm

Then surely just

$row['id'] = $row['ID'];
unset($row['ID']);

would be it?

A quick test to see whether it solves the original issue would be to just stick “xxx” on the beginning of all the row headers?

multichild · January 16, 2018, 1:39pm

ye you would think wouldn’t you, but it doesn’t touch it.

multichild · January 16, 2018, 3:29pm

Tried:

$row = array_map(function($row) {
    return array(
        'id' => $row['ID']
		);
	}, $row);

I’m not even sure if its possible to do this, or do I do a str_replace on the Select statement to change ID to id, but couldnt get that to work either.

I’m just trying things in the wrong places perhaps.

I get this error with this attempt

<b>Warning</b>:  Illegal string offset 'ID' in

In honest ID doesn’t even need to be there, so that whole column could in fact come out so then it will start with a column name titled ‘Contract_Number’.

But again how to do that in my case.

Ive gone right back to where the select statement is created, and wondered if i could do somehting like this

$queryb = "SELECT *, ID as id ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause}";

Change ID to id using ‘as’. It hasnt worked but thought it might be close to an option

droopsnoot · January 16, 2018, 6:40pm

Because you first select *, you get all the columns as well as another called “id”. Can you change it to specify only the columns you need?

Failing that to crack a nut, you could retrieve the row into $row, create a brand new array and do something like

$crw = array();
foreach ($row as $key=>$data) { 
  if ($key == "ID") { 
    $crw['id'] = $data;
    }
  else {
    $crw[$key] = $data;
    }
  }

Or even just do something like that when you’re outputting the column headers, if you’re sure that calling the first one “ID” is the root of the issue.

If the outputting of the array_key stuff is just for the header, how about:

$result->execute();
  
  foreach ($result as $row) {
    if(!$flag) {
      $cheaders = array();
      foreach ($row as $key=>$data) { 
        if ($key == "ID") $key = "xid";
        $cheaders[] = $key;
        }
      fputcsv($out, array_values($cheaders), ',', '"');
      $flag = true;
      }
      foreach ($row as $key => $value)
    	{ 
    if ($value instanceof DateTime){ 
    $row[$key] = date_format($value, 'd/m/y'); 
	  }
    }
	$row = str_replace("&#44;", ",", $row);
	$row = str_replace("&#46;", ".", $row);

	//$row["ID"] = $row["id"];
	//unset($row["ID"]);

	print_r ($row);

    fputcsv($out, array_values($row), ',', '"');
  }

  fclose($out);
  exit;

multichild · January 17, 2018, 9:03am

There we go, you’ve got it, cheers droopsnoot.

Works perfectly.

If you don’t mind could you advise me on something else.

I have the print_r ($row) where you have it and something that needs to be sorted out is in a column such as ‘Country’ the value in the database in this table is numeric, as below

 [Country] => 28

So where would I put the select statement and how would I start it, so that I can run a select statement that takes this value ‘28’ and then checks in another table that I know of that gives the name of the country in text, in this case 28 = Egypt.

What I’m unsure of is how and where to start that check on that certain field, and then do the select statement and loop to change that 28 to a country being name being outputted.

This is what I’m thinking to kick it off

$row = str_replace("&#44;", ",", $row);
$row = str_replace("&#46;", ".", $row);
print_r ($row);
while ($Country = current($row)){
	
}

or

while ($Country = current($row)){
	if ($Country != ""){
		
	}
}

Just so it doesn’t waste time on empty Country array values

droopsnoot · January 17, 2018, 10:10am

It would be easier perhaps to modify your query to retrieve that extra information using an INNER JOIN, though it might make the data output more complex. I’m not a db expert by any means, so maybe someone else will suggest an alternative method, but for example:

select id, contract_number, country from intranet inner join countries on intranet.country = countries.id

which I think would give you the selected columns from the intranet table along with all columns from the countries table.

Adding it as an inner join would be quicker than running a separate query for each result row, though it might not be noticeable.

Once that works you can play with only retrieving the columns you need, for example there’s no need to actually have the numeric country-id in the results set if all you want is the country name, so it could be:

select intranet.id, intranet.contract_number, countries.name as countryname from intranet inner join countries on intranet.country = countries.id

which (I think) would just give those three specified columns and rename the last to avoid confusion.

multichild · January 17, 2018, 10:14am

Ye I see your thinking, could get messy this, as there one main table which the hotel exists in then I could be joining around 6 other tables to it, but ye it could be easier to do it this way.

Am I being stupid though in thinking I can yes do a LEFT JOIN but I cant change for example 28 to Egypt within the select statement so it outputs the name instead of the value.

This is the select statement used

$queryb = "SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause}";

{limit clause} is where I add the users search terms onto the query

This may sound a bit over the top, but maybe a way to go would be to create a query on its own that loops through the Country table, to then create an array where you have a key thats the country name and the value which is the numeric value.

Then somehow in the array thats already there $row, I somehow check the values of both arrays and then when they match up I use the key from the second array to exchange for example 28 with Egypt to output to Excel.

Can as they say talk the talk, its walking the walk with this one.

droopsnoot · January 17, 2018, 11:03am

Well, that’s another way I was thinking of. Something like

$cnames = array();
$query = "select id, countryname from countries";
$result = $db->query($query); // I *think* this is right, usually do prepared statements
while ($row = $result->fetch()) { 
  $cnames[$row['id']] = $row['countryname'];
  }
...
// in loop prior to output:
if (isset($cnames[$row['country']])) { 
  $row['country'] = $cnames[$row['country']];
  }

I think you can using the ‘as’ specifier, but I think you need to stop using '*' and start specifying the columns individually, otherwise you’ll get both the id (as part of the “*”) and the country name where it comes in from the JOIN. I’m not sure what it would output if the country name is not found in the other table - depending on how the data gets into the table, that might not be possible. Hence in the above post in the second query, all you get there is the country name, not the country ID number.

multichild · January 17, 2018, 11:15am

OK sounds good, ye will revert to using field names and not * and yes using ‘as’ might solve the problem, will have a go with the country name section and see where I get.

Thanks again

multichild · January 17, 2018, 4:02pm

Thank you droopsnoot, this is working perfectly, its mad when you know an answer to a question how easy it then is on reflection, but that aside thank you again.

This is a standards question really when using INNER JOIN

I have in some cases 2 fields that are feeding off the second table, for instance I have the main field 'Country; which I started on using

Country.Country_Name as Country
...
FROM Intranet INNER JOIN Country ON (Intranet.Country = Country.ID)

That works great, but then there is another field called ‘Invoice_Country’ which also uses the values within ‘Country’, so am I allowed to use the original INNER JOIN which targets Intranet.Country for this also, or do I need to create another INNER JOIN such as

 INNER JOIN Country ON (Intranet.Invoice_Country = Country.ID)

I’m worried that the original will feed its results into the other Invoice_Country field, rather than it looking after itself. The reason why I’m worried is because I noticed that in a few places where I originally had an empty value, it then got filled up with a value when i did try it that way, so I then guessed that it was being filled by the original call. But then when i tried to create another INNER JOIN it crashed it, so was in a bit of muddle.

This is how it looks at the moment

$queryb = "SELECT Intranet.ID, Contract_Number, date_Cancelled, Company_Name, Invoice_Street, Invoice_City, Invoice_State, Invoice_Country, Invoice_Postcode, Invoice_Tax_Code, Invoice_Telephone, Invoice_Fax, Invoice_Contact_Name, Invoice_Email, Annual_Contract_Value, Invoicing_Profile, Invoice_Paid_By, Currency.Currency as Currency_of_Contract, Contract_Start, Contract_End, Renewal_Date, Contract_Length, Quote_Number, Site_Name, Street, City, State, Country.Country_Name as Country, Postcode, Establishment.Establishment_Name as Type_of_Establishment, No_of_Rooms, Groups.Group_Names as Group_Name, Invoice_Value, Invoice_Currency, Payment_Contact, Payment_Contact_Tel, Invoice_Frequency, Seasonal_or_Full, Month_Opens, Month_Closes, Brandcheck_Frequency, StandardsCheck_Frequency, Roomcheck_Frequency, Foodcheck_Frequency, Crisischeck_Frequency, Poolcheck_Frequency, Firecheck_Frequency, Aquacheck_Frequency, Spacheck_Frequency, Safetycheck_Frequency, Accesscheck_Frequency, Ecocheck_Frequency, Supplycheck_Frequency, Dinecheck_Frequency, Tourcheck_Frequency, Securitycheck_Frequency, Restaurantcheck_Frequency, Training_Days, Labcheck_Aqua_Frequency, Labcheck_Food_Frequency, Labcheck_Pool_Frequency, Labcheck_Room_Frequency, Legionella_Test_Frequency, Additional_Sampling, No_of_Modules, Principle_Contact, Principle_Telephone, Preferred_Language, Principle_Contact_Job_Title, Principle_Contact_Email, contract_File, date_Created, date_Modified, Date_Disabled, ROW_NUMBER() OVER (ORDER BY Intranet.ID) AS 'RowNumber' FROM Intranet INNER JOIN Currency ON (Intranet.Currency_of_Contract = Currency.ID) INNER JOIN Country ON (Intranet.Country = Country.ID) INNER JOIN Establishment ON (Intranet.Type_of_Establishment = Establishment.ID) INNER JOIN Groups ON (Intranet.Group_Name = Groups.ID) WHERE {limit clause}";

I’m loving this though, thanks again

multichild · January 17, 2018, 4:39pm

I think I have an answer to it, but not sure how to apply it, here is the INNER JOINS

INNER JOIN Country AS m1 ON (Intranet.Country = Country.ID) INNER JOIN Country AS m2 ON (Intranet.Invoice_Country = Country.ID)

But then I have the 2 fields, so am not sure if thats correct above how to fuse them together so the one works with the right one, as this didn’t work

SELECT m2.Country_Name as Invoice_Country, m1.Country_Name as Country

droopsnoot · January 17, 2018, 6:40pm

I’m not entirely sure either, it’s getting a bit complex now. In the first part though I think it needs to be something like

inner join country as m1 on (intranet.country = m1.id) inner join country as m2 on (intranet.invoice_country = m2.id)

so the only different is using m1, m2 to specify the joining field instead of the table name as you have it.

Topic		Replies	Views
.xls export PHP	2	515	October 8, 2014
Export table to Excel in php PHP	5	12384	November 3, 2016
Exporting query to .csv file PHP	9	3058	October 8, 2014
Excel file output load error PHP	2	2889	May 28, 2010
I want to export all array to Exel file PHP	4	960	December 31, 2019

Exporting to Excel creating error

Related topics