creating a file, to use by Excel

[jazztie]

I'm currently working on an administration webprogram. The organization stores all its clients in a website database. Now, they want to create a file, which can be read by a spreadsheet program so they can use that program to print address-stickers.

All the information needed (first name, last name, address [street, zip code, city], businessname) are stored in a single table. A simple list for the website would be created like this:

$clientQuery    = mysql_query("SELECT * FROM client ORDER BY businessname");
        
        if(mysql_num_rows($clientQuery) > 0){
          while($row = mysql_fetch_array($clientQuery )){
                  extract($row);  
$first_name = mysql_result($clientQuery,0,'first_name');
$last_name = mysql_result($clientQuery,0,'last_name');
$street = mysql_result($clientQuery,0,'street');
$zipcode = mysql_result($clientQuery,0,'zipcode');
$city = mysql_result($clientQuery,0,'city');
$business_name = mysql_result($clientQuery,0,'business_name');

print  "et cetera, et cetera, you catch my drift";
  }
} 

Now, what I would like to know is:
- how do I create a file with the list of people?
- what should it look like so it can be read as a datafile by programs like Excel?

Please advise... All help is appreciated

[aspen]

just print it out in a delimited table.

comma is the most used one

in excel you can open it as a csv

so you just need to output it like this

$name,$lastname,$city,$state,$zip\n

and so on

[jazztie]

But how do I write a text-file to a location on the server's harddrive?

[JustForWebmasters]

Try this page: http://www.php.net/manual/en/features.file-upload.php.

I'm not quite sure if that's what you're looking for, but it's worth a try.

You could also store the files in a MySQL database. See this page for information on how to do it.

Hope that helps!

[freddydoesphp]

What you need to do, is not upload files, you need to create a file for download on the fly. Here is what I would do. Build the long string of all the records in a delimited fashion like with commas, and encapsulated by doubloe quotes. Then use header() to force the download. AS a sidenote by using extract($row) you do not need to assign mysql_result() to each variable because that is what extract does you can start calling the variables after extract().

<?
$clientQuery    = mysql_query("SELECT * FROM client ORDER BY businessname");
    if(mysql_num_rows($clientQuery) > 0){
        while($row = mysql_fetch_array($clientQuery )){
            extract($row);  
            $data .= sprintf('"%s","%s","%s","%s","%s","%s"%s', $firt_name, 
                                                 $last_name, 
                                                 $street, 
                                                 $zipcode, 
                                                 $city, 
                                                 $business_name, 
                                                 "\n");
        }
    }
header("Content-Type: application/oct-stream");
header("Content-Disposition: attachment; filename=\"file.xls\"");
print $data;
?>

AS with other header() calls this must reside before any other output to the screen. Just create this phgp script and call it from the browser it will auto force the downnlaod of the excel file. You can change the name from file.xls to anything you want, I used .xls extension because you mentioned Excel, you could use a .csv extension if you plan on opening it in other programs as well.

[jazztie]

Hey Freddy,

thank you for the code! I have a slight problem though. I don't know what to make of it.
This code you provided just prints the output on the screen. I don't get any message that I can download the 'file.xls'. (nor any errors)

Does this have to do with the properties of the php-server, or is it something in the code?

Jazz

[freddydoesphp]

It works for me I just tested it. Why don't you give me the run down on what platform and browser you are using for testing this? Also could you show the exact code you tried, sometimes something can get omitted on the transfer of code.


I will try it on some other browsers and see if I can recreate your problem and fix it. BTW I tested it on IE5.5 on Windows.

I know its a stupid question, so no offence, but you are calling the script through a browser and having the server parse the page right?

[jazztie]

Hey Freddy,

I can give you the phpinfo(); if you want.
I have the file on a Linux computer somewhere.

But the script is called from a normal Windows platform (win98 SE), with Internet Explorer v6. I copied your code as it was. (only changed the table name, because it was in dutch).

after reading up on it at http://www.w3.org/Protocols/rfc2616/rfc2616 I changed the first header information to :

header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"file.csv\""); 

"application/oct-stream" didn't intepretet the \n.
octet-stream did.

If I click on 'save as' in the browser, the file is called file_csv. (???)

link of the file: http://jongmkb.ibkb.net/file_write.php so you can see the output.

[jazztie]

This is really weird...

I just clicked on the link in my previous message, and now it does work! This is really, really strange. Oh well, it seems like the problem has been solved. I just have to call the link from a different window.

Thanks Freddy!!!

[jazztie]

I have a problem with the following statement:

       header("Content-Type: application/octet-stream");
       header("Content-Disposition: attachment; filename=\"relaties $date.csv\"");
      print $data; 

