Reading a CSV file into Ruby using Sequel and the SQLite3 gems

Question

I need to read data from two CSV files and load them into SQLite3 in two different tables. I then want to query the two tables and create a new dataset that needs to be output into a new CSV/Excel. What would be the best way to go about doing this?

I am thinking of a combination of SQLite3, Sequel, and FasterCSV.

There is also a CSV2SQLite gem, but I'm not sure how to use it.

Also, if anyone has code snippets, that would be really appreciated.

Answer 1

require 'csv'

csv_file_1 = CSV.read("/home/user/Desktop/first_file_1.csv")
csv_file_2 = CSV.read("/home/user/Desktop/first_file_2.csv")

csv_file_1[0][0] = ["name", "phone"] #ect 

#
# logic goes here to work with your csv files 
# 

require 'sequel'
DB = Sequel.sqlite("/home/user/Desktop/csv.db")

DB.create_table :people do 
  primary_key :id
  String :name
  String :phone
end

database = DB[:people]


database.insert(:name => "duck", :phone => "867-5309")

#logic goes here to insert/sort/manipulate your CSV files.  

BOOM reading csv files and creating a table with sequel ruby gem.

Answer 2

I use another code snipplet:

def import_csv(tabname, data)    
  csv = CSV.parse(data, :headers=> true, :header_converters => :symbol )
  DB.create_table(tabname){
    primary_key :id
    csv.headers.each{|col|    
      String col
    }
  } 
  p csv.headers
  DB[tabname].multi_insert(csv.map {|row| row.to_h})
  #~ csv.each{|row|
    #~ DB[tabname].insert(row.to_h)
  #~ }
end

It reads the CSV-data and creates a table columns CSV-headers (as Strings). Then all data are copied to the table.

A full example:

#encoding: utf-8
=begin
=end
    require 'sequel'
    require 'csv'
    Sequel.extension :pretty_table  #Sequel::PrettyTable.print()/Sequel::PrettyTable.string()
=begin
Test data
=end
    DB = Sequel.sqlite

def import_csv(tabname, data)    
  csv = CSV.parse(data, :headers=> true, :header_converters => :symbol )
  DB.create_table(tabname){
    primary_key :id
    csv.headers.each{|col|    
      String col
    }
  } 
  p csv.headers
  DB[tabname].multi_insert(csv.map {|row| row.to_h})
  #~ csv.each{|row|
    #~ DB[tabname].insert(row.to_h)
  #~ }
end

import_csv(:tab1, DATA.read)    

DB.tables.each{|table|
  puts table
  Sequel::PrettyTable.print(DB[table])
}

    #~ Sequel::PrettyTable.print(DB[:mytables].filter(Sequel.like(:a, 'a%')))
__END__
a,b,c,d,e
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5