From: "Ara.T.Howard" Date: 2003-10-09T00:09:22+09:00 Subject: Re: Parsing challenge... On Wed, 8 Oct 2003, Useko Netsumi wrote: > Got it! I just have to replace the (+) sign with (*) for blank or any > string. > > Next, how do I insert those values into MySQL database, assuming I have > those table defined. Thanks. file: parse.rb ----CUT---- #!/usr/bin/env ruby require 'mysql' # command line args host, user, passwd, db, relation = ARGV db ||= 'test' relation ||= 'test' # connect to db mysql = Mysql.connect host, user, passwd mysql.select_db db # parse txt = DATA.read pat = %r{([^^]*)\^([^^]*)\^([^^]*)\^([^^]*)\n}mox tuples = txt.scan pat # insert tuples sql = "insert into %s values('%s','%s','%s','%s')" tuples.each do |tuple| begin insert = sql % [relation, *tuple] mysql.query insert rescue Exception => e p e end end # show results res = mysql.query('select * from %s' % [relation]) while((row = res.fetch_row)) p row end # sample input is embedded below - can be read via DATA object __END__ CODE#1^DESCRIPTION^CODE#2^NOTES NN-110^an info of NN-001^BRY234^some notes NN-111^1st line data 2nd line data 3rd line data^BRT345^another notes NN-112^description of NN-112^BBC23^multiline notes blah blah blah NN-113^info info^MNO12^some notes here ----CUT---- running it looks like: ~/eg/ruby > ./parse.rb ["CODE#1", "DESCRIPTION", "CODE#2", "NOTES"] ["NN-110", "an info of NN-001", "BRY234", "some notes"] ["NN-111", "1st line data\n2nd line data\n3rd line data", "BRT345", "another notes"] ["NN-112", "description of NN-112", "BBC23", "multiline\nnotes blah\nblah\nblah"] ["NN-113", "info info", "MNO12", "some notes here"] i created a database named 'test', and a table named 'test' using 'create table test(f0 text,f1 text,f2 text,f3 text)' hope that gets you going. -a > > "Useko Netsumi" wrote in message > news:bm08ec$gd9eb$1@ID-159205.news.uni-berlin.de... > > this script failed if any of the cell is blank/no-value, > > e.g: > > > > CODE#1^DESCRIPTION^CODE#2^NOTES > > NN-110^^^some notes > > NN-111^1st line data > > 2nd line data > > 3rd line data^BRT345^another notes > > NN-112^description of NN-112^BBC23^multiline > > notes blah > > blah > > blah > > NN-113^info info^MNO12^some notes here > > > > > > "Ara.T.Howard" wrote in message > > news:Pine.LNX.4.53.0310072218560.32521@eli.fsl.noaa.gov... > > > On Tue, 7 Oct 2003, Artco News wrote: > > > > > > > I thought I ask the scripting guru about the following. > > > > > > > > I have a file containing records of data with the following > format(first > > > > column is the label): > > > > > > > > CODE#1^DESCRIPTION^CODE#2^NOTES > > > > NN-110^an info of NN-001^BRY234^some notes > > > > NN-111^1st line data > > > > 2nd line data > > > > 3rd line data^BRT345^another notes > > > > NN-112^description of NN-112^BBC23^multiline > > > > notes blah > > > > blah > > > > blah > > > > NN-113^info info^MNO12^some notes here > > > > > > > > How do I parse so I can insert them in the database, e.g. > MySQL/Access? > > > > > > > > Perhaps there are an advanced scripting language can do this easily. > > > > > > ruby is one of the more advanced :-) > > > > > > ~/eg/ruby > cat ./parse.rb > > > > > > #!/usr/bin/env ruby > > > > > > txt = <<-txt > > > CODE#1^DESCRIPTION^CODE#2^NOTES > > > NN-110^an info of NN-001^BRY234^some notes > > > NN-111^1st line data > > > 2nd line data > > > 3rd line data^BRT345^another notes > > > NN-112^description of NN-112^BBC23^multiline > > > notes blah > > > blah > > > blah > > > NN-113^info info^MNO12^some notes here > > > txt > > > > > > > > > pat = %r{([^^]+)\^([^^]+)\^([^^]+)\^([^^]+)\n}mox > > > tuples = txt.scan pat > > > > > > tuples.map{|tuple| p tuple} > > > > > > > > > ~/eg/ruby > ./parse.rb > > > > > > [" CODE#1", "DESCRIPTION", "CODE#2", "NOTES"] > > > [" NN-110", "an info of NN-001", "BRY234", "some notes"] > > > [" NN-111", "1st line data\n 2nd line data\n 3rd line data", > > "BRT345", "another notes"] > > > [" NN-112", "description of NN-112", "BBC23", "multiline\n notes > > blah\n blah\n blah"] > > > [" NN-113", "info info", "MNO12", "some notes here"] > > > > > > -a > > > ==================================== > > > | Ara Howard > > > | NOAA Forecast Systems Laboratory > > > | Information and Technology Services > > > | Data Systems Group > > > | R/FST 325 Broadway > > > | Boulder, CO 80305-3328 > > > | Email: ahoward@noaa.gov > > > | Phone: 303-497-7238 > > > | Fax: 303-497-7259 > > > | The difference between art and science is that science is what we > > understand > > > | well enough to explain to a computer. Art is everything else. > > > | -- Donald Knuth, "Discover" > > > | ~ > /bin/sh -c 'for lang in ruby perl; do $lang -e "print > > \"\x3a\x2d\x29\x0a\""; done' > > > ==================================== > > > > > > > ==================================== | Ara Howard | NOAA Forecast Systems Laboratory | Information and Technology Services | Data Systems Group | R/FST 325 Broadway | Boulder, CO 80305-3328 | Email: ara.t.howard@noaa.gov | Phone: 303-497-7238 | Fax: 303-497-7259 | The difference between art and science is that science is what we understand | well enough to explain to a computer. Art is everything else. | -- Donald Knuth, "Discover" | ~ > /bin/sh -c 'for lang in ruby perl; do $lang -e "print \"\x3a\x2d\x29\x0a\""; done' ====================================