Load Rails table from Text File
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

A text file contains column names and values as key values. It also contains (hard coded & embedded into the key value) the row number. I would like some ruby code to read the file and then load it into a Rails SQL table.

The key, without the instance number, is the column name in the table. In the example below there are 3 rows of data, numbered from 0 to 2. There could be 100s of rows in the real data.

For the first row as an example:

PRIMER_PAIR_0_PENALTY=10.196208

is mapped into the column:

primer_pair_penalty, with a value of 10.196208

key: PRIMER_PAIR_0_PENALTY

value: 10.196208

column: primer_pair_penalty

PRIMER_LEFT_0_PENALTY=5.064257

is mapped into the column:

primer_left_penalty, with a value of 5.064257

The same thing happens for the subsequent rows. For example:

PRIMER_PAIR_1_PENALTY=10.491267

is mapped into primer_pair_penalty on the next row, with a value of 10.491267

An example filename is P3_INPUT_577_8328_1197.raw

Text File - P3_INPUT_577_8328_1197.raw

PRIMER_PAIR_0_PENALTY=10.196208

PRIMER_LEFT_0_PENALTY=5.064257

PRIMER_RIGHT_0_PENALTY=5.131951

PRIMER_LEFT_0_SEQUENCE=TAGCTCAAGGCAGCTAAG

PRIMER_RIGHT_0_SEQUENCE=CACTCTCAGAGCCTCCTC

PRIMER_LEFT_0=23,18

PRIMER_RIGHT_0=91,18

PRIMER_LEFT_0_TM=63.936

PRIMER_RIGHT_0_TM=64.132

PRIMER_LEFT_0_GC_PERCENT=50.000

PRIMER_RIGHT_0_GC_PERCENT=61.111

PRIMER_LEFT_0_SELF_ANY_TH=0.00

PRIMER_RIGHT_0_SELF_ANY_TH=0.00

PRIMER_LEFT_0_SELF_END_TH=0.00

PRIMER_RIGHT_0_SELF_END_TH=0.00

PRIMER_LEFT_0_HAIRPIN_TH=44.79

PRIMER_RIGHT_0_HAIRPIN_TH=38.42

PRIMER_LEFT_0_END_STABILITY=6.0000

PRIMER_RIGHT_0_END_STABILITY=7.9000

PRIMER_PAIR_0_COMPL_ANY_TH=0.00

PRIMER_PAIR_0_COMPL_END_TH=0.00

PRIMER_PAIR_0_PRODUCT_SIZE=69

PRIMER_PAIR_1_PENALTY=10.491267

PRIMER_LEFT_1_PENALTY=5.359317

PRIMER_RIGHT_1_PENALTY=5.131951

PRIMER_LEFT_1_SEQUENCE=GTTAGCTCAAGGCAGCTA

PRIMER_RIGHT_1_SEQUENCE=CACTCTCAGAGCCTCCTC

PRIMER_LEFT_1=21,18

PRIMER_RIGHT_1=91,18

PRIMER_LEFT_1_TM=63.641

PRIMER_RIGHT_1_TM=64.132

PRIMER_LEFT_1_GC_PERCENT=50.000

PRIMER_RIGHT_1_GC_PERCENT=61.111

PRIMER_LEFT_1_SELF_ANY_TH=0.00

PRIMER_RIGHT_1_SELF_ANY_TH=0.00

PRIMER_LEFT_1_SELF_END_TH=0.00

PRIMER_RIGHT_1_SELF_END_TH=0.00

PRIMER_LEFT_1_HAIRPIN_TH=44.79

PRIMER_RIGHT_1_HAIRPIN_TH=38.42

PRIMER_LEFT_1_END_STABILITY=7.2000

PRIMER_RIGHT_1_END_STABILITY=7.9000

PRIMER_PAIR_1_COMPL_ANY_TH=0.00

PRIMER_PAIR_1_COMPL_END_TH=0.00

PRIMER_PAIR_1_PRODUCT_SIZE=71

PRIMER_PAIR_2_PENALTY=10.494943

