Export data from PHP to Excel with comma issue

Question

My database:

My database

I am having a issue exporting my data to excel due to the comma in the "description" field in my database.

Here is my code:

<?php 
include 'database.php';
if (isset($_POST['submit'])){
    $filename = 'uploads/'.strtotime("now").'.csv';
    $fp = fopen($filename, "w");
    $sql = "SELECT * FROM data";
    $linkSql = mysqli_query($link,$sql) or die(mysqli_error($link));
    $row = mysqli_fetch_assoc($linkSql);

    $seperator ="";
    $comma = "";
    foreach ($row as $name => $value) {
        $seperator .= $comma . '' .str_replace('','""',$name);
        $comma = ",";
    }
    $seperator .="\n";
    fputs($fp, $seperator);

    mysqli_data_seek($linkSql, 0);

    while($row = mysqli_fetch_assoc($linkSql)){
    $seperator ="";
    $comma = "";
    foreach ($row as $name => $value) {
        $seperator .= $comma . '' .str_replace('','""',$value);
        $comma = ",";
    }
    $seperator .="\n";
    fputs($fp, $seperator);
    }        fclose($fp);
} ?>

<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <form method="post" action="export.php">
            <input type="submit" name="submit" value="export">
        </form>
    </body>
</html>

And here is the data after I export to excel:

Data after export to excel.

Stop using homebrew solutions that don't work properly, and use PHP's built-in fputcsv() function which is written to handle commas correctly — Mark Baker
– Mark Baker, Commented Dec 23, 2015 at 14:37

Ray · Accepted Answer · 2015-12-23 14:39:22Z

5

The issue is you're implementing something needlessly and have to solve all the tedious issues others before you have already addressed.

There's a wonderful built in function called fputcsv() that handles seperators and enclosures to escape your content fields very well.

Here's the link to the php doc page: http://php.net/manual/en/function.fputcsv.php

Learn it
Love it
Live it

answered Dec 23, 2015 at 14:39

Ray

41.6k22 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

crmpicco · Accepted Answer · 2015-12-23 15:39:19Z

0

If you need anything over an above simple reading then maybe have a look at PHPExcel

answered Dec 23, 2015 at 15:39

crmpicco

17.3k31 gold badges142 silver badges223 bronze badges

Collectives™ on Stack Overflow

Export data from PHP to Excel with comma issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related