www.webdeveloper.com
Results 1 to 11 of 11

Thread: [RESOLVED] Export to excel with comma in field

  1. #1
    Join Date
    May 2009
    Posts
    233

    resolved [RESOLVED] Export to excel with comma in field

    The name is the database if "Lastname, Firstname" - so when I export to excel the firstname goes into the second field. I have tried using an explode to no avail.

    PHP Code:
    include("../connectdb.php");

    $XML "name,phone,department\n";

    $file ="directory-"date("Y-m-d"). ".csv";

    $query "SELECT * FROM directory_national WHERE title!='Room' AND city='toronto' OR city='ottawa'  ORDER BY name";
    $result mysql_query($query);
    while(
    $rows mysql_fetch_array($result)) {
    $name $rows["name"];
    $phone $rows['phone'];
    $department $rows['department'];

    $XML.= $name",";
    $XML.= $phone",";
    $XML.= $department"\n";
    }
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$file\"");
    header("Content-Transfer-Encoding: binary");
    if(
    strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE')){
        
    header('Cache-Control: public');
    }
    echo 
    $XML;
    exit; 

  2. #2
    Join Date
    Jul 2007
    Location
    Wisconsin
    Posts
    468
    What about a string_replace to convert the comma?


    PHP Code:
    $name str_replace(","  "&# 44 ;"$rows["name"]);  // or replace it with a different char? 
    or else encapsulate the string that has a comma in quotes:

    PHP Code:
    $XML.= " \" " $name " \" ,"
    Last edited by OctoberWind; 08-01-2012 at 11:13 AM.

  3. #3
    Join Date
    May 2009
    Posts
    233
    That worked thanks, but there is an error in your code: a comma is missing

    PHP Code:
    $name str_replace(",",  "&# 44 ;"$rows["name"]); 
    Also though, is there a way that the data can cme out clean without me having to do a search and replace for the unique character, that is, enclose the name with its comma?

  4. #4
    Join Date
    Jul 2007
    Location
    Wisconsin
    Posts
    468
    Quote Originally Posted by beylah View Post
    That worked thanks, but there is an error in your code: a comma is missing
    D'oh!


    Also though, is there a way that the data can cme out clean without me having to do a search and replace for the unique character, that is, enclose the name with its comma?
    Yea, take a look at my second part (which I have have edited after you saw my initial post).

    If you enclose the entire string in quotation marks, that should give you the whole string in one cell (assuming the string doensn't contain quotes).

    PHP Code:
    $XML.= " \" " $name " \" ,"

  5. #5
    Join Date
    May 2009
    Posts
    233
    PHP Code:
    $XML.= " \" " $name " \" ,"
    This purt quotes around the name and still put it into 2 fields:

    "lastname firstname"

  6. #6
    Join Date
    Oct 2009
    Posts
    658
    Why not just use put_csv into a cache or a physical file then spit out the actual content.
    Good Luck

    Santos Systems

  7. #7
    Join Date
    May 2009
    Posts
    233
    because i would like my boss to get the data by himself when he needs it without me having to fix it

  8. #8
    Join Date
    Oct 2009
    Posts
    658
    Why would you fix it? You don't have to.

    1. Get data from database
    2. Build the array (if not big)
    3. Put in a container file
    4. then spit out in php that file


    PHP Code:
    while($rows mysql_fetch_array($result)) { 
     
    // append to csv file


    // headers
    // send the csv file 
    Good Luck

    Santos Systems

  9. #9
    Join Date
    May 2009
    Posts
    233
    Sorry ssytems - I misunderstood what you had said. I thought you were on the same reply as OctoberWind.

    Ok so looking at your suggestion it makes a lot of sense but I am sorry I dont know how to do what you ask. 1. get dat from db I can do that. 2. Build an array - have no clue how or what to array.

    If you could help that would be great.

  10. #10
    Join Date
    Aug 2012
    Location
    Belgium
    Posts
    66
    <?php
    $CSV = "name;phone;department\n";
    $search = ";";
    $replace = " - ";

    while($rows = mysql_fetch_array($result)) {
    $name = $rows["name"];
    $phone = $rows['phone'];
    $department = $rows['department'];

    // optional str_replace in case you are not sure vars don't contain the ";" char.
    str_replace ($search, $replace, $name);
    str_replace ($search, $replace, $phone);
    str_replace ($search, $replace, $department);
    $CSV .= $name . ";" . $phone . ";" . $department . "\n";
    }

    //... do what you want with your csv from this point
    ?>

    ... CSV is a point coma format, and not a coma format^^

  11. #11
    Join Date
    Oct 2009
    Posts
    658
    Quote Originally Posted by Gudlife View Post
    ... CSV is a point coma format, and not a coma format^^
    CSV is an acronym for "Coma" Separated Values. Technically it can be any string separated values, it can be tabs, pipes, double pipes, superstring variable, etc.

    Your code

    Code:
    $query = "SELECT * FROM directory_national WHERE title!='Room' AND city='toronto' OR city='ottawa'  ORDER BY name"; 
    $result = mysql_query($query);
    is # 1: Get the data

    for #2: Build the array, below mysql_fetch_array already made it an array for you and is actually an associated one
    Code:
    while($rows = mysql_fetch_array($result)) { 
    // append to csv file
    }
    for #3: Place the container file
    Code:
    while($rows = mysql_fetch_array($result)) { 
        fputcsv($fh, array_keys($data));
    }
    At that point you are actually placing it in a stream file. For #4 you just need to spit it out. Here is a similar solution http://bluedogwebservices.com/howto-...tream-csv-php/
    Good Luck

    Santos Systems

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles