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.

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
    @kk = "false"
    @i = 0
    @ptr_str = ""

  def begin_page(arg=nil)
    puts ""

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

  def move_to_next_line_and_show_text

  alias :super_show_text :show_text
  alias :set_spacing_next_line_show_text :show_text

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

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

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.