Working on a SMS Gateway Portal (PHP and Kannel) and needed a bulk list of Phone numbers to populate table for testing. Wanted to use real data so while searching the Internet I came across Jaa’s Blog (http://www.jawish.org/) with a data set, though it is dated. I hate to use spreadsheets so created the below script using Ruby. Well, I am only learning Ruby so this was rather a test, and I am surprised how fast I came up with this.
Note that I am working on Linux so if you want to use the code you will need to set the directory structure appropriately. Also take note that I have skipped a lot of validation and cleaning up. However, the script managed to populate the table in 72 seconds. Well, did this is a hurry, 36 lines of coding in 20 min (including testing).
For the inspiration of newbies like me I am sharing the code below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | require 'rubygems' require 'mysql' timebegin = Time.now database_name = "directory" table_name = "dhiraagu" # Phone List Downloaded FROM # http://www.jawish.org/blog/archives/347-Dhiraagu-e-Directory-data-for-download-2009-04-12.html csvfile = '/home/yusuf/workspace/csv/edirectory/edirectory_2009-04-12.txt' m = Mysql.new("localhost", "username", "password") m.select_db(database_name) m.query("DROP TABLE IF EXISTS "+table_name) ptable_create = "CREATE TABLE "+table_name+"( id INT auto_increment primary key, name CHAR(100), number INT(11));" m.query(ptable_create) putrecord = 0 File.open(csvfile).each do |record| i = 1 putrecord = putrecord + 1 puts putrecord.to_s() pname = '' record = record.force_encoding('ISO-8859-1').chomp.gsub(/[\"\'\\]/,"").gsub(/\s,/,",\s").to_s() record.split("\t").each do |field| case field when /^[79365]./ then pnumber = field pquery = "INSERT INTO `"+table_name+"` VALUES('','"+pname.to_s()+"','"+pnumber.to_s()+"');" m.query(pquery) else pname = field end end end m.close puts "Time elapsed #{Time.now - timebegin} seconds" |
I am loving Ruby especially the one liners, gems and clear codes. All I can say is Ruby simply rocks!
You can download the codes here: csv.rb
Recent Comments