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

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开发者_如何学Python 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???

---

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

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

2. Parse your text file into an array of logical fields. I'd suggest using regular expressions, unless your text file is CSV.

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