Export a CSV file from MySQL Command Line

Updated November 2024: Stop getting error messages and slow down your system with our optimization tool. Get it now at this link

1. Download and install the repair tool here.
2. Let it scan your computer.
3. The tool will then repair your computer.

When you add online purchases to your website, it may not immediately integrate with your accounting or inventory systems. This means that until you can set up an automated interface, you may need to manually synchronize data between systems by exporting data from the site’s MySQL database in generic CSV format, the de facto standard format for the lowest file layout, and then importing it into your accounting or inventory management systems.

Exporting MySQL in CSV with phpMyAdmin

The phpMyAdmin tool provides a graphical interface for managing your MySQL databases. You can use it to export each of its tracked databases to a CSV file.

• Start by connecting to phpMyAdmin.
• Then click on the Databases button in the top banner.
• In the list of databases, click on the link of the database you want to export. In this example, we have selected the user database.
• The next screen displays a list of the tables in this database. Check the boxes corresponding to the tables you want to export.
• Click on the “Export” button in the banner.
• Leave the export method as is. Select CSV from the Format drop-down menu, then click Go.
• A dialog box appears, asking you to specify the location of the CSV file.

From the MySQL Command Line

If you only have command line access to the MySQL instance and no access to the server itself (for example if it is not managed by you, as in the case of Amazon RDS), the problem is a bit more delicate. When you are on the server, you can use the fields ending with “,” to create a comma-separated list, the MySQL CLI separates by default with tabs.

Just type a query from the command line and pass it to a:

• mysql -u root -e “select * from database;” > output.tsv
• Since MySQL output is tab-delimited, it is called a TSV file, for “tab-delimited values”, and can work in some programs like spreadsheets instead of your CSV file. But it is not a CSV file, and converting it to a CSV file is complicated.

You could simply replace each tab with a comma, which would work, but fail if there are commas in the input data. If you are 100% sure that there are no commas in your TSV file (check with grep), you can replace the tabs with sed :

sed “s/\t/,/g” output.tsv > output.csv

However, if your data contains commas, you must use a much longer regex :

sed “s/’/\’/;s/\t/\”,\”/g;s/^/\”/;s/$/\”/;s/\n//g” output.tsv > output.csv

This allows you to correctly bypass the fields in quotation marks, which solves the comma problem.

Note: The tab character \t is not a standard. It is not available on MacOS and BSD, causing a jumble of every lowercase “t”, which causes sed to insert incorrect commas. To solve this problem, you should use a literal tab character instead of \t :

sed “s/ /,/g” output.tsv > output.csv

If your input data contains tabs, you are out of luck and have to create a CSV file yourself using a scripting language.

Importing into MySQL

Now that you have learned how to export a MySQL database backup, we will see how to reverse the process and import the backup into an existing database.

As you can imagine, in addition to the mysqldump command used for exporting, there is a similar mysqlimport command for importing.

In most cases, importing is simply a matter of passing options that are virtually identical to the mysqlimport command. To import our saved books.sql file that we created earlier, we would use many of the same flags and much of the same syntax.

$ mysqlimport -u book_admin -p books_production ~/backup/database/books.sql
Enter the password:
As before, the -u and -p flags are required for authentication, followed by the name of the database we want to import into (in this case we will use a different and productive database), and finally the fully qualified path to the SQL dump file containing our import data, ~/backup/database/books.sql. Also note that mysqlimport does not need the < or > (STDIN/STDOUT) symbols used with mysqldump.

Conclusion

Developers are often asked to export data from a MySQL database in order to deliver it to a customer. In most cases, when tools such as phpMyAdmin are available, the data is exported via the web tool.

However, if such tools are not available, developers will most likely turn to their development skills and start writing scripts.

https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format

Expert Tip: This repair tool scans the repositories and replaces corrupt or missing files if none of these methods have worked. It works well in most cases where the problem is due to system corruption. This tool will also optimize your system to maximize performance. It can be downloaded by Clicking Here