[SOLVED] CSV export ...

[budimir]

Hey guys,

 

How can I put 5 columns form MySQL to 5 columns in CSV? Not comma separated, but each every column for itself.

 

like this:

 

Column 1    Column 2    Column 3    Column 4    Column 5

Data 1      Data 2        Data 3      Data 4      Data 5

[MatthewJ]

separate them with

\t

 

That would tab delimited, not comma separated

[budimir]

For some reason it's not working for me. This is the peace of code I'm using.

 

//Stavljanje imena svih polja u $out
for($i = 0; $i < $columns; $i++) {

$l = mysql_field_name($fields,$i);

$out .= "".$l."\t";
}

$out .= "\n";

 

 

[Mark Baker]

For some reason it's not working for me.

What errors are you getting? or what is being generated in $out?

[budimir]

This is the result:

 

"kalkulacija_id kataloski_broj VPC MPC datum_kalkulacije naziv klasifikacija marza banka spedicija carina tecaj prosjecni_rabat max_rabat nabavana "

 

But, everything is generated in column A. I would like it to be :

 

kalkulacija_id - Column A

kataloski_broj - Column B

VPC - Column C

...

[MatthewJ]

Something like this maybe?

 

<?php

$host = 'localhost';
$user = 'xxxx';
$pass = 'xxxx';

$conn  = mysql_connect($host, $user, $pass);
$db = mysql_select_db('test', $conn);

$output = "";

$sql = "SELECT * FROM yourtable";
$res = mysql_query($sql, $conn);
$row = mysql_fetch_array($res);
$headers = mysql_num_fields($res);
$data = mysql_num_rows($res);

for($i = 0; $i<$headers; $i++) {
$colname = mysql_field_name($res, $i);
$output .= $colname."\t";
}

$output .= "\r\n";

do {

  for($i = 0; $i<$data; $i++) {
  $field = $row[$i];
  $output .= $field."\t";
  }
  
$output .= "\r\n";

} while ($row = mysql_fetch_array($res));

$fh = fopen('output.txt', 'w');
fwrite($fh, $output);

?> 

[budimir]

Something like that, but I'd like to export it to Excel CSV file format.

 

Every column from MySQL should be in separate column in CSV.

 

This is the example:

 

MySQL columns: Part Number      Description      Location

to                          |                    |                    |

                            ˅                    ˅                  ˅

Excel columns:  Column A          Column B        Column C

 

Right now, the code from above is putting everything to Column A.

[abdfahim]

1st of all, u r saying you want CSV, but at the same time you said you dont want comma separated. Its little bit confusing. CSV mean comma seperated value. When you open this CSV file with MS Excel, you will not see the commas, instead you will see each comma seperated value in a new column, what you want. And if you dont want comma seperated fiiles, then go for Tab Delimited File, not CSV because you can't change definition. But in both cases, either CSV or Tab Delimited, the end result will be same, when you open with excel, each seperated value in a line will be in a new column.

 

Anyway, It would be better if you post some portion of your code bacause those value should not be placed in the same column.

[budimir]

OK, so here is the code I'm using:

 

<?php
include ("../admin/servis/include/session.php");

$baza = "servis";
$tablica = "kalkulacija";

$upit = "SELECT kataloski_broj, VPC, MPC FROM kalkulacija";
$rezultat = mysql_query($upit,$veza) or die (mysql_error());

$out = '';

//Dohvačanje naziva svih kolona
$fields = mysql_list_fields($baza,$tablica);

//Brojanje polja tablice i ubacivanje u $columns
$columns = mysql_num_fields($fields);

//Stavljanje imena svih polja u $out
for($i = 0; $i < $columns; $i++) {

$l = mysql_field_name($fields,$i);

$out .= "".$l."\t";
}

$out .= "\n";

//Dodavanje svih vrijednosti iz tablice u $out
while($l = mysql_fetch_array($rezultat)) {

//for($i = 0; $i < $columns; $i++) {
//$out .= '"'.$l["$i"].'"\t';
//}
$out .= $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\t";

$out .= "\n";

}

//Otvori file export.csv
$f = fopen('export.csv','w');

//Stavljanje svih vrijednosti u CSV
fputs($f,$out);
fclose($f);

header('Content-type:application/csv');
header('Content-Disposition:attachment; filename="export.csv"');
readfile('export.csv');

mysql_close($veza);

//header("Location:kalkulacija.php");
exit;
?>

 

But the result is put in one column. I want to separate every column for itself.

[abdfahim]

the reason is, you are making tab delimited file. So you cant name file example.csv, rather name it example.txt.

 

If you want to name file example.csv, then make csv file, that is, replace all "\t" with a comma (,)

[abdfahim]

I forgot to mention, whether you make it tab delimited and name it example.txt .... or make it comma seperated and name it example.csv, you will find the same result when you open it with Excel, exactly what you want. But dont mix those format, that is, dont make it tab delimited and name it example.csv or vice-versa.

[budimir]

OK, so can you tell me how can I export data from MySQL to Excel in that way that every column is separated in columns in Excel????

[abdfahim]

I guess making CSV or Tab delimited is much more easier and straight forward and at the same time it will surve your purpose. Do you try opening those files with Excel? You cant distinguish those with an excel file. Then why are you want to write a pure excel file (.xls) ?

 

Ofcourse you can't save any advance feature like Macro, Pivot table or formula etc in those files (though you can write it, just cant save it). If you really want to make a pure excel file, you need to use some classes. You can find many of those in internet like PhpExcel etc. I personaly use a user-defined class. But again I am teling you that, I go for pure excel on that particular case only because I need to attach some image, color some cells, make some formula ect. In all other cases where I just need the data to be stored, I always use CSV/tab delimited files.

[budimir]

OK, I'll try to see this PHPExcel tool and see what I can get.

 

Thanks for your help.

[MatthewJ]

<?php

$host = 'localhost';
$user = 'xxxx';
$pass = 'xxxx';

$conn  = mysql_connect($host, $user, $pass);
$db = mysql_select_db('yourdb', $conn);

$output = "";

$sql = "SELECT * FROM yourtbl";
$res = mysql_query($sql, $conn);
$row = mysql_fetch_array($res);
$fields = mysql_num_fields($res);
$data = mysql_num_rows($res);

for($i = 0; $i<$fields; $i++) {
$colname = mysql_field_name($res, $i);
$output .= '"'.$colname.'"';
if($i != $fields - 1) {
	$output .= ",";
}
}

$output .= "\n";

do {

  for($i = 0; $i<$fields; $i++) {
  $field = $row[$i];
  $output .= '"'.$field.'"';
  if($i != $fields - 1) {
  	$output .= ",";
  }
  }
  
$output .= "\n";

} while ($row = mysql_fetch_array($res));

$fh = fopen('output.csv', 'w');
fwrite($fh, $output);

?>  

 

That will export any sized table to .csv by changing the table name in the select statement.

 

Well, and the database name

 

It will open properly in Excel

 

Edited the last time becaus eI forgot to wrap the fields with quotes... in case there are spaces.