0

I am a newbie studying Ruby on Rails. I am supposed to read and parse a csv file from a url then inject the data from csv to a database. Then via this database I must create a html page table on rails contains and lists the info from csv file.

My controller

require 'open-uri'
require 'csv'

class HmrcRatesController < ApplicationController
  def index
    @hmrc_rates = HmrcRate.all
  end

  def new
    csv_text = URI.open('https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/988629/exrates-monthly-0621.csv') { |io| io.read.encode("UTF-8", invalid: :replace) }
    csv = CSV.parse(csv_text, :headers=>true)
    csv.each_with_index do |row, i|
      HmrcRate.create(country: row["Country/Territories"], currency: row["Currency"], curr_code: row["Currency Code"], units_per_pound: row["Units Per £"], start_date: row["Start Date"], end_date: row["End Date"])
      puts "#{i}. #{row}"
      puts "***********************"  #To seperate rows in command line this will go.
  #    HmrcRate.create!(row.to_hash)  #This didn't work don't know why
    end
    redirect_to hmrc_rates_path, notice: "HMRC rates created"
  end
end

My Migration

class CreateHmrcRates < ActiveRecord::Migration[6.0]
  def change
    create_table :hmrc_rates do |t|
      t.string :country
      t.string :currency
      t.string :curr_code
      t.float :units_per_pound
      t.datetime :start_date
      t.datetime :end_date

      t.timestamps
    end
  end
end

My Index HTML

<h1>HmrcRates</h1>
<%= link_to "Get Rates", new_hmrc_rate_path %>

#I NEED THIS BIT HERE. HOW CAN I APPLY MY DATABASE TO HTML LIKE AN EXCEL TABLE?

Thank you for your time and effort.

Holger Just
  • 52,918
  • 14
  • 115
  • 123
Kvothe28
  • 74
  • 1
  • 12
  • 1
    you need to use `io.read.encode("UTF-8", Invalid: :replace, undef: :replace, replace: '?')` to get around the encoding issues you are probably encountering ... see https://stackoverflow.com/questions/13003287/encodingundefinedconversionerror for further info. – David Jun 22 '21 at 14:19
  • I used '''{ |io| io.read.encode("UTF-8", invalid: :replace) } ''' and it worked for me. And table works as well. I have just one problem left. ''' t.float :units_per_pound ''' this parameter is returning te value 0.0 but it's fine on the command prompt. I am uncertain should I edit this question now or should I ask a new question? – Kvothe28 Jun 22 '21 at 14:26
  • 1
    best to ask it as a separate question as it's a different issue. – David Jun 22 '21 at 14:27

1 Answers1

1

Assuming that you've successfully inserted everything into the DB, only thing left to do is just to use that @hmrc_rates variable in your index.html.erb view to display every DB row in a table.

For that you need to loop through each row in that variable and that would be something like:

<table>
<thead>
  <th>Country</th>
  <th>Currency</th>
  <th>Currency Code</th>
  <th>Units per Pound</th>
  <th>Start Date</th>
  <th>End Date</th>
</thead>
<tbody>
  <% @hmrc_rates.each do |hmrc_rate| %>
    <tr>
      <td>
        <%= hmrc_rate.country %>
      </td>
      <td>
        <%= hmrc_rate.currency %>
      </td>
      <td>
        <%= hmrc_rate.curr_code %>
      </td>
      <td>
        <%= hmrc_rate.units_per_pound %>
      </td>
      <td>
        <%= hmrc_rate.start_date %>
      </td>
      <td>
        <%= hmrc_rate.end_date %>
      </td>
    </tr>
  <% end %>
</tbody>
</table>

You can then apply your own styling.

Or you can use one of the table generators suggested in this SO post.

Burak Kaymakci
  • 662
  • 2
  • 16
  • 36