RECOMMENDED: Click here to fix Windows errors and optimize system performance
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.
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.
RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.