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