Export MySQL Query Result to CSV via Command Line

Posted on

This is another article which is used to describe on generating file through remote query execution. In this context, an additional tool called sed is used to manipulate the content of query result so that it can be viewed as a suitable csv file.

This article has the same purpose with the other article titled ‘Retrieving Result from Remote Executable MySQL Database Query to File‘ and another one titled ‘Remote Execute MySQL Query via Command Line‘. Below is the sample pattern of command which can be executed to achieve the purpose :

user@hostname:~$ mysql -u username -h xxx.xxx.xxx.xxx -p -B -e "select * from ci_sessions" -p dbname | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > /home/username/tester-file.csv
Enter password:
user@hostname:~$ vim tester-file.csv

Description  :
mysql : It is a command executed to call mysql utility
-u : It is an additional parameter to specify username to be used to connect to MySQL Database Server
username : It is the value of -u parameter, in this context the value is username. It is used as an additional MySQL Database Server connection parameter.  
-h : It is an additional parameter to specify host to be used to connect to MySQL Database Server
xxx.xxx.xxx.xxx : It is the value of -h parameter, in this context the value is xxx.xxx.xxx.xxx. It is used as an additional MySQL Database Server connection parameter. 
-p : It is used as an additional parameter for activating interactive password request to log in to MySQL Database Server
-B : It is used as an additional parameter for printing the result of the query into whatever output specified in a column separator using tab as the separator with each line as one row of record. With this option mysql doesn't use the history file. It can also specified as --batch 
-e : It is used as an additional parameter to pass query which is going to be executed. It can also assume that the 'e' itself stands for execute in order to be easily remembered. 

For an example :

In the above pattern, it is also act as a real example. It is a command used to get all records from ci_sessions table which is a default table exists in Code Igniter Framework application. The query itself is specified after -e parameter, ‘select * from ci_sessions’.

The other patterns are clearly explained including, -u additional parameter which is accompanied with the suitable user as the value to connect to MySQL Databse Server. Another one is -h additional parameter which is followed with the correct IP Address acts as the identifier of MySQL Database Server location and it is specified if it exists remotely.

Another additional parameter which is also important is -p where the primary usage is to make MySQL Database Server interactively provide password when it is trying to be accessed.

There is a pipe sign, after mysql command, specified with ‘|’ sign. It is used to redirect output of the command specified before the ‘|’ sign to be directed to any command or media defined after the ‘|’ sign.

The command after the ‘|’ sign is sed command. The ‘sed’ itself stands for stream editor. This is detail of sed command :

s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g

This is the general pattern of substitute command :

s/old_pattern/new_pattern/

The first part : s/’/\’/;

s stands for substitute, the above part means replace the first part with the second part. Replace ‘ with \’.

The second part : s/\t\”,\”/g;

s stands for substitute, the above part means replace the first part with the second part. Replace all \t which is character symbolizes tab with \”,\” or “,”. In this context, there is another additional parameter which is ‘/g and it stands for global. The /g or global context means replace every character in the whole part of output.

The third part : s/^/\”/;

s stands for substitute where the above part means place  \”” or ” in the begining of the line, character ^ is a regular expression character identifying the beginning of the line.

The fourth parth : s/$/\”/;

s stands for substitute where the above part means place  \”” or ” in the end of the line. the character $ is a regular expression character identifying the end of the line.

The fifth part : s/\n//g

s stands for substitute where the above part means replace \n or new line character with nothing for every sign met in the whole part of output.

The sample case for example is taken by retrieving the content of table ci_sessions available in Code Igniter Framework web-based application.

Below is the output of the file if it is normally executed without sed command to remodel it into a CSV display format. This is the command executed :

mysql -uroot database_name -p -B -e "select * from ci_sessions into outfile '/var/lib/mysql-files/test-ci-sessions.csv' fields terminated by ',' enclosed by '\"' lines terminated by '

Below is the content of test-ci-sessions.csv file located in /var/lib/mysql-files :

session_id      ip_address      user_agent      user_data       last_activity
68425dd410a8a22d92543aaba4c130db        xxx.xxx.xxx.xxx Mozilla/5.0 (Windows NT 6.1; WOW64; rv:29.0) Gecko              1402044289
cc3a2c3cb30124070e6c7d178c1a5e3e        xx.xxx.xx.xx    Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww              1402516491
211260689f0fa8eea1c847b5c8a47a1f        xx.xxx.xx.xx    Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS              1403638946
74e61fd3bd80bb12fff1ec64f9b44d0e        xx.xxx.xx.xx    Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS              1399906204
6337d725db0d31b1f0c8cdae2817ac98        xx.xxx.xx.xxx   Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww              1398496769
75c09573148a85611d22290a4ecbea1b        xx.xxx.xx.xx    Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww              1400689934
9e1d9c148abab728ef79388540172b3b        xx.xxx.x.xx     Mozilla/5.0 (Windows NT 5.1; rv:29.0) Gecko/201001              1401098090
bbebe6d0e4bbfd1fc617e043956bc217        xx.xxx.xx.xx    Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww              1399007426
b8c5afa8d5375aaa7d6467191771bf59        127.0.0.1       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G              1405167152
12abe207cdc22751b39ccc27b06ba9cf        127.0.0.1       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G              1405167164
bb4f0c298644597bda9e6caa5cdef364        127.0.0.1       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G      a:13:

On other hands, below is the output of the display if it is modified with sed to be able to construct it into a CSV display format. This is the command executed :

mysql -uroot -p -B -e "select * from ci_sessions" database_name | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > /home/user/testing-sample.scv

Below is the content of file ‘testing-sample.csv’ located in /home/user :

"session_id","ip_address","user_agent","user_data","last_activity"
"68425dd410a8a22d92543aaba4c130db","xxx.xxx.xxx.xxx","Mozilla/5.0 (Windows NT 6.1; WOW64; rv:29.0) Gecko","","1402044289"
"cc3a2c3cb30124070e6c7d178c1a5e3e","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1402516491"
"211260689f0fa8eea1c847b5c8a47a1f","xx.xxx.xx.xx","Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS","","1403638946"
"74e61fd3bd80bb12fff1ec64f9b44d0e","xx.xxx.xx.xx","Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS","","1399906204"
"6337d725db0d31b1f0c8cdae2817ac98","xx.xxx.xx.xxx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1398496769"
"75c09573148a85611d22290a4ecbea1b","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1400689934"
"9e1d9c148abab728ef79388540172b3b","xx.xxx.x.xx","Mozilla/5.0 (Windows NT 5.1; rv:29.0) Gecko/201001","","1401098090"
"bbebe6d0e4bbfd1fc617e043956bc217","xx.xxx.xx.xx","Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww","","1399007426"
"b8c5afa8d5375aaa7d6467191771bf59","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","","1405167152"
"12abe207cdc22751b39ccc27b06ba9cf","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","","1405167164"
"bb4f0c298644597bda9e6caa5cdef364","127.0.0.1","Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:28.0) G","a:13:

As shown in the above output, it is obviously based on the sed command pattern, each field is enclosed with “” and each field is separated with ‘,’ (comma sign).

One thought on “Export MySQL Query Result to CSV via Command Line

Leave a Reply