Page 1 of 2

MySQL Query to CSV File - Please show me how

Posted: Fri Oct 08, 2010 6:07 pm
by diseman
Hi All,

Been Google'ing this for a while now, but can't seem to find a SIMPLE solution to this.

Does anyone have some SIMPLE code that shows a basic query that outputs to a CSV file and has the column headers as well? Ideally, I would like to customize the column header names, but I don't know how much more difficult that will make this request for help.

If possible, could you include the following in any example you might have:

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY

Seeing that a lot, so it seems crucial.

Thank you in advance for your time and any help...

Re: MySQL Query to CSV File - Please show me how

Posted: Fri Oct 08, 2010 6:45 pm
by twinedev
Off the top of my head, (ie. not tested), try:

Code: Select all

$fp = fopen('myfile.csv','w');

$rsFields = mysql_query('DESCRIBE tblData');
$aryFields = array();
if ($rsFields && mysql_num_rows($rsFields)>0) {
	while ($aryTemp = mysql_fetch_assoc($rsFields)) {
		$aryFields[] = $aryTemp['Field'];
	}
	mysql_free_result($rsFields);
}
fputcsv($fp,$aryFields); // See http://php.net/fputcsv for options
unset($rsFields,$aryFields);

$rsData = mysql_query('SELECT * FROM tblData');
if ($rsData && mysql_num_rows($rsData)>0) {
	while ($aryTemp = mysql_fetch_assoc($rsData)) {
		fputcsv($fp,$aryTemp); // See http://php.net/fputcsv for options
	}
	mysql_free_result($rsData);
}
unset($rsData);

fclose($fp);

Re: MySQL Query to CSV File - Please show me how

Posted: Fri Oct 08, 2010 8:04 pm
by mikosiko
if your server and client are the same machine then it should do it

Code: Select all

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
example taken from here
http://dev.mysql.com/doc/refman/5.0/en/select.html

scroll down there and read all the available options.

Re: MySQL Query to CSV File - Please show me how

Posted: Fri Oct 08, 2010 8:07 pm
by diseman
Hey TwinDev,

That was good stuff! It was a lot more complicated then I was looking for, but it worked like a champ first time 'round. Thank you.

Just a quick question since you seem to know about this stuff. I'm just thinking down the line....

When you submit your .csv file for someone to import into their db, do they typically map the fields for importing or do I name the column headers to their system, so they can import?

2nd question: Assuming it's me, does that mean I have to rename my column headers or would another script have to be written to do this importing?

It's not that I have to do it. I'm just learning PHP and wondering what the typical procedure, if there is one, might be.

Last question. What if you had to export more than one table into one .csv file. How do you do that?

If/when you have the time, I would be grateful for your thoughts.

Thanks again...

Re: MySQL Query to CSV File - Please show me how

Posted: Fri Oct 08, 2010 9:40 pm
by Christopher
SELECT INTO OUTFILE will be much, much faster.

Re: MySQL Query to CSV File - Please show me how

Posted: Sat Oct 09, 2010 10:00 am
by diseman
The SELECT INTO OUTFILE is a MYSQL command to be entered into the db, right?

I'm looking to create a .CSV file from the website with a click of a button.

I like the SELECT INTO OUTFILE better as it's certainly more simple and less code, but can that be made to work from a php page?

I tried reconstructing it to more of a php-like command, but didn't have any luck getting it to work.

Thanks.

Re: MySQL Query to CSV File - Please show me how

Posted: Sat Oct 09, 2010 11:12 am
by twinedev
If you are running the database on the same machine as the webserver, you should be able to do this, you will need to give ti the full path to a location that apache can server files from.

I'm used to working in environments with dedicate mySQL servers, so that is why I went the route I did.

-Greg

Re: MySQL Query to CSV File - Please show me how

Posted: Sat Oct 09, 2010 11:43 am
by Christopher
diseman wrote:The SELECT INTO OUTFILE is a MYSQL command to be entered into the db, right?

I'm looking to create a .CSV file from the website with a click of a button.

I like the SELECT INTO OUTFILE better as it's certainly more simple and less code, but can that be made to work from a php page?

I tried reconstructing it to more of a php-like command, but didn't have any luck getting it to work.

Thanks.
I think the process is just uniqid, query, output, delete. Full code might look something like this:

Code: Select all

$dir = '/tmp/';
$file = $dir . uniqid() . '.csv';
$db->query("SELECT a,b,c INTO OUTFILE $file FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'  FROM mytable");
// the below is taken from manual
if (file_exists($file)) {
    // set headers for file download
    header('Content-Description: File Transfer');
    header('Content-Type: text/csv');       // depending on users maybe try 'application/vnd.ms-excel'
    header('Content-Disposition: attachment; filename='.basename($file));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    // output file to browser
    readfile($file);
    // delete temp file
    unlink($file);
}

Re: MySQL Query to CSV File - Please show me how

Posted: Sun Oct 10, 2010 7:03 pm
by diseman
Thanks Christopher, but that didn't work for me.

