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.
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.
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
