CODEWITHSUNDEEP
ruby
FlameDra
FlameDra

Reputation: 2087

Save data from text file to SQL database

I have some data in a text file in the following format:

Cat 3 24 2 2 4 6 4 7 6 0 0
Dog 18 0 8 2 42 6 6 4 5 8 0 
Mouse 8 1 2 2 4 6 4 7 6 0 0
Rabbit 8 10 8 26 46 2 6 7 6 10 0

I would like to write a Ruby script which can take this data and store it in SQLite database where the table's columns will be : Name, Value1, Value2, Value3,...., Value11. And the rows are each piece of data on every line of the file, so the first row of the table will be: Cat, 3, 24, 2, 2, 4, 6, 4, 7, 6, 0, 0.

I have my class for Data set up as follows:

require 'dm-core'
require 'dm-migrations'


DataMapper.setup(:default, "sqlite3://#{Dir.pwd}/development.db")

class Data
  include DataMapper::Resource
  property :id, Serial
  property :name, String
  property :value1, Integer
  property :value2, Integer
  property :value3, Integer
  property :value4, Integer
  property :value5, Integer
  property :value6, Integer
  property :value7, Integer
  property :value8, Integer
  property :value9, Integer
  property :value10, Integer
  property :value11, Integer
end

DataMapper.finalize
DataMapper.auto_upgrade!

In my app.rb I'm trying to write down the logic that can parse this data and create and write it to the database. I'm also using Sinatra for this. How do I go about doing that?

EDIT: Updated app.rb file

require 'sinatra'
require 'slim'
require 'sass'
require './Names'
require 'rubygems'
require 'data_mapper'

get '/' do

  File.readlines("data.txt").each do |line|
    name, *values = line.split
    params = {:name => name}
    values.each_with_index do |value, i|
      params[:"value#{i+1}"] = value
    end
    my_data = Names.new params #class renamed to Names
    my_data.save
  end

end

When I do this I get a NoMethodError - undefined method `validators' for Names:Class.

Upvotes: 1

Views: 108

Answers (1)

Eric Duminil
Eric Duminil

Reputation: 54263

This is a start :

File.readlines('data.txt').each_with_index do |line, i|
  name, *values = line.split
  values.map!{|v| v.to_i}
  puts i
  puts name
  puts values.inspect
end

You get the index, the name and the values for each row.

It returns :

0
Cat
[3, 24, 2, 2, 4, 6, 4, 7, 6, 0, 0]

1
Dog
[18, 0, 8, 2, 42, 6, 6, 4, 5, 8, 0]

2
Mouse
[8, 1, 2, 2, 4, 6, 4, 7, 6, 0, 0]

3
Rabbit
[8, 10, 8, 26, 46, 2, 6, 7, 6, 10, 0]

Now that you have the desired variables, you can write them into your database. To do so, you can create a params Hash with the expected attributes for your Data :

File.readlines('data.txt').each do |line|
  name, *values = line.split
  params = {:name => name}
  values.each_with_index do |value, i|
    params[:"value#{i+1}"] = value
  end
  my_data = MyData.new params
  my_data.save
end

puts MyData.count # => 4

NOTE: Do not call your class Data, it is already defined in Ruby and you will get weird errors.

Stand-alone Example

Everything put together :

require 'dm-core'
require 'dm-migrations'

# Initialize Database

DataMapper.setup(:default, "sqlite3://#{Dir.pwd}/development.db")

class MyData
  include DataMapper::Resource
  property :id, Serial
  property :name, String
  property :value1, Integer
  property :value2, Integer
  property :value3, Integer
  property :value4, Integer
  property :value5, Integer
  property :value6, Integer
  property :value7, Integer
  property :value8, Integer
  property :value9, Integer
  property :value10, Integer
  property :value11, Integer
end

DataMapper.finalize
DataMapper.auto_upgrade!

# Parse file and save data in DB

File.foreach('data.txt') do |line|
  name, *values = line.split
  params = { name: name }
  values.each_with_index do |value, i|
    params[:"value#{i + 1}"] = value
  end
  MyData.create params
end

puts MyData.count # => 4, then 8, then 12, ...

Upvotes: 1

Related Questions