I'm having some success with TWINDEV's code, but still a couple issues:

1. Is that much code really needed to output table info to a csv file?
2. Can't seem to get more than one table in the code to output
3. When I customize which columns I want, my output doesn't align properly with the column header

Doesn't anyone have some code where they have multiple tables and custom columns selected for output that's simple to edit/customize on this end?

Thank you.

Re: MySQL Query to CSV File - Please show me how

Posted: Sun Oct 10, 2010 7:17 pm
by mikosiko
diseman wrote:Thanks Christopher, but that didn't work for me.
what part didn't work?... and why?... error message?... something?

Re: MySQL Query to CSV File - Please show me how

Posted: Mon Oct 11, 2010 12:41 am
by Christopher
My questions too! ;)

The code I posted is short and most of it is setting headers properly (per the manual). There are only three lines that do the actual DB to CSV to download. And now you are on to multiple tables and columns...

Re: MySQL Query to CSV File - Please show me how

Posted: Mon Oct 11, 2010 12:42 pm
by diseman
The problem I'm getting is a parse error on line 3 to start. I assumed it was the part about 'LINES TERMINATED BY '\n' ', but when I took it out, I then got the same error on line 7:

Here's what I used:

Code: Select all

$dir = '/tmp/';
$file = $dir . uniqid() . '.csv';
$db->query("SELECT user, username INTO OUTFILE $file FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'  FROM contact_info");
// the below is taken from manual
if (file_exists($file)) {
    // set headers for file download
    header('Content-Description: File Transfer');
    header('Content-Type: text/csv');       // depending on users maybe try 'application/vnd.ms-excel'
    header('Content-Disposition: attachment; filename='.basename($file));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    // output file to browser
    readfile($file);
    // delete temp file
    unlink($file);
} 
Sorry for the delay in getting back.

After the second error, I just felt like the first solution provided might be the simplest.

Not sure what you're meaning is Christopher when you say " and now you are on to multiple tables and columns..."

I just realized that in most cases, I would think people want to output data from multiple tables where the columns might not match. For example. In my learning website, I've got 9 tables for data. The data for one user is spread throughout all 9. I just want to learn how to customize an output for one user that spans across 9 tables. Granted, if someone were to help they don't have to cover 9 tables in any example code they might share; maybe just two tables would show a pattern for me to follow. I'm pretty good at inferring where the change needs to be made in order to work for me.

When I said I was wondering if that was the shortest code, I only meant that often I see code from different people where some is very long while others are very short and concise. Since I'm just a beginner and don't know if you're code is in fact the shortest amount of code necessary, what I meant to ask from everyone was... is there anything that will achieve the same outcome with less code? Not sure if you're taking my post personal or if I'm just feeling like you are, but nothing was meant by it. And, if my post sounded short - I'm sorry. I must admit... I was getting a little frustrated with not being able to find anything myself on the Internet that was clear.

So, I hope there are no misunderstandings

Re: MySQL Query to CSV File - Please show me how

Posted: Mon Oct 11, 2010 1:37 pm
by Christopher
diseman wrote:The problem I'm getting is a parse error on line 3 to start. I assumed it was the part about 'LINES TERMINATED BY '\n' ', but when I took it out, I then got the same error on line 7:
I just copied that SQL from mikosiko's post above. Posted code is usually not tested. You should check the mysql manual for the exact syntax for SELECT INTO OUTFILE. I always get the query working manually (in a MySQL client) before putting it in the code.
diseman wrote:Not sure what you're meaning is Christopher when you say " and now you are on to multiple tables and columns..."
It seemed like you had not got the basic CSV export working, yet you were talking about tables and rows. Get the basics working first.

Re: MySQL Query to CSV File - Please show me how

Posted: Mon Oct 11, 2010 1:47 pm
by diseman
I did get the basic CSV exporting working fine with the code TWINDEV provided. That worked flawlessly from the beginning. I just wasn't having any luck expanding it to multiple tables and custom fields that I wanted to export. Not sure how to do it.

Yes, I'll take a look at the manual and see what I can get out of it.

Thank you.

Re: MySQL Query to CSV File - Please show me how

Posted: Mon Oct 11, 2010 2:29 pm
by mikosiko
Christopher wrote:I just copied that SQL from mikosiko's post above
so now I'm the culprit? :) nice... :)

Diseman: as Christopher said... the basic task for you when somebody provide code/examples is at least read the manual/links that usually are provided too (as I did in my first post) and after read and understand what has been posted for you, you must try your code and be able/prepared to debug it before give up so easily.

per example:
in your code you are showing this line:

Code: Select all

$db->query("SELECT user, username INTO OUTFILE $file FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'  FROM contact_info");


and you got a parse error... did you check/analyze why?... let me give you a clue... look for the usage of " inside of that sentence.. :wink:

P.S.: and before you ask... no ... my answer is nothing personal :) and there is not misunderstandings from my side :)

and for export multiples tables... is just a matter to change the query and name of the $file inside of the $db_query()...