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

**URL:** <https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482>\
**Category:** Ruby\
**Created:** [July 23, 2006, 10:27am UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482 "2006-07-23T10:27:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 10:27am UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/1 "2006-07-23T10:27:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 12:01pm UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/2 "2006-07-23T12:01:39Z")

</div>

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](http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/index.html)

otherwise just

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

---

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 12:11pm UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/3 "2006-07-23T12:11:05Z")

</div>

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/](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

---

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 9:41pm UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/4 "2006-07-23T21:41:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 7:36pm UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/5 "2006-07-23T19:36:00Z")

</div>

This is great! Thank you for your input.

gk

---

<div class="post-metadata">

**Author:** ![kublai](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/a3d4f5/32.png) [@kublai](https://www.ruby-forum.com/u/kublai)\
**Post date:** [July 23, 2006, 8:46pm UTC](https://www.ruby-forum.com/t/converting-a-text-file-into-an-insert-into-file/99482/6 "2006-07-23T20:46:36Z")

</div>

Hi –

On Sun, 23 Jul 2006, removed\_email\_address@domain.invalid 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