Create CSV from mysqldump command

Hello.
I'm Mandai, in charge of Wild on the development team.

Have you ever wanted to edit MySQL data in Excel?
In this article, we'll show you how to convert MySQL data to CSV and make it readable in Excel.

The mysqldump command is often used to write the contents of mysql to a file, but if no special specification is given, the output will be in SQL format

This is fine when transferring data to another database, but what if you want to process this file in Excel?

The mysqldump command has the --tab option to meet this need

The version of MySQL used for this test was MySQL Community Server 5.6.35, which was installed via the official MySQL repository

 

Try to get it by using the mysqldump command alone

When using the mysqldump command to obtain a CSV file, you often encounter
the following

mysqldump -uroot -p --tab=/tmp hogehoge fugafuga
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

I am writing as root, but for
some reason I am getting an error. This is because the MySQL server startup option "--secure-file-priv" is set, so files cannot be written to any directory other than the specified directory.

Let's see how to check this.
First, log in to MySQL.

mysql> select @@global.secure_file_priv; +--------------------------+ | @@global.secure_file_priv | +--------------------------+ | /var/lib/mysql-files/ | +--------------------------+ 1 row in set (0.00 sec)

 
By default, the MySQL server in the official repository allows files to be written only to the directory /var/lib/mysql-files/

No, no, that's ridiculous.
I've been able to write out SQL up until now.

I thought the same thing, but it just redirects the results of the mysqldump command to a file, doesn't it?
Mysqldump doesn't write the results to a file.

The --tab option causes mysqldump itself to write to the path specified after the "=", so writing to a path other than the one specified in "@@global.secure_file_priv" will result in an error

So, in this environment, the file output of the mysqldump command using the --tab option will look like this:

mysqldump -uroot -p --tab=/var/lib/mysql-files/ hogehoge fugafuga

 
This will output a tab-separated CSV (TSV) file to /var/lib/mysql-files/

 

Unable to access the @@global.secure_file_priv path or change @@global.secure_file_priv

There will be times like this

In that case, there is nothing you can do, so the following steps come to mind:
1. First, get the SQL using mysqldump
. 2. Import the output SQL into a controllable MySQL server.
3. Output CSV.

If you have the time, this is fine.
you don't have the time or don't want to go through the trouble
, you can use grep and sed to convert the SQL to CSV.

# tsv mysqldump -uroot -p -t [target schema] [target table] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/);$//' -e 's/^(//' -e 's/),(/)\n(/g' -e 's/)\n/\n/g' -e 's/\n(/\n/g' -e 's/,/\t/g' -e "s/'//g" > [output file name]

 

# CSV (comma delimited, enclosed in double quotes) mysqldump -uroot -p -t [target schema] [target table] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/^(/"/' -e 's/);$/"/' -e 's/),(/)\n(/g' -e "s/'//g" -e 's/)\n/"\n/g' -e 's/\n(/\n"/g' -e 's/,/","/g' > [output file name]

 The above command was tested with GNU sed 4.2.1 (CentOS 6.8).
The order of substitutions in sed is important, and depending on the data content, it may not convert properly (especially if the string contains "),("). It
can be processed with a one-liner, so give it a try.

 
That's it.