Hi Eldon,

I just wonder if you could help. I’m trying to include a command line script in my migration to import the data from a .txt file to a sqlite3 table.
The .txt file contain data like this:

VOTE 1168042507 Campaign:ssss_uk_01B Validity:during Choice:Tupele CONN:MIG01XU MSISDN:00777774039999 GUID:2E57FBCC-9A5E-48C0-A5C9-D9372AA3440A Shortcode:63334

VOTE 1168042515 Campaign:ssss_uk_01B Validity:during Choice:Mark CONN:MIG01XU MSISDN:00777778179999 GUID:C2FD053E-8EBE-49A5-A940-D76A71F6D6A7 Shortcode:63334

VOTE 1168042552 Campaign:ssss_uk_01B Validity:during Choice:Tupele CONN:MIG00VU MSISDN:00777770939999 GUID:A0E2C5B2-3AFB-40D7-8D48-B68B93D4FFE9 Shortcode:63334

btw, I finally got my heroku invitation, I managed to put the community project online (but with many modifications). In case you are curious, check it out at happycommunity.heroku.com/ , I know it’s completely useless, but I’m still happy :-)

Lei,

So sorry – I completely spaced on logging back into heroku to see if I had any invites. I feel like a bunghole. Sorry.

As for importing from a text file into sqlite – I haven’t had a reason to try that yet. Is an interesting idea though – I’ll see what I can do.

And your happy community site looks great – I really loved the tagline “Come and join us, we are happy!”

Lei,

Was thinking about your question a little more during a meeting and it seems to me that the easiest approach would be to simply open the file from ruby and pass each line through a regular expression – so something like:

    txt_file = File.open('votes.txt')
    data = txt_file.read
    re = /\G(.+)\s([a-zA-Z_0-9\-]+)\s.+\s\(v9.0\)\s\((qos\/\d+\s\d+)\),\sstart\s(.+)/
## this is regular expression i copied from an old script
## obviously you'll need to create your own to match your data
    data.each_line do |line|
      re =~ line
      t = Table.new
      t.field1 = "#{$1}" 
      t.field2 = "#{$2}" 
      etc
      t.save
    end

Obviously I’m simply writing this from scratch in my head so it may not work but hopefully it’ll get you along the path towards the working solution. Since you’re doing this from within a migration – you’ve already got your connection to your database so now it’s simply a matter of parsing the text file and grabbing the relevant data.

no worries, I don’t actually have a real need for Heroku anyway. But their technology does seem to be quite amazing!

It may be better to explain a little about the outcome I was trying to achieve with the data. I think this is a really interesting task, I’d really love to hear what you think about it.

The data in the text file were actually votes gathered for TV shows (most likely reality TV shows), where viewers vote on their favorite candidates through SMS messages. The campaign is actually an episode of voting, and there are many different episodes. The aim I believe is to import the data into the database immediately after the show to display the votes for each candidates. So that when the user clicks on a campaign, it should present a list of the candidates, their valid votes, also the number of votes which were sent in but not counted. The only useful fields are Campaign, Validity, and Choice, all the rest are irrelevant. I have built a very basic version of the application with a single table (the data were typed in manually), I don’t know if it is necessary or even practical to have normalized tables to handle the data because it would be very challenging to import the data into these tables.

Also I thought it might be easy to import the data through migration because many books handled data importing in this way, but is it possible to do it directly through command line from terminal?

Well if you’re only importing data once (i.e. an initial seeding of the database) then it would make sense to have it in your migration.

However it sounds like you might be needing to do this import on a ongoing basis, in which case migrations wouldn’t be the right place for this. Instead I would look at creating a new Rake task in lib/tasks that would load the file(s) and insert their data into the database. You could even automate the process by calling the Rake task as a cron job on a regular basis.

In case you’re not familiar with Rake—here’s a good (and I think entertaining) little tutorial on creating Rake tasks by the RailsEnvy guys http://www.railsenvy.com/2007/6/11/ruby-on-rails-rake-tutorial

And using a regexp – you should be able to grab just the data out of each line that you want—ignoring the rest.

Thanks, I’ll definitely have a look at the tutorial. For now, I think I’ll just do it through migration, though I have to first figure out how to write a proper regular expression for it.

Thank you for being so helpful, I’ve just loaded the data :D