Skip to main content

Shell Script to export MySQL table data into a text file

Hi, our today's topic is "Write Shell Script to export MySql table data into a text file". I will try my best to explain each and everything in steps. Let's start :

Following is full shell script

#tableexport.sh
 
#(1) define variables
DBNAME=test
FNAME=/usr/tmp/$(date +%Y.%m.%d)-$1.txt

#(2)creates an file with column names seperated by comma in a single line
mysql -u$2 -p$3 $DBNAME -B -e "SELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name = '$1';" | awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' > $FNAME

#(3)appends newline to mark beginning of data vs. column titles
echo "" >> $FNAME

#(4)dumps data from DB into /usr/tmp/tempfile.txt
mysql -u$2 -p$3 $DBNAME -B -e "SELECT * INTO OUTFILE '/usr/tmp/tempfile.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM $1;"

#(5)merges data file and $FNAME file column names
cat /usr/tmp/tempfile.txt >> $FNAME

#(6)deletes tempfile
rm -rf /usr/tmp/tempfile.txt

You will call the script using following nomenclature.
sh   tableexport.sh  table_name  db_username  db_password

I will be using a table named bookmarks of test database for explanation.
Let’s discuss each and every step now.

I will be using a table named bookmarks of test database for explanation.

Let’s discuss each and every step now.

1)    In first step we have defined two variables.
       
       DBNAME  : It represents the database name
       FNAME : It specifies the file name and it’s location that we have to populate
   
2)    Second step is the most important one. It would extract Column names from the table
       and would write them into a single line separated by commas. One more thing to pen
       down is that to run this script file we have to pass three arguments
   
        a) Table Name b) Database Username and c) Database Password

       So in this second step $1 represents the 1st argument means table name, $2 represents
       the 2nd argument means database name and $3 represents the 3rd argument means
       database password. 
   
       Let’s go into more detail. I will divide the whole command into parts and explain
       it further.
   
       [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
       FROM  information_schema.COLUMNS C WHERE table_name = 'bookmarks';"
   
        This command will fetch the column information of bookmarks table of test database
        and the output  would be
   
        //output starts here
       COLUMN_NAME
       bookmark_id
       bookmark_name
       bookmark_url
       created_date
       modified_date
       is_active
       //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';"
      | awk '{print $1}'
   
      Ask is an important unix utility. Here in out example it would read first word of the line
      and print it. As in out case whole column name is a single word so output would be same.
   
      //output starts here
      COLUMN_NAME
      bookmark_id
      bookmark_name
      bookmark_url
      created_date
      modified_date
      is_active
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" |
      awk '{print $1}' | grep -iv   ^COLUMN_NAME$
   
      It would display all the columns except the COLUMN_NAME. So output would be
   
      //output starts here
      bookmark_id
      bookmark_name
      bookmark_url
      created_date
      modified_date
      is_active
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" | 
      awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g'
   
      sed is again an important utility of unix.  In our example it would replace starting of the
      line (represented by ^) and end of the line (represented by $) by double quotes. Here
      g specifies the global substitute otherwise it would work only for first line.
     
      Ouput would be :
   
      //output starts here
      “bookmark_id”
      “bookmark_name”
      “bookmark_url”
      “created_date”
      “modified_date”
      “is_active”
      //output ends here
   
      [dirtyhandsphp@blog shiv]$ mysql –u$2 –p$3 test -B -e "SELECT COLUMN_NAME
      FROM information_schema.COLUMNS C WHERE table_name = 'bookmarks';" |
      awk '{print $1}' | grep -iv  ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' |
      sed 's/\(.*\),/\1./' > $FNAME
   
      Tr : Translate characters.  In this case it would replace ‘\n’ means new line character
      with comma.
   
      We have again used sed here. It would replace the last comma with space. So the final
      output of this step would be
   
      //output starts here
      “bookmark_id”,“bookmark_name”,“bookmark_url”,“created_date”,“modified_date”,
      “is_active”
      //output ends here
   
      It would be written to the $FNAME file.

3)   As the documentation says it would just put us in next line so that we can copy other
      data in $FNAME file’s next line. 

4)   Next step would just get the content from table, would separate them by comma and
      enclosed by double quotes and put them in a file /usr/tmp/tempfile.txt.

5)   5th step would append the tempfile.txt file data into $FNAME.

6)   Will delete temporary file : tempfile.txt.


Thanks!!!!!!!!!! Enjoy Programming :)          



Comments

Popular posts from this blog

Odoo: Qweb Templates Cache Issue

Hi Everyone, Today I was working on a hotel booking template in Odoo and changes made in the Qweb template(.xml) were not reflecting on updating module with odoo service restart. Tried few times without any success :(. Finally, I decided to delete the old template from Odoo backend and then update module again and it worked like a charm!! :) Steps to delete template from backend: Settings --> User Interface --> Views --> Search by template name Delete the used template. Although it's a small post, but I'm sure it will meet the purpose and help someone in the future. Thanks!!! Enjoy Programming!! :) Reference Links: =================================== https://www.odoo.com/forum/help-1/question/odoo-cache-and-updating-qweb-templates-84492

Odoo12/13: Add an extra action in the tree view action button

This post will help you to add an action window in Odoo. Odoo13: <act_window id="action_make_bank_payment"              name="Do Bank Payment"              res_model="account.ob.payment"              binding_model="account.move"              view_mode="form"              target="new"              binding_views="list,form"              domain="[('type','=', 'in_invoice')]"              groups="account.group_account_user" /> Odoo12: <act_window id="action_make_bank_payment"              name="Do Bank Payment"              res_model="account.ob.payment"             view_mode="tree,form"              domain="[('type','=', 'in_invoice')]"              groups="account.group_account_user" /> Thanks!!! Enjoy Programming!! :) Reference: https://www.odoo.com/es_

Odoo/OpenERP: one2one relational field example

one2one relational field is deprecated in OpenERP version>5 but you can achieve the same using many2one relational field. You can achieve it in following two ways : 1) using many2one field in both the objects ( http://tutorialopenerp.wordpress.com/2014/04/23/one2one/ ) 2)  using inheritance by deligation You can easily find the first solution with little search over internet so let's start with 2nd solution. Scenario :  I want to create a one2one relation between two objects of openerp hr.employee and hr.employee.medical.details What I should do  i. Add _inherits section in hr_employee class ii. Add field medical_detail_id in hr_employee class class hr_employee(osv.osv):     _name = 'hr.employee'     _inherits = {' hr.employee.medical.details ': "medical_detail_id"}     _inherit = 'hr.employee'         _columns = {              'emp_code':fields.char('Employee Code', si