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
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
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.
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
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