SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    creating a file, to use by Excel

    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:
    PHP Code:
    $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

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But how do I write a text-file to a location on the server's harddrive?

  4. #4
    SitePoint Evangelist
    Join Date
    Dec 2000
    Posts
    528
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    Corbb O'Connor
    Looking for quality website design or database programming?
    Contact me for more information and a FREE quote!

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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().

    PHP Code:
    <?
    $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.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Last edited by freddydoesphp; Aug 5, 2001 at 09:24.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :
    PHP Code:
    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.

  9. #9
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!!!

  10. #10
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    new problem

    I have a problem with the following statement:
    PHP Code:
           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

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works for me. Win98, IE5, and I have Excell installed.
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  13. #13
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  15. #15
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by jazztie; Aug 26, 2001 at 03:32.

  16. #16
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    all the data in one cell

    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..
    the neigbours (free) WIFI makes it just a little more fun

  17. #17
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    hmm

    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
    the neigbours (free) WIFI makes it just a little more fun

  20. #20
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  21. #21
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  22. #22
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yes!!!!

    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
    the neigbours (free) WIFI makes it just a little more fun

  23. #23
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  24. #24
    SitePoint Guru
    Join Date
    Aug 2001
    Location
    Amsterdam
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yeah ..

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

    That's my worry..

    Thanks..

    Peanuts
    the neigbours (free) WIFI makes it just a little more fun


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •