Uploading and Extracting data from mysql as csv

Extract data as csv from mysql table 

From the termial run the following command (you may need to unformat  this if you are copying this command, the quotes may appear in different format),

mysql -u root -pspace123 -e “SELECT * from employee” mydb > asd.csv                                                                         The above command export data from the table employee of the database mydb into a csv file called asd.csv . By default the delimiter would be tab in this csv file and this can be overidden. Also note that there no space after -p. The password is expected be written along with -p attribute unlike what we do for -u. Otherwise error will be thrown.

asd.csv                                                                                                                                                                                   empno ename
1              ram
1000       Jeena

In this, the header row can be omitted by specifying -N parameter along with the previous command

mysql -N -u root -pspace123 -e “SELECT * from employee” mydb > asd.csv

asd.csv
1               ram
1000        Jeena

Upload csv into mysql table                                                                                                                                                        For this, the only requirement is that we need to have a table in the database with the same and structure as that of the csv file we are going to upload. In my case I have an employee.csv file and table with same name employee and same structure.

employee.csv
1;ram
1000;Jeena

Now type the following command from the terminal,

mysqlimport -u root -pspace123 –fields-terminated-by=’;’ –local mydb employee.csv

This would upload this csv file to the table employee and that can be viewed    from mysql console

mysql> select * from employee;
Screenshot from 2016-06-04 12:52:07

Upload csv file into Remote Table

mysqlimport -h remote-host-name –port 3306 -u username -ppassword –fields-terminated-by=’;’ –local remote-db-name filename.csv-on-local-machine

mysqlimport -h example.com –port 3306 -u testUser -ptestUser123 –fields-terminated-by=’;’ –local demo test.csv

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s