Asp Forum - converting a text file into an "insert into ..." file

kublaikhan55

7/23/2006 8:21:00 AM

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

5 Answers

Jano Svitok

7/23/2006 10:00:00 AM

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

otherwise just

ruby -ne 'puts "insert into some_table values (#{$_.strip});"' < data.csv

On 7/23/06, kublaikhan55@hotmail.com <kublaikhan55@hotmail.com> wrote:
> 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
>
>
>

Erik Veenstra

7/23/2006 10:10:00 AM

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.erikve...

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

$ 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

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

kublaikhan55

7/23/2006 5:33:00 PM

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.erikve...
>
> ----------------------------------------------------------------
>
> $ 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
>
> ----------------------------------------------------------------

dblack

7/23/2006 6:44:00 PM

kublaikhan55

7/23/2006 7:35:00 PM

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

Jan Svitok wrote:
> In case you need escaping there is a module for parsing CSV in stdlib.
> http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/...
>
> otherwise just
>
> ruby -ne 'puts "insert into some_table values (#{$_.strip});"' < data.csv
>
> On 7/23/06, kublaikhan55@hotmail.com <kublaikhan55@hotmail.com> wrote:
> > 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
> >
> >
> >

comp.lang.ruby

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

kublaikhan55

Jano Svitok

Erik Veenstra

kublaikhan55

dblack

kublaikhan55

x Login to ForumsZone