Import CSV file into sqlite3 using Ruby script

Question

I have a ruby script which will read data from some CSV file and import them into sqlite database. I can do this task easily from console using following command:

sqlite> .import <filename> <tablename>
sqlite> .import test.csv PROJECT_REPORT

How can I achieve the same form Ruby script ?

I am doing something similar like following(just a work arround):

 CSV.foreach("timelog.csv") do |row|
     database.execute("INSERT INTO PROJECT_REPORT(
     Date,User,Activity,Comment)
     values
    (#{row[0]}, '#{row[1]}', '#{row[2]}', '#{row[3]}') ")
 end

nathan · Accepted Answer · 2012-02-29 16:35:45Z

2

If it's a one time job and you have a simple script that works why not stick with what you already have? If you need to grow the ruby script and want to expand it look at DataMapper (or on of the other ORMs) as an interface to your SQLite database. Create a simple class to contain your Date, User, Activiy and Comment fields and let DataMapper do the heavy lifting to the database.

Updated with datamapper example Here's a simple pseudo-example the datamapper implementation. This is not tested!

#datamapper configuration here
#.
#.
class UserActivity
 include DataMapper::Resource

  property :id,         Serial    
  property :log_date,       DateTime  
  property :user,       Text        
  property :activity    Text
  property :comment     Text
  property :created_at, DateTime
end

So to use it in your example above...

CSV.foreach("timelog.csv") do |row|
  userActivity = UserActivity.new( :log_date => row[0], :user => row[1], :activity => row[2], :comment => row[3])
  userActivity.save!
end

Obviously this isn't a complete solution, but it will get you started.

edited Feb 29, 2012 at 16:35

answered Feb 29, 2012 at 16:06

nathan

5,7394 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

suvankar Over a year ago

No this is not a 'one time job'. I need to read multiple, small csv file and persist them in sqlite3 database

suvankar Over a year ago

Thanks a lot..and one more thing, in my csv file first line contains Column header and I skip that row.

Collectives™ on Stack Overflow

Import CSV file into sqlite3 using Ruby script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related