Saving continually to one excel file, rather than creating a fresh one every time

Hi all,

This is an extension from a project I came for help with and one of the problems was on click of an export button the correct data was exported to an excel file, which we got working using the code below.

Now i have a slightly different take on it for a different project.

This time they want to data to be added to the same excel file that sits on the server, so building it up rather than creating a fresh one each time, and still having the option once the data has been saved of saving the file locally.


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");

$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

if ($_SESSION['flaglog']!=1)
$query = "SELECT * FROM Users WHERE Username='$username'";
$res = sqlsrv_query($conn, $query);
while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) {
$name = $result["First_Name"];
$security = $result["Admin"];
//echo $security;
$contractLike = substr($username, 0, 3);
//echo $contractLike;

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace('))', ')', $query);
$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
$query = str_replace("%')", "%'", $query);

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 for download
  $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;

  $result = sqlsrv_query($conn, $query) or die('Query failed!');
  while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {

    if(!$flag) {
      // display field/column names as first row
      fputcsv($out, array_keys($row), ',', '"');
      $flag = true;
    //array_walk($row, 'cleanData');
	foreach ($row as $key => $value)
    if ($value instanceof DateTime)
    $row[$key] = date_format($value, 'd/m/y');
	$row = str_replace(",", ",", $row);
	$row = str_replace(".", ".", $row);
    fputcsv($out, array_values($row), ',', '"');


$out = fopen(“php://output”, ‘w’);

$out = fopen(“some/local/file”, ‘a+’);

and then
echo fread($out, filesize(‘some/local/file’));

never, seriosuly thats all it is?

Had to tweak my post a bit, but yeah. Really. You’ve already got the formatting, you just need to tell the script to Append (a) the local file, rather than writing to the output buffer, and then be able to read (+) it back out using fread.

EDIT: ACTUALLY. No. I’m wrong there. You need to tweak your query to only pull NEW lines, or you’ll be duplicating your data. So you’ll need to store some information about the last/newest/whatever piece of information the script pulled.

so the a+ is append is it?

blimey that suprised me.

and the only pull NEW lines out, could you help me a little bit more with that please.

a+ means “Read/Write, Create file If It Does Not Exist, Put File Pointer at end of file”
(a means the same, but without Read)

rewind puts the file pointer back at the beginning; this is necessary so that fread can read the document in it’s entirity.

As for the new lines; as i said, you’ll need to store a piece of information somewhere that identifies your data as having been read; I cant quite follow your input/output, so i’ll have to be a bit vague;
If the data has an ID column, for example;

ID             DATA
1              Moo
2              Cow

ID (probably an auto_increment) will always increase. If I pull the data now, my last record would be ID 2.

If i then add some more data

ID             DATA
1              Moo
2              Cow
3              Foo
4              Bar
5              Natural

I dont want rows 1&2 again, because i’ve already written them to my file. So my query would have to implement WHERE ID > 2.

mm, yes I see thats a tricky one that isnt it, will have to think about that and see what could work.

I may have to re loop again at the end and add a value to a field there that says this has been saved before, a simple 1,0 would work, but would have to get it working.

Thanks a lot for this, that has taken a huge worry off my mind.

I will crack on with this, and post back if I get any problems, so thanks again for all the help.

Good luck with it… I have a feeling you’re trying to abstract this script to the point that it is causing you grief. (IE: Your query is too abstract - could be anything coming into that query)

Yes I agree, on this occasion though the query is going to be a lot easier, with that one above it had to be abstract due to the job, but this is far more straight forward, and thikning about it i dont have to loop around as when the last bit of the form is completed the user clicks submit and then after that the excel file is created.

So at the point where the user clicks submit that indicator can go in then, and then the query can read it to create the new line to go into the excel file.

I tihnk once I get going with it, and simplify it, I will see how it can come together.

Thanks again though, will surely return, but not for a day or so.

This is going to sound like I dont know what I’m doing, but I cant seem to figure out what the path would be to a file lets say in My Documents (he shamefully asks!)

How can I get that, or is there a standard path to that folder on a PC