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;

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

Importing CSV files into MYSQL tables

For  importing a csv file into mysql table, we need to execute following command:

LOAD DATA INFILE ‘/tmp/t.csv’ INTO TABLE table1 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;

Sometimes we may face some file not found exceptions and errors and it may persist even after giving full permissions. I got an error which said t.csv file is missing even though I have place it at correct location. The fix for this issue is to change the ownwership of t.csv to mysql using the following command.

chown mysql:mysql t.csv

DATE filed and CSV imports

I had a table table2:

create table table2(ID INT,title INT NULL,dt DATETIME NULL);

t.csv :

1, 3,10/12/2000
1000, 1223,12/12/2014

And when I executed the previopus load command, I got incorrect values in mysql table table2 and which was as follows:

ID            title                    dt
+——+——-+———————+
1          3             0000-00-00 00:00:00
1000   1223        0000-00-00 00:00:00

In this case, we need to modify the date filed while uploading to database. This can be done using the following command:

LOAD DATA INFILE ‘/tmp/t.csv’ INTO TABLE table2 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (ID, title, @var1) SET dt = STR_TO_DATE(@var1, ‘%m/%d/%Y’);

ID            title                    dt
+——+——-+———————+
1          3            2000-10-12
1000   1223      2014-12-12

And if there are multiple fields which are to be modified,you can specify them in a comma separated way like,

LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' 
INTO TABLE mytable 
LINES TERMINATED BY '\n'
(id, task, hoursWorked, @var1, @var2) 
SET begindate = STR_TO_DATE(@var1, '%m/%d/%Y'),     
enddate = STR_TO_DATE(@var2, '%m/%d/%Y');

apt-get update Errors -5 – No address associated with hostname

When I tried to run sudo apt-get update, I came across something like this.

Ign http://in.archive.ubuntu.com precise InRelease
Err https://archive.cloudera.com precise-cm5.5.1/contrib amd64 Packages
Couldn’t resolve host ‘archive.cloudera.com’
Err http://archive.cloudera.com precise-cm5/contrib Sources
Something wicked happened resolving ‘archive.cloudera.com:http’ (-5 – No address associated with hostname).                    Ign http://in.archive.ubuntu.com precise-updates InRelease
Err http://archive.cloudera.com precise-cm5/contrib amd64 Packages
Something wicked happened resolving ‘archive.cloudera.com:http’ (-5 – No address associated with hostname)
Ign http://in.archive.ubuntu.com precise-backports InRelease
Err http://archive.cloudera.com precise-cm5/contrib Translation-en_IN
Something wicked happened resolving ‘archive.cloudera.com:http’ (-5 – No address associated with hostname)

 

This was a bit scary for me as it was for the first time I was facing something like this. And I even tried adding hostname of ubuntu server and  its ip address directly in my /etc/hosts file. and further to my frustration it did not work.  But The actual fix that worked for me is as follows:

 

echo “nameserver 8.8.8.8” | sudo tee /etc/resolv.conf > /dev/null                                                                                        sudo apt-get update

Then apt-get update worked perfectly.

Copy the content of a folder to another preserving permissions and metadata

cp -a /source/. /dest/
The -a option is an improved recursive option (which actually stands for cp --archive), that preserve all file attributes, and also preserve symlinks.

The . at end of the source path is a specific cp syntax that allow to copy all files and folders, included hidden ones.