MySQL: CSV Export as File stored on the Server

In this tutorial, I will show you how you can save a MySQL table as CSV file, which will then be saved on the server. If you would rather like to export your CSV file as an automatically starting download, you should have a look into it this other tutorial.

What many do not know: for the export as a file, MySQL already has a prepared command, which is a variation of a normal SELECT command:

SELECT * 
  INTO OUTFILE 'test.csv'
  FIELDS
    TERMINATED BY ';'
    OPTIONALLY ENCLOSED BY '"'
  LINES
    TERMINATED BY '\r\n'
  FROM tabelle 
  WHERE 1

In this command, the instructions of SELECT, FROM and WHERE are not different from a normal MySQL SELECT query. After SELECT, you can as usual specify the fields you want to export. In the example, we use * to export all fields of the table. Also after FROM, as usual, we specify the table that is to be exported and behind the WHERE clause, we can define conditions for the columns to be exported like the conditions in a normal SELECT query. With "WHERE 1", again, we export the entire table. Of course, you can omit this as well here.

Difference to a normal SELECT

It becomes interesting when we look at the differences to a normal SELECT: The difference is what comes after "INTO OUTFILE". We would like to have a look at the instructions in detail:

INTO OUTFILE

Behind this command, we write down the file, in which the output should be saved. In the example, we use the file "test.csv". The file will be stored on the computer on which the database is established. If we are using XAMPP, the location would be "C:\xampp\mysql\data", for example.

It becomes more difficult here, when we are using a Shared Hoster and do not have an own server. Then the computer, where the database is located, may differ from the web space.

But we can use the PHP function getcwd() to determine the absolute path of our web space and then try to directly save in this path. For example, in the case of XAMPP, it would be "INTO OUTFILE 'c:/xampp/htdocs/pagex/test.csv'". If that does not work, we can still create a CSV file with PHP. Important: MySQL will not overwrite an existing file, the file must not exist before to make it work.

FIELDS

Behind FIELDS, we write rules according to which the fields of the CSV file should be formatted:

TERMINATED BY

Specifies the delimiter character with which individual fields are separated. A good choice would be the semicolon ";" as it is shown in the example, or you can use the comma ",".

OPTIONALLY ENCLOSED BY

In the case, a field contains our separator, the order of the CSV file would get confused. Therefore, here, we specify a character such as ", with which the fields should be enclosed. It follows, for example: "value 1", "value 2", "value 3" and so on. If we omit the "OPTIONALLY", each field will always be surrounded with the character. If we do not omit it, the values will only be framed when needed. Numbers, for example, will then remain as unquoted numbers in the file.

LINES TERMINATED BY

Also in case of the lines, we can determine by which individual lines of the CSV file should be separated. In the example, we use the word wrap of Windows "\r\n", but you can also specify any other separator or leave the command out entirely.