When used under Windows 2000 pro (maybe also under different platforms) this provides me with a problem:

First it asks to 'save as' the window that links to the page with the 'header'-code... when I click 'open from current location' it asks to 'save as' the window that has the 'header'-code... when I click 'open from current location' it finally ask to 'save as' relaties 23082001.csv.

What is wrong here? I don't have this problem under Windows98.
What should I change?

Jazz

[freddydoesphp]

I am on win2k and I just followed the link above
http://jongmkb.ibkb.net/file_write.php

And it prompts me to save the file. After saving the file I can view it in Excel, so I am not sure why it isn't working for you. I can email you the file I just downloaded from your site, if you want.

[exbabylon]

Works for me. Win98, IE5, and I have Excell installed.

[jazztie]

When you guys click on the link http://jongmkb.ibkb.net/file_write.php
You get a new page with the question to save 'file_write,php'... if you do you get a 'save as' screen with 'file $date.csv'..

But when you do, the page 'file_write.php' appears on the background as: 'The Page Cannot Be Displayed'.
How do get the file_write page to display something more user-friendly... or close it after the user has saved the file?

[jazztie]

By the way... I found the problem for why it asks 3 or 4 times to save the different pages...

Or actually the solution, I still don't understand why the problem exsists.

If the link (which calls the file_write file) has a target = '_new' in it, then it solves the problem. Which is quite weird... but, hey... as long as it works.

But then I still have the problem in my previous post. How can I produce a more user-friendly file_write.php page? Or close it after the user has saved the file?

[jazztie]

Let me show you what I mean:

http://jongmkb.ibkb.net/ind4/mailwizard01.php

You will see a mail-wizard with two options:
- papieren mailing
- emailing

click on papieren mailing

A new window will open, with several buttons:
select : selecteer op status

in the next window you will see a list of people with checkboxes in front of them.
Check some people (or all)
and click on the button at the bottom reading 'Bestand opslaan'.

A new window will open and a pop-up window asking you if you want to save the page...here is the problem.
It should save the file, instead it wants to save the page first.

How do I fix this? PLease help

[peanuts]

I have the download part finally working but it is dumping all the data in one cell!!

This is what I use..

$data = sprintf('"%s","%s","%s","%s","%s","%s"%s', "Verzamelen", "Speeltijd", "Thuisteam", "Uitteam", "Veldtype", "Veldnr", "scheids/vervoer", "\n");
while {all the while stuff
$data .= sprintf('"%s","%s","%s","%s","%s","%s"%s', $verzamelen, $speeltijd, $thuis, $uit, $veldtype, $veldnr, $fluitvervoer, "\n");
}
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"overzicht.csv\"");

print $data;

Try this link otherwise..
Click here

Please help..

[jazztie]

The problem lies with the settings of Excell. You need to import a datafile, and be sure to select 'comma' as the seperating value. Otherwise, all the data WILL be put in one cell.

I've had the same problem, and was as confused as you were. Somehow, it's a problem that Office programs have. Nothing much to do about it, unfortunately.

[jazztie]

Another problem is:

('"%s","%s","%s","%s","%s","%s"%s'

%S stands for a colums header (or something like that)... so, if you have more or less colums, change the number of %s.

It seems like you have 6 %s and 7 columns.

[peanuts]

I thoughed that was the hole idea of a csv file that Excell knows to look for comma's ..
second if I change it to a text file I get the options to use comma to put it in to more cells the problem I then have is that I won't recognise the new lines..

so if I place /n it just ignores it..

I'm really getting confused now!!

Please Help

Peanuts

[jazztie]

That's what I thought as well... You would think csv (Comma Seperated Values) would be read automatically by Excel or Office in general.

Unfortunately that is NOT the case.

For the columns: Just use the format we used above. Pay close attention the last combination of %s's. This to like that, otherwise it won't work.

[freddydoesphp]

Why don't you put a \r\n at the end of each line instead of just \n I think even excel will be able to recognize the \r as a newline.

[peanuts]

I got thet thing to work now...

The only problem I have now is to get excell to recognize it but I don't think this will be easy.. So I think I'll go for the txt solution.

Thanks all,

Peanuts

[jazztie]

If you want to use it in a mailmerge:

I've been told that Word in combination with mail merge doesn't have a problem with it.

If you want to use it in Excel:
- open a new, empty worksheet
- open menu 'data'
- click on 'Import external Data' >> 'Import data'
- select the filename

In the wizard, make sure to check that it is a 'delimited' file. In the next step you'll get the option to select which is the delimiter (in your case a comma)

This should work.

Jazz

[peanuts]

I know how to get it in excell myself but will the users know???

That's my worry..

Thanks..

Peanuts