From: kublaikhan55@...
Date: 2006-07-24T02:35:06+09:00
Subject: Re: converting a text file into an "insert into ..." file

This is great! Thank you for your input.

gk


Erik Veenstra wrote:
> Something like this?
>
> (It's compiled from a couple of library files. So, this script
> might not be fully tested.)
>
> gegroet,
> Erik V. - http://www.erikveen.dds.nl/
>
> ----------------------------------------------------------------
>
>  $ cat test.table
>  HEADER     NAME       DESCRIPTION
>  Waypoint   NS-Ac      NS Abcoude
>  Waypoint   NS-Ah      NS Arnhem
>  Waypoint   NS-Ahp     NS Arnhem Velperpoort
>  Waypoint   NS-Ahpr    NS Arnhem Presikhaaf
>  Waypoint   NS-Akl     NS Arkel
>  Waypoint   NS-Akm     NS Akkrum
>  Waypoint   NS-Alm     NS Almere
>  Waypoint   NS-Almb    NS Almere Buiten
>
>  $ ruby table2sql.rb test.table
>
>  $ cat test.table.sql
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Ac", "NS Abcoude");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Ah", "NS Arnhem");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Ahp", "NS Arnhem Velperpoort");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Ahpr", "NS Arnhem Presikhaaf");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Akl", "NS Arkel");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Akm", "NS Akkrum");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Alm", "NS Almere");
>  INSERT INTO test (header, name, description) VALUES ("Waypoint",
> "NS-Almb", "NS Almere Buiten");
>
>  $ cat table2sql.rb
>  class String
>    def compress
>      self.gsub(/[[:blank:]\r\n]+/, " ").strip
>    end
>  end
>
>  class Table
>    attr_reader :attributes
>    attr_writer :attributes
>
>    def self.file(file)  # The first line contains the names of the
> instance variables.
>      res        = []
>
>      File.open(file) do |f|
>        keys     = f.gets.strip.split(/(\t| {3,})/)
>        keys     = keys.collect{|x| x.downcase}
>        keys     = keys.reject{|key| key.compress.empty?}
>
>        keys.each do |key|
>          module_eval do
>            define_method("#{key}") do
>              instance_variable_get("@#{key}")
>            end
>            define_method("#{key}=") do |value|
>              instance_variable_set("@#{key}", value)
>            end
>          end
>        end
>
>        while (line = f.gets)
>          values = line.strip.split(/(\t| {3,})/)
>          values = values.reject{|value| value.compress.empty?}
>
>          object = self.new
>          object.attributes = keys
>
>          keys.zip(values).each do |k, v|
>            object.send("#{k}=", v)
>          end
>
>          if block_given?
>            res  = yield(object)
>          else
>            res << object
>          end
>        end
>      end
>
>      res
>    end
>
>  end
>
>  ARGV.each do |file|
>    infile       = file
>    outfile      = file + ".sql"
>
>    table                = File.basename(file).gsub(/\..*/, "")
>
>    File.open(outfile, "w") do |f|
>      Table.file(infile) do |o|
>        columns  = o.attributes
>        values   = columns.collect{|s| o.send(s)}
>        sql      = "INSERT INTO %s (%s) VALUES (%s);" % [table,
> columns.join(", "), values.collect{|s| "\"%s\"" % s}.join(", ")]
>
>        f.puts   sql
>      end
>    end
>  end
> 
> ----------------------------------------------------------------