What's the most efficient way to build an SQLite database from the data in a file?

Question

I have a single text file with data in text format and I want to put this data in an automated manner in a database for further use in a Ruby on Rails application and I want to use Ruby for solving this problem.

I want to know the most efficient way of solving this problem and the possible solutions, that come in your mind.

The solutions I have found include using the active record to fetch the data from the file and into the database which might not be the most efficient, so let's see what you have got in your mind???

"in text format" is vague. Now that I think about it, so is "in an automated manner" and "for further use in Rails". If you provided more details, you'd get better answers. — Mark Thomas
– Mark Thomas, Commented Jan 23, 2011 at 14:52
With "text format" I meant that the file is a .txt file and the contents include questions and answers which I can be parsed using regex. — Apoorv Saxena
– Apoorv Saxena, Commented Jan 24, 2011 at 14:18

Phrogz · Accepted Answer · 2011-01-23 19:44:12Z

3

I find Sequel to be faster than ActiveRecord, but both should be fine.

Read your text file into memory. If it is too large to fit into memory, you'll want to use File.foreach to operate on it one line at a time.
Parse your text file into an array of logical fields. I'd suggest using regular expressions, unless your text file is CSV.
Perform a multi_insert on your database, which uses a single SQL command to insert n rows at once.
- If you can't perform a batch insertion (I understand that ActiveRecord doesn't support it by default without some extension), then be sure at least to use a database transaction around all your inserts. If you don't, SQLite will be hitting the file system for each insert, which slows you down greatly.

Without more information about your "text file", this is about as helpful as I can be.

answered Jan 23, 2011 at 19:44

Phrogz

304k115 gold badges669 silver badges758 bronze badges

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

Collectives™ on Stack Overflow

What's the most efficient way to build an SQLite database from the data in a file?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related