7 min read

Exporting data to a simple CSV file

While databases are a great tool to store and manage your data, you sometimes need to extract some of the data from your database to use it in another tool (a spreadsheet application being the most prominent example for this). In this recipe, we will show you how to utilize the respective MySQL commands for exporting data from a given table into a fi le that can easily be imported by other programs.

Getting ready

To step through this recipe, you will need a running MySQL database server and a working installation of a SQL client (like MySQL Query Browser or the mysql command line tool). You will also need to identify a suitable export target, which has to meet the following requirements:

  • The MySQL server process must have write access to the target file
  • The target file must not exist

The export target file is located on the machine that runs your MySQL server, not on the client side!

If you do not have file access to the MySQL server, you could instead use export functions of MySQL clients like MySQL Query Browser.

In addition, a user with FILE privilege is needed (we will use an account named sample_install for the following steps; see also Chapter 8 Creating an installation user).

Finally, we need some data to export. Throughout this recipe, we will assume that the data to export is stored in a table named table1 inside the database sample. As export target, we will use the file C:/target.csv (MySQL accepts slashes instead of backslashes in Windows path expressions). This is a file on the machine that runs the MySQL server instance, so in this example MySQL is assumed to be running on a Windows machine. To access the results from the client, you have to have access to the file (for example, using a fi le share or executing the MySQL client on the same machine as the server).

How to do it…

  • Connect to the database using the sample_install account.
  • Issue the following SQL command:
    • mysql> SELECT * FROM sample.table1 INTO OUTFILE 'C:/target.csv'
    FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES
    TERMINATED BY 'rn';

Please note that when using a backslash instead of a slash in the target file’s path, you have to use C:target.csv (double backslash for escaping) instead.

If you do not give a path, but only a fi le name, the target fi le will be placed in the data directory of the currently selected schema of your MySQL server.

How it works…

In the previous SQL statement, a file C:/target.csv was created, which contains the content of the table sample.table1. The file contains a separate line for each row of the table, and each line is terminated by a sequence of a carriage return and a line feed character. This line ending was defined by the LINES TERMINATED BY ‘rn’ portion of the command.

Each line contains the values of each column of the row. The values are separated by semicolons, as stated in the TERMINATED BY ‘;’ clause. Every value is enclosed by a double quotation mark (“), which results from the FIELDS ENCLOSED BY ‘”‘ option.

When writing the data to the target fi le, no character conversion takes place; the data is exported using the binary character set. This should be kept in mind especially when importing tables with different character sets for some of its values.

You might wonder why we chose the semicolon instead of a comma as the field separator. This is simply because of a greatly improved Microsoft Excel compatibility (you can simply open the resulting files), without the need to import external data from the fi les. But you can, however, open these fi les in a different spreadsheet program (like OpenOffice.org Calc) as well. If you think the usage of semicolons is in contradiction to the notion of a CSV file, think of it as a Character Separated File.

The use of double quotes to enclose single values prevents problems when field values contain semicolons (or generally the field separator character). These are not recognized as field separators if they are enclosed in double quotes.

There’s more…

While the previous SELECT … INTO OUTFILE statement will work well in most cases, there are some circumstances in which you still might encounter problems. The following topics will show you how to handle some of those.

Handling errors if the target fi le already exists

If you try to execute the SELECT … INTO OUTFILE statement twice, an error File ‘C:/target.csv’ already exists occurs. This is due to a security feature in MySQL that makes sure that you cannot overwrite existing fi les using the SELECT … INTO OUTFILE statement. This makes perfect sense if you think about the consequences. If this were not the case, you could overwrite the MySQL data files using a simple SELECT because MySQL server needs write access to its data directories. As a result, you have to choose different target files for each export (or remove old files in advance).

Unfortunately, it is not possible to use a non-constant file name (like a variable) in the SELECT … INTO OUTFILE export statement. If you wish to use different file names, for example, with a time stamp as part of the file name, you have to construct the statement inside a variable value before executing it:

 

mysql> SET @selInOutfileCmd := concat("SELECT * FROM sample.table1 INTO
OUTFILE 'C:/target-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".csv' FIELDS
ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY
'rn';");
mysql> PREPARE statement FROM @selInOutfileCmd;
mysql> EXECUTE statement;

The first SET statement constructs a string, which contains a SELECT statement. While it is not allowed to use variables for statements directly, you can construct a string that contains a statement and use variables for this. With the next two lines, you prepare a statement from the string and execute it.

Handling NULL values

Without further handling, NULL values in the data you export using the previous statement would show up as “N in the resulting file. This combination is not recognized, for example, by Microsoft Excel, which breaks the file (for typical usage). To prevent this, you need to replace NULL entries by appropriate values. Assuming that the table sample.table1 consists of a numeric column a and a character column b, you should use the following statement:

mysql> SELECT IFNULL(a, 0), IFNULL(b, "NULL") FROM sample.table1 INTO
OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED
BY '"' LINES TERMINATED BY 'rn';

The downside to this approach is that you have to list all fi elds in which a NULL value might occur.

Handling line breaks

If you try to export values that contain the same character combination used for line termination in the SELECT … INTO OUTFILE statement, MySQL will try to escape the character combination with the characters defined by the ESCAPED BY clause. However, this will not always work the way it is intended. You will typically define rn as the line separators. With this constellation, values that contain a simple line break n will not cause problems, as they are exported without any conversion and can be imported to Microsoft Excel flawlessly. If your values happen to contain a combination of carriage return and line feed, the rn characters will be prepended with an escape character (“rn), but still the target file cannot be imported correctly. Therefore, you need to convert the full line breaks to simple line breaks:

mysql> SELECT a, REPLACE(b, 'rn', 'n') FROM sample.table1 INTO OUTFILE
'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY 'rn';

With this statement, you will export only line breaks n, which are typically accepted for import by other programs.

Including headers

For better understanding, you might want to include headers in your target fi le. You can do so by using a UNION construct:

mysql> (SELECT 'Column a', 'Column b') UNION ALL (SELECT * FROM sample.
table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY
';' ESCAPED BY '"' LINES TERMINATED BY 'rn');

The resulting file will contain an additional first line with the given headers from the first SELECT clause.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here