Converting a text file into an "insert into ..." file

Ruby
1

Hi,
I have a csv delimited file that I’d like to ‘insert into some_table
table values …’ file. Thus I have a file where each row is of the
sort:

value1, value2, value3, value4

and I’d like to convert the file into a file where each row looks
like::

insert into some_table values (value1, value2, value3, value4)

How may I efficiently do this?

Thanks for your help.
gk

2

In case you need escaping there is a module for parsing CSV in stdlib.
http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/index.html

otherwise just

ruby -ne ‘puts “insert into some_table values (#{$_.strip});”’ <
data.csv

3

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

4

This one-liner works for me very well. Thank you.
gk

5

This is great! Thank you for your input.

gk

6

Hi –

On Sun, 23 Jul 2006, [email protected] wrote:

insert into some_table values (value1, value2, value3, value4)

How may I efficiently do this?

Assuming you don’t have to do any massaging or escaping of the values,
you could do something like:

ruby -ne
‘puts “insert into some_table values (#{$_.chomp});”’ old > new

David