How can I export my data from MySQL to EXCEL or CSV

PHP
1

Hi, I need some help please how can I export my data from database to excel format ?..I have no idea how to do this.can you help me please how to achieve. This is the first time that I will export my data to excel.

Thank you in avdvance,

2

Do you want to export it via a web page (i.e create a CSV file dynamically using PHP so that your user can download it), or do you have a database containing data that you want to export and can do so via the admin page on your database server?

3

from a MySQL point-of-view, it’s a SELECT … INTO OUTFILE … statement.

4

yes I want the user can download and the downloaded file can be open in the Ms excel.Yes I have data in database and I want to export this.How can I create CSV FILE dynamically.

5

Is this for creating CSV ? by the way if the user will donwload the file is this will .xls file and can be open in MS EXCEL ?.

Thank you in advance.

6

I think you’d need a special API to create an actual xls or xlsx file, hence csv is often used as it’s really straightforward. XLS files are/were very complex and, I think, undocumented format. I am sure I read that more modern Office formats were basically xml, so that might be easier.

Once the user has data in CSV format, they can open in Excel either by double-clicking the csv file (assuming their file assocations link CSV and Excel) or if more control is required, open Excel then use the data import wizard.

7

so if I will export my data into CSV it can be opened in excel ?..how can i create CSV format ?

Thank you in advance.

8

See post #3 above, you can do it directly from the query, create an output file and then draw a link to it from your PHP.

9

The process would be to fetch the data from the database than use the csv php functions to build the export file. Depending on the size of the export it might also be smart to increase and timeout and memory limit unless running the script from the command line.

http://php.net/manual/en/function.fgetcsv.php

10

I am having error with this

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ‘\n’
FROM makes’ at line 5

mysql_query("SELECT name,meaning,gender

              INTO OUTFILE 'C:\\wamp\\www\\csv\\csv\\makes.csv'
             FIELDS TERMINATED BY ','
            ENCLOSED BY '\"\'
            LINES TERMINATED BY '\\n'
              FROM makes
              ")or die(mysql_error());
11

Okay I will this fgetcsv in php

Thank you for the links.

12

It’s actually fairly easy to generate full excel spreadsheets using PHPExcel. https://phpexcel.codeplex.com/

13

Thank you for the links,I think this will help me…
by the way can you help me in my post #10,I want to see that output.

Thank you again.

14

In your ‘Enclosed by’ specification, should you be escaping the closing single-quote? I can’t see whether it’s just confusing the forum, or whether that might confuse sql as well. Should perhaps read ‘"’ rather than '"' on that line.

Also if this is new code you want to look at mysqli or PDO functions to access the database instead of the older mysql functions that you use in the code above. Interesting to see there are functions to output directly to Excel files though.

16

Thank you :slight_smile: it works