[RESOLVED] So close to an Outlook calendar import file...but

Joseph_Sliker

Hi;
I've built a calendar for my group that is a bit easier to configure and create reports for our purposes than Outlook (which is the application supported by the overall institution). I am trying to build an Excel and/or .csv export file that will dump a file on the users desktop if they want to put their items from our calendar into their Outlook calendar. I'm real close, but Outlook b-tches about various things when I try to import the dump file.

Here is the code:

<?php
session_start();
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("content-disposition: attachment;filename=outlookimport.xls");
$link = mysqli_connect("localhost",  "user", "password", "database")  or die("Could not connect : " . mysql_error());
?>
<html><body>
<?
$RATER=$_SESSION[LOGINARRAY][RATER];
$sql = "SELECT CONCAT(SCHL,':',SVC_GROUP,':',COMMENT) as Subject, DATE_FORMAT(ARRIVE, '%Y-%m-%d') AS 'Start Date', DATE_FORMAT(ARRIVE, '%l:%i:00 %p') AS 'Start Time', DATE_FORMAT(DEPART, '%Y-%m-%d') AS 'End Date', DATE_FORMAT(DEPART, '%l:%i:00 %p') AS 'End Time' FROM CALENDAR LEFT JOIN SCHOOLS ON SCHOOLS.SCHOOLID=CALENDAR.SCHOOLID LEFT JOIN SVC_CODES ON CALENDAR.SVC_CODE=SVC_CODES.SVC_CODE WHERE RATER='$RATER' and (SCHL !=''and SVC_GROUP !=''and COMMENT !='') ORDER BY ARRIVE"; 

$result = mysqli_query($link,$sql) or die("Query failed : " . mysql_error());  

$row = 1; 
echo "<table>"; 
//the MYSQL_ASSOC gets field names instead of numbers
while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) { 
   if ($row == 1) { 
      echo "<tr>"; 
      foreach ($line as $col_key => $col_value) { 
	      echo "<th>$col_key</th>"; 
      } 
echo "</tr>"; 
   } 
//start a new row
echo "<tr>"; 
   foreach ($line as $col_value) { 
       echo "<td>".$col_value."</td>"; 
   } 
   echo "</tr>"; 
   $row++; 
}
//get the record count right
$row=$row-1;
echo "</table>";
?>   

</body></html>

I've tried various version of headers and filenames like:

header("Content-Type: text/csv");
...
header("content-disposition: attachment;filename=outlookimport.csv");

If I save the downloaded file as an Excel (.xls) file the Outlook import calendar process complains that there are no named ranges, or that I don't have the right to open the file or that someone else has it open (no they don't, it's closed) blah blah blah. If the output file is saved as .csv, then Outlook can't find the column names and asks the user to map Field 2, Field 3, etc. etc to import fields which I know will be beyond the ken of most of my users.

Anything obvious that I'm missing in this code? Different suggestions for all that header stuff (that I don't really understand very well).

Thanks!

dagon

are you trying to write an xls file with the above code?

Joseph_Sliker

Yes I am. But when I get the dialog box that asks me if I want to open or save the file, if I save the file it WILL open just fine in Excel and all the data is lined up just perfectly in rows with column names identical to those required for Outlook import, BUT when I try to go through the Outlook dialog to Import File from another program->Microsoft Excel and then select the file saved on the desktop, Outlook gripes that there are no named data ranges and/or that the file is open (when it is not) or that I don't have the right to open the file.

I have also tried to change the headers to:

header("Content-Type: text/csv");
...
header("content-disposition: attachment;filename=outlookimport.csv");

...in order to create a .csv file. This also sort of works, but the Outlook import dialog requires that I map the columns from generically named "Field 2, Field3" etc etc. which I think will be too confusing/inconvenient for most of my users.

dagon

well you cant create an xls file just by naming it *.xls, and its not a csv file either with all that html in it.

Joseph_Sliker

Oooo...kay. Do you have a suggestion maybe?

dagon

well a csv, is comma separated with values in quoted

'one,','two' line break
'three','four' line break

Joseph_Sliker

Yeah....I understand that. If I change the headers to
header("Content-Type: text/csv");
header("content-disposition: attachment;filename=outlookimport.csv");

and then recreate the offending section like so:

while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) { 
   if ($row == 1) { 
      foreach ($line as $col_key => $col_value) { 
	      echo "".$col_key.","; 
      } 
echo "\n";
   } 
//start a new row
      foreach ($line as $col_key => $col_value) { 
	      echo "".$col_value.","; 
   } 
echo "\n";
   $row++; 
}

...the outlook import process still gripes the the DOS or Windows .CSV translator can't build a field map.

Also, this non-html version introduces a new problem: sometimes there are actual commas in the fields used to concat the "Subject" field, and that means some rows end up having too many columns and that will be a problem too.

Joseph_Sliker

What DOES work, is my original file (it may not be a real excel file but excel opens it and displays it correctly) which I can open, then SAVE AS a .CSV file, Which then works perfectly for the outlook import process. It's just that I want to save that fussy step for my many impatient, easily confused, non-nerdy users.

dagon

well save as csv converts to a csv, so writing a valid (for outlook) csv in the first place would be the best option. how about opening the excel generated csv file in notepad and see what they do differently. Perhaps grab a copy of phpmyadmin and 'borrow' the code for creating "CSV for MS Excel "

Joseph_Sliker

...hmmmm that sounds interesting. I'll give it a try.

Joseph_Sliker

Looks like everything ends up with double quotes around it. Have to figure out exactly how to do that. This shows promise.

Joseph_Sliker

Nope....still not it. The Outlook import process chokes on this file too.

Joseph_Sliker

What a difference a good night's sleep makes.

This works:

<?php
session_start(); // I use sessions to simplify stuff for users
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: text/csv");
header("content-disposition: attachment;filename=outlookimport.csv");
$link = mysqli_connect("localhost",  "USER", "PASS", "DBASE")  or die("Could not connect : " . mysql_error());

$RATER=$_SESSION[LOGINARRAY][RATER];
$sql = "SELECT CONCAT(SCHL,':',SVC_GROUP,':',COMMENT) as Subject, DATE_FORMAT(ARRIVE, '%Y-%m-%d') AS 'Start Date', DATE_FORMAT(ARRIVE, '%l:%i:00 %p') AS 'Start Time', DATE_FORMAT(DEPART, '%Y-%m-%d') AS 'End Date', DATE_FORMAT(DEPART, '%l:%i:00 %p') AS 'End Time' FROM CALENDAR LEFT JOIN SCHOOLS ON SCHOOLS.SCHOOLID=CALENDAR.SCHOOLID LEFT JOIN SVC_CODES ON CALENDAR.SVC_CODE=SVC_CODES.SVC_CODE WHERE RATER='$RATER' and (SCHL !=''and SVC_GROUP !=''and COMMENT !='') ORDER BY ARRIVE"; 

$result = mysqli_query($link,$sql) or die("Query failed : " . mysql_error());  

$row = 1; 
while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) { 
if ($row == 1) { 
 foreach ($line as $col_key => $col_value) {echo "\"$col_key\",";} 
echo "\n\r";
} 
//start data rows
 foreach ($line as $col_key => $col_value) {echo "\"$col_value\",";} 
echo "\n\r";
$row++; 
}
?>

The problem seemed to be the newline/carriage return combo (\n\r)...I was only using newline \n and the Outlook .csv import process was choking on the lack of the \r.

dagon

i think its a windows thing to always need a \r as well, glad its sorted, and thanks for posting the result