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

Discussion in 'Ruby' started by kublaikhan55@hotmail.com, Jul 23, 2006.

  1. Guest

    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
    , Jul 23, 2006
    #1
    1. Advertising

  2. Jan Svitok Guest

    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

    On 7/23/06, <> 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
    >
    >
    >
    Jan Svitok, Jul 23, 2006
    #2
    1. Advertising

  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

    ----------------------------------------------------------------
    Erik Veenstra, Jul 23, 2006
    #3
  4. Guest

    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
    >
    > ----------------------------------------------------------------
    , Jul 23, 2006
    #4
  5. Guest

    Hi --

    On Sun, 23 Jul 2006, 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?


    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

    --
    http://www.rubypowerandlight.com => Ruby/Rails training & consultancy
    http://www.manning.com/black => RUBY FOR RAILS (reviewed on
    Slashdot, 7/12/2006!)
    http://dablog.rubypal.com => D[avid ]A[. ]B[lack's][ Web]log
    => me
    , Jul 23, 2006
    #5
  6. Guest

    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/index.html
    >
    > otherwise just
    >
    > ruby -ne 'puts "insert into some_table values (#{$_.strip});"' < data.csv
    >
    > On 7/23/06, <> 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
    > >
    > >
    > >
    , Jul 23, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nimesh
    Replies:
    0
    Views:
    358
    nimesh
    Feb 20, 2006
  2. Alfredo Agosti
    Replies:
    3
    Views:
    346
    Aaron Bertrand - MVP
    Sep 19, 2003
  3. Mike Green

    converting vertical text to horizontal text

    Mike Green, Feb 1, 2005, in forum: Perl Misc
    Replies:
    9
    Views:
    385
    Arndt Jonasson
    Feb 2, 2005
  4. Replies:
    2
    Views:
    163
    Tad McClellan
    Mar 22, 2005
  5. Sergio del Amo
    Replies:
    4
    Views:
    233
Loading...

Share This Page