How do I export a table from a MySQL database to a tab delimited file for use in Microsoft Excel? .: Netfirms 24/7 Support

Since CSV exporting is disabled in phpmyAdmin, customer's may ask how to export data in order to use it with MS Excel. Please follow the directions below to export a database table to a tab delimited file. This will allow you to use the data within Microsoft Excel.

There are 2 methods for exporting your database table information into a tab delimited file to use with Excel and other database utilities.

Method #1:

1. Enable SSH on your account if your account supports SSH
2. Using an SSH client (eg. Putty), connect to your Netfirms account
3. Optional: Using the cd command change to the directory where you will output your comma-delimited file to. Otherwise the file will output to your /www folder.
4. Execute the following command (where userid is your database username, password is your mysql password, and databaseid is your database's database id - all found in your Netfirms Control Panel, Site Tools, MySQL section; table_name is the name of the table from your database you wish to back up; filename.csv is the name of the file you wish to output the results to.

echo "select * from [table_name];" | mysql -u [userid] -p[password] [databaseid] | sed -e 's/^Mn/r/g' > filename.csv

5. Using an FTP client or Netfirms File Manager, download filename.csv to your local machine

Method #2:

1. Using a text editor (eg. Notepad) or Netfirms File Manager, create a file called csv.php.
2. Click here and copy and paste the code to your csv.php file.
3. Run the csv.php script in your browser to export the tables (eg. browse to yourdomain.com/csv.php where yourdomain.com is your registered domain hosted at Netfirms).
4. Optional: If you would like to schedule this to perform daily backups of your tables, you may create a cron job or scheduled task.

How do I export a table from a MySQL database to a tab delimited file for use in Microsoft Excel?

Related Articles