Monday, August 1, 2011

Truncate all tables in a particular database using a single line command.

Generally if you like to truncate or empty all the records in a database containing more no. of tables, use the below command to do it. It really helps to remove all the records completely only having the structure of the tables left in a database.

mysql -u {user-name} -p {database-name} -BNe "show tables" | awk '{print "TRUNCATE TABLE " $1 ";"}' | mysql -u {user-name} -p {database-name}

Let me know if it doesn't works you out.

Tuesday, May 17, 2011

Dumping Multiple Selected MySQL Tables as SQL


Not sure if anybody already figured out how to dump the selected mysql tables from a particular database. But after googling I found some way which helped me how to proceed and adding my thought little & attained what I need. Ok, not bugging you too much, here is my requirement:

I need to dump 5 tables (employee, address, salary, attendance, payslip) & the tables whose name starts with map_* in a single command.

Solution
What I know from the syntax is that I can append as many table name with the mysqldump to get the dump of all the tables.mysqldump -u{user} -p {database} {table1} {table2} {table3} > {destination}.sql
but what here missing is that I can add map_* tables which is really important for me to proceed further.

As said, after googling I got the site and understood how to get map_* tables include with the mysqldump.

The referred site provided me something like this..
mysql databasename -u [root] -p[password] -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump [databasename] -u [root] -p[password] > [target_file]

but what I modified to get my requirement complete is
mysql {databasename} -u {root} -p{password} -e 'show tables like "wp_153_%"' | grep -v Tables_in | xargs mysqldump {databasename} -u {root} -p{password} {table1} {table2} {table3} > {destination}.sql

So my final output was
mysql employeeDB -u root -p -e 'show tables like "map_%"' | grep -v Tables_in | xargs mysqldump employeeDB -u root -p employee address salary attendance payslip > destination.sql

Hope NO other better option than this.


Note: I have remove {password} from my command as I think we should not execute the password in the command line instead asking us

Monday, May 16, 2011

Reading PDF and converting to CSV using RubyCode & pdf-reader plugin

I wanted to convert a PDF document into a XLS table and after a couple of searches I could easily able to write the code in ruby and converted a Citibank PDF Statement to CSV file. This gave me enough relief that I learnt how to read a PDF file if its not password protected.

require 'rubygems'
require 'pdf/reader'
class PageTextReceiver
  attr_accessor :content

  def initialize
    @content=[]
    @kk = "false"
    @i = 0
    @ptr_str = ""
  end

  def begin_page(arg=nil)
    puts ""
  end

  def show_text(string, *params)
    if string.strip=="Previous Balance"
      @kk="false"
    end
    if @i==4
      puts @ptr_str
      @ptr_str = ""
      @i=0
    end
    if string.strip=="Sale Date" or @kk == "true"
      @kk="true"
      if @i==0
        @ptr_str << string + "/2009,"
      else
        @ptr_str << string + ","
      end
      if (string.reverse.index(".")==2 or string=="Amount (in Rs)")
        @i=4
      else
        @i=@i+1
      end
    end
  end

  def move_to_next_line_and_show_text
    @i=0
    show_text
  end

  alias :super_show_text :show_text
  alias :set_spacing_next_line_show_text :show_text

  def show_text_with_positioning(*params)
    params=params.first
    params.each { |str| show_text(str) if str.kind_of?(String) }
  end
end

receiver = PageTextReceiver.new
(1..45).each do | x |
  pdf = PDF::Reader.file("#{x}.pdf", receiver)
  puts receiver.content.inspect
end

The above code use to read 45 pdf files. Say the above code is saved in read_pdf.rb

Below is the command to execute the file and store in a file (which I hope the easiest way)
ruby read_pdf.rb >> a.csv
hope it helps you ? or If you know how to read a PDF which is password protected thru code, where I can input the password of the file, please let me know.

Tuesday, January 4, 2011

rmagick installation for MacOSX

Try the below following steps to install rmagick using macports.

sudo port install tiff -macosx imagemagick

If you get an error like this..

Error: Checksum (md5) mismatch for lcms-1.19.tar.gz
Error: Checksum (sha1) mismatch for lcms-1.19.tar.gz
Error: Checksum (rmd160) mismatch for lcms-1.19.tar.gz

then execute the below commands
sudo port selfupdate
sudo port clean --all lcms
sudo port install tiff -macosx imagemagick

now try this below command to really work for Rails

sudo gem install rmagick

hope it helps you ?