How to export all tables data into csv or excel format in MySQL using a script or some other way ?

Question

I have the following problem. I have a production table on a server its like 12GB and what i wanted is to just pull off the data form production and import it into my local database for ease operation.

AT the end i want to submit the data in CSV or Excel format to the client. But , i need to have automated script which can import each tables data into a csv file , as naming it by the table name or something ? can you really help on this , i appreciated it?

Answer 1

Check out mysqldump with --tab option. It outputs one file per tale, naming the files according to the table names, and the contents of the file is tab-delimited text.

See http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_tab

You can then import the text files into Excel, and you can change the delimiter between fields to tab.

You may also want to import the data directly into Excel without needing to use an intermediate text file. See http://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-import.html

Answer 2

The easiest method I did after some research is to run a python script. Put all the required tables in a list and loop over the list. The following python script will export all the tables into corresponding csv files

import MySQLdb
import csv

user = '' # your username
passwd = '' # your password
host = '' # your host or localhost if running locally
db = '' # database where your table is stored
dest_folder = '' # destination folder for the files to be written

table_list = []    
for table in table_list:
    print(table)
    con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
    cursor = con.cursor()

    query = "SELECT * FROM %s;" % table
    cursor.execute(query)

    with open(folder + table + '.csv','w') as f:
        writer = csv.writer(f)
        for row in cursor.fetchall():
            writer.writerow(row)

If you get an error saying that no module named MySQLdb, then install MySQLdb using the following methods

1. easy_install mysql-python
2. pip install mysql-python
3. pip install mysqlclient 
4. apt-get install python-mysqldb (Ubuntu - my preferred OS)