CSV output to several columns

Question

Everything works perfectly but I've encountered a problem when I'm exporting data to a CSV file. I tried searching for similar problems but I didn't quite find any that could be relevant to my problem.

This code here is supposed to export the whole database table from mySQL database. It does it perfectly but the thing is, it puts all the data in the first column - in different cells. The cell placement is alright but it's supposed to spread the data over several columns (13 in my case).

Here's the screenshot to explain what's happening:

enter image description here Code:

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: filename=export.csv");

#$query=mysql_query("set names 'utf8'");
#mb_http_output('UTF-8');
#mb_http_input('UTF-8');
#mb_language('uni');
#mb_regex_encoding('UTF-8');
#ob_start('mb_output_handler');
#header('Content-type: text/html; charset=utf-8');

$conn = mysql_connect('localhost', 'root', 'asdasd') or die(mysql_error());
mysql_select_db('nooruse', $conn) or die(mysql_error($conn));

$query = sprintf('SELECT osakond as Osakond, soetusaasta as Soetusaasta, it_number as IT_Number, tooteruhm as Tooteruhm, mudeli_nimetus as Mudeli_nimetus, sn as SN, riigivara_nr as Riigivara_nr, inventaari_nr as Inventari_nr, maja as Maja, ruum as Ruum, vastutaja as Vastutaja, markus as Markus,kasutajanimi as Kasutajanimi FROM norse5_proov');
$result = mysql_query($query, $conn) or die(mysql_error($conn));

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

function echocsv($fields)
{   
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}

Thanks in advance.

In my view the above code is working fine.You need to check your csv delimiter in excel....... — Vikas Umrao
– Vikas Umrao, Commented Mar 19, 2013 at 9:04

Joe Stein · Accepted Answer · 2013-03-19 09:42:54Z

1

The problem is you are assuming that Excel knows that a semicolon is the separator.

For some machines this might work, others not.

It was suggested this has to do with regional setting in the control panel under List Separator.

I found a suggestion to add the following as the first line of the CSV to tell excel what separator to use:

sep=;

Haven't tried this, but it seems legit.

Here is a link to a better description (see the 3rd comment down for the way to set the separator in the CSV file to avoid client computer changes):

Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Settings for Europe

edited Mar 19, 2013 at 9:42

answered Mar 19, 2013 at 9:18

Joe Stein

314 bronze badges

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

1 Comment

Erlend Anderson Over a year ago

The separator is supposed to be a comma. It says so in the code and I checked excel settings. The "Use system separators" box was ticked. I unticked it and filled in "decimal separator" to be "," but that was no good. I'll give the "sep:," a try.

Collectives™ on Stack Overflow

CSV output to several columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related