Exporting data from MySQL to Excel (csv) via PHP

Question

I'm going to export data in SQL to Excel (csv) using PHP. I can export all data from SQL to Excel, but it is unknown Japanese language. For now, i can solved it; but other problem is about each row in SQL will export into one column in Excel.

Example (data in SQL)

ccode    country
US       United State
UK       United Kingdom
FR       France
KO       Korea
JP       東京

After export to Excel (csv)

    A                  
1   ccode,country
2   US,United State
3   UK,United Kingdom
4   FR,France
5   KO,Korea
6   JP,東京

Here is my code

<?php

header("Content-type: text/csv; charset=UTF-8");
header('Content-Disposition: attachment; filename=Export.csv');
//connection
$con = mysql_connect('localhost', 'root', '');
if(!$con){
    echo "Error connection";
}
//select db
$select_db = mysql_select_db('country', $con);
if(!$select_db){
    echo "Error to select database";
}
mysql_set_charset("utf8", $con);

//Mysql query to get records from datanbase
$user_query = mysql_query('SELECT * FROM countries');

//While loop to fetch the records
$contents = "ccode,country\n";
while($row = mysql_fetch_array($user_query))
{
    $contents.=$row['ccode'].",";
    $contents.=$row['country']."\n";
}

$contents_final = chr(255).chr(254).mb_convert_encoding($contents, "UTF-16LE","UTF-8");
print $contents_final;

?>

Here is what i want after export to Excel (csv)

    A          B 
1   ccode      country
2   US         United State
3   UK         United Kingdom
4   FR         France
5   KO         Korea
6   JP         東京

Can anyone help me to solve this problem? I'm appreciate to your help!

Thank in advance.

Tarik · Accepted Answer · 2013-09-30 16:42:45Z

3

What you are doing is already correct. When importing the csv file in Excel, specify that the separator character is a comma. Alternatively, use a tab character as a separator in lieu of the comma.

answered Sep 30, 2013 at 16:42

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

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

1 Comment

Tarik Over a year ago

Mark as answered please.

Mike Brant · Accepted Answer · 2013-09-30 16:57:25Z

You really should not be in the business of creating the CSV manually as you are doing. Consider using fputcsv() to do this:

//While loop to fetch the records
fputcsv('php://output', array('ccode','country'));
while($row = mysql_fetch_array($user_query, MYSQL_NUM)) {
    array_walk($row, function(&$item) {
          $item = mb_convert_encoding($item, "UTF-16LE","UTF-8");
    }
    fputcsv('php://output', $row); 
}

Note here that I am also doing the encoding step on each individual field value.

The benefit of using this method is that it will properly enclose all your fields as necessary to prevent certain values from breaking your CSV format. For example, if one of your database values contained ,, your approach would break, whereas using a proper csv creation mechanism would handle this without a problem.

Another added benefit of this approach is that if you have a large set of data, this would use much less memory, in that you would only hold one line of data in memory at a time (before you output it and move to the next data row).

user2915430 · Accepted Answer · 2013-11-07 07:00:56Z

0

use \t for the $content variable to get data in different column of excel sheet

answered Nov 7, 2013 at 7:00

user2915430

1

Comments

TheWolf · Accepted Answer · 2013-09-30 16:29:57Z

-2

You will need to separate your columns with a ; rather than a ,. The name 'comma-separated values' is a bit misleading here. So you would write:

while($row = mysql_fetch_array($user_query))
{
    $contents.=$row['ccode'].";";
    $contents.=$row['country']."\n";
}

answered Sep 30, 2013 at 16:29

TheWolf

1,3857 silver badges16 bronze badges

3 Comments

Mark Baker Over a year ago

The separator is actually locale-specific in MS Excel; so a tab will work in some locales, a semi-colon in others. There is no universal solution; although a sep=; as the first line will help if you use ; as a separator

TheWolf Over a year ago

Have you changed the line $contents = "ccode,country\n";, too?

user2738520 Over a year ago

It works now, by change comma to tab character. Anyways, thank for your help.

Collectives™ on Stack Overflow

Exporting data from MySQL to Excel (csv) via PHP

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

3 Comments

Related