From: Erik Veenstra <erikveen@...>
Date: 2006-07-23T19:09:56+09:00
Subject: Re: converting a text file into an "insert into ..." file

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

----------------------------------------------------------------