PRIMER_LEFT_2_PENALTY=5.131951

PRIMER_RIGHT_2_PENALTY=5.362992

PRIMER_LEFT_2_SEQUENCE=GAGGAGGCTCTGAGAGTG

PRIMER_RIGHT_2_SEQUENCE=CATGAGATGAATGTGCGT

PRIMER_LEFT_2=74,18

PRIMER_RIGHT_2=161,18

PRIMER_LEFT_2_TM=64.132

PRIMER_RIGHT_2_TM=64.363

PRIMER_LEFT_2_GC_PERCENT=61.111

PRIMER_RIGHT_2_GC_PERCENT=44.444

PRIMER_LEFT_2_SELF_ANY_TH=15.23

PRIMER_RIGHT_2_SELF_ANY_TH=0.00

PRIMER_LEFT_2_SELF_END_TH=0.00

PRIMER_RIGHT_2_SELF_END_TH=0.00

PRIMER_LEFT_2_HAIRPIN_TH=31.39

PRIMER_RIGHT_2_HAIRPIN_TH=0.00

PRIMER_LEFT_2_END_STABILITY=6.4000

PRIMER_RIGHT_2_END_STABILITY=9.9000

PRIMER_PAIR_2_COMPL_ANY_TH=0.00

PRIMER_PAIR_2_COMPL_END_TH=0.00

PRIMER_PAIR_2_PRODUCT_SIZE=88

Rails Table

create_table "primer_pairs", :force => true do |t|

t.integer "primer3_parameter_id"

t.integer "ranking"

t.string "cosmic_mut_id"

t.boolean "left_seq_selected"

t.boolean "right_seq_selected"

t.text "modified_left_sequence"

t.text "modified_right_sequence"

t.decimal "primer_pair_penalty"

t.decimal "primer_left_penalty"

t.decimal "primer_right_penalty"

t.text "primer_left_sequence"

t.text "primer_right_sequence"

t.string "primer_left"

t.string "primer_right"

t.decimal "primer_left_tm"

t.decimal "primer_right_tm"

t.decimal "primer_left_gc_percent"

t.decimal "primer_right_gc_percent"

t.decimal "primer_left_self_any_th"

t.decimal "primer_right_self_any_th"

t.decimal "primer_left_self_end_th"

t.decimal "primer_right_self_end_th"

t.decimal "primer_left_hairpin_th"

t.decimal "primer_right_hairpin_th"

t.decimal "primer_left_end_stability"

t.decimal "primer_right_end_stability"

t.decimal "primer_pair_compl_any_th"

t.decimal "primer_pair_compl_end_th"

t.integer "primer_pair_product_size"

t.datetime "created_at", :null => false

t.datetime "updated_at", :null => false

end

add_index "primer_pairs", ["primer3_parameter_id"], :name => "index_primer_pairs_on_primer3_parameter_id"

awarded to MSF via johndoe

Crowdsource coding tasks.

1 Solution

Winning solution

Assuming that the text file(P3_INPUT_577_8328_1197.raw) is in lib/assets directory of your rails project:


/lib/taks/load_table.rake

namespace :db do
  desc "load data into table"
  task load_table: :environment do
    results = []
    row = 0
    File.open(Rails.root.join("lib", "assets", "P3_INPUT_577_8328_1197.raw"), "r").each_line do |line|
      key, value = line.strip.split("=")
      id = key.scan(/\d+/).first.to_i
      column_name = key.gsub(/_\d+/, "").downcase
      results[id] = {"primer3_parameter_id" => id} unless results[id]
      results[id][column_name] = value
      if id > row
        PrimerPair.create!(results[row])
        results[row] = nil
        row = id
      end
    end
    PrimerPair.create!(results.last)
  end
end

app/models/primer_pair.rb

class PrimerPair < ActiveRecord::Base
  attr_protected
end
Interesting technique with attr_protected. Great work,thanks.
kusadasi almost 6 years ago
I was too lazy to type out the column names in attr_accessible. Thanks for the bounty! :)
johndoe almost 6 years ago