Obtain data from .csv

ruby-talk
1

Sample of .csv file:

["Issue,Risk/Opportunity,Compliance,Consequence,Current Status,Due
Date,Priority,Responsibility,Probability"]
["1,Efficiency (Energy),No
Action,Unknown,Neutral,Active,Medium,Possible"]
["Action,Cost,Status,Due Date,Responsibility,"]
["1.a,Maximise use of natural lighting,Active,"]
["1.b,Turn-off unnecessary lights/equipment,Active,"]
["1.c,Install energy efficient lighting,Active,"]
["1.d,Turn-off unnecessary air conditioning,Active,"]

Using the following code:

f = File.open("ActionPlan.csv", "r")
f.each_line { |line|
  fields = line.split(',')
  #.........
}

Anyone knows how to use ruby code to obtain the following data(question)
from the file?

- Maximise use of natural lighting
- Turn-off unnecessary lights/equipment

I only know how to obtain data from the first two lines using the
following code:

  p.issue = fields[0].tr_s('"', '').strip
  p.blank = fields[1].tr_s('"', '').strip
  p.risk_opportunity = fields[2].tr_s('"', '').strip
  p.compliance = fields[3].tr_s('"', '').strip
  p.consequence = fields[4].tr_s('"', '').strip
  p.current_status = fields[5].tr_s('"', '').strip
  p.due_date = fields[6].tr_s('"', '').strip
  p.priority = fields[7].tr_s('"', '').strip
  p.responsibility = fields[8].tr_s('"', '').strip
  p.probability = fields[9].tr_s('"', '').strip

But dont know how to obtain the rest of the data. Any help would be
great. Thanks in advance

Nizam

···

--
Posted via http://www.ruby-forum.com/.

2

I'd use the fastercsv gem:

\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

require 'fastercsv'

FasterCSV.foreach('test.csv', :headers => true) do |row|
  puts row[nil]
end

\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

I am assuming each line doesn't really have square brackets and quotes
around it, but if it does you can strip those off before passing the
result to fastercsv. Some examples here:
http://fastercsv.rubyforge.org

Its great making your own CSV parser until you (inevitably) get some
data with quotes in it and then it all goes downhill.

Inside the loop there I am printing out the column without any name,
which is the column you wanted. If you changed the first line of your
CSV file to:

Issue,Comment,Risk/Opportunity,Compliance,Consequence,Current
Status,Due Date,Priority,Responsibility,Probability

..then you could change the:
   puts row[nil]
to
   puts row["Comment"]

···

On Tue, Jan 25, 2011 at 3:22 PM, Kamarulnizam Rahim <niezam54@hotmail.com> wrote:

Sample of .csv file:

["Issue,Risk/Opportunity,Compliance,Consequence,Current Status,Due
Date,Priority,Responsibility,Probability"]
["1,Efficiency (Energy),No
Action,Unknown,Neutral,Active,Medium,Possible"]
["Action,Cost,Status,Due Date,Responsibility,"]
["1.a,Maximise use of natural lighting,Active,"]
["1.b,Turn-off unnecessary lights/equipment,Active,"]
["1.c,Install energy efficient lighting,Active,"]
["1.d,Turn-off unnecessary air conditioning,Active,"]

Using the following code:

f = File.open("ActionPlan.csv", "r")
f.each_line { |line|
fields = line.split(',')
#.........
}

Anyone knows how to use ruby code to obtain the following data(question)
from the file?

- Maximise use of natural lighting
- Turn-off unnecessary lights/equipment

I only know how to obtain data from the first two lines using the
following code:

p.issue = fields[0].tr_s('"', '').strip
p.blank = fields[1].tr_s('"', '').strip
p.risk_opportunity = fields[2].tr_s('"', '').strip
p.compliance = fields[3].tr_s('"', '').strip
p.consequence = fields[4].tr_s('"', '').strip
p.current_status = fields[5].tr_s('"', '').strip
p.due_date = fields[6].tr_s('"', '').strip
p.priority = fields[7].tr_s('"', '').strip
p.responsibility = fields[8].tr_s('"', '').strip
p.probability = fields[9].tr_s('"', '').strip

But dont know how to obtain the rest of the data. Any help would be
great. Thanks in advance

3

Kamarulnizam Rahim wrote in post #977296:

Sample of .csv file:

["Issue,Risk/Opportunity,Compliance,Consequence,Current Status,Due
Date,Priority,Responsibility,Probability"]
["1,Efficiency (Energy),No
Action,Unknown,Neutral,Active,Medium,Possible"]
["Action,Cost,Status,Due Date,Responsibility,"]
["1.a,Maximise use of natural lighting,Active,"]
["1.b,Turn-off unnecessary lights/equipment,Active,"]
["1.c,Install energy efficient lighting,Active,"]
["1.d,Turn-off unnecessary air conditioning,Active,"]

Using the following code:

f = File.open("ActionPlan.csv", "r")
f.each_line { |line|
  fields = line.split(',')
  #.........
}

Anyone knows how to use ruby code to obtain the following data(question)
from the file?

But dont know how to obtain the rest of the data. Any help would be
great. Thanks in advance

Nizam

Hi Nizam,

Sorry to say but I think I'm quite confused. You started out trying CSV
and have another thread on that, but with this thread it seems you have
decided to give up on that approach and use simple lines and split.
Unfortunately I'm still getting nowhere closer to what you actually
intend to do with your data once you have broken it down into individual
fields.

Just a point to note...
in the other thread, where 1 of the solution is to use regexp to split
each line down into individual sections, is made based on the assumption
your delimiter is '|'. Maybe you can try to change that piece of code to
split on ',' and try printing out the fields again and see if it works.

yjw

···

--
Posted via http://www.ruby-forum.com/\.

4

does you CSV file actually really have those square brackets?? If yes,
you can strip them off first

f = File.open("ActionPlan.csv", "r")
f.each_line { |line|
  line = line.gsub!(/^\[\"|\"\]$/,"")
  fields = line.split(',')

}

i am not sure why you are stripping of double quotes from each field,
when the sample csv you supplied doesn't tally with what you are doing.

···

--
Posted via http://www.ruby-forum.com/.

5

Leslie : I dont think I have time to create a new script based on the
parser since i have already write great amount of codes on this ruby
file.

Jiawei : Yes I have worked on two separate file for obtaining csv data
to see which one is better. and i think i have write more code for the
file on this thread rather than the other one.

Steel : My csv file do not have any square bracket. But in my ruby
codes, i put them into an array so that it is easier to call a class
upon them later (thats what i thought based on my 3 weeks on ruby
programming career).

p = Objective.new

I put all data into Objective class, in which the class is still not
being completed.

** Attached is the csv data i used. I still dont know how to obtain data
as mentioned in my first post as i have no idea how to tell ruby to
obtain that data (csv data from line 3 , 4, onwards). Any help would be
great. Thank you

Nizam

Attachments:
http://www.ruby-forum.com/attachment/5787/ActionPlan.csv

···

--
Posted via http://www.ruby-forum.com/.

6

Yeah, iam still having trouble extracting some data from the csv file
(i.e All of the 'actions'). The thing is i need to extract those data
and transfer those data into an existing yaml file for some reason. DO
you know how to extract for example :

Maximise use of natural lighting
Turn-off unnecessary lights/equipment
Install energy efficient lighting
Turn-off unnecessary air conditioning

Thank you

Kind Regards,
Nizam

···

--
Posted via http://www.ruby-forum.com/.

7

Hi yjw,

Thanks for your post. btw, how do you specifically obtain data for
example 'Maximise use of natural lighting' only?do i need to filter it
out as you said?
This is because, based on your code above, it will loop over the entire
row in which all action's content will be displayed.

Thank you,

Nizam

···

--
Posted via http://www.ruby-forum.com/.

8

Hi yjw,

didnt realize your second post. yes, i mean like that but what if i only
the second or last result only (in particular)? how do i extract that?

Thanks for your help. Really appreciate it

Nizam

···

--
Posted via http://www.ruby-forum.com/.

9

Tried the code but give this error:

C:/Ruby192/lib/ruby/1.9.1/csv.rb:1892:in `block (2 levels) in shift':
Unquoted fields do not allow \r or \n (line 1). (CSV::MalformedCSVError)
from C:/Ruby192/lib/ruby/1.9.1/csv.rb:1863:in `each'
        from C:/Ruby192/lib/ruby/1.9.1/csv.rb:1863:in `block in shift'
        from C:/Ruby192/lib/ruby/1.9.1/csv.rb:1825:in `loop'
        from C:/Ruby192/lib/ruby/1.9.1/csv.rb:1825:in `shift'
        from C:/Ruby192/lib/ruby/1.9.1/csv.rb:1767:in `each'

y?

Btw thanks guest and yjw for the post

Nizam

···

--
Posted via http://www.ruby-forum.com/.

10

is it because im using windows 7 or because of my ide NetBeans 6.9.1?

Nizam

···

--
Posted via http://www.ruby-forum.com/.

11

Yes im using the same file. And it worked!!! after changing into 'rb'.
Its quite inconvenient to know that different platform requires
different format.

Anyway thank you so much!!

Nizam

···

--
Posted via http://www.ruby-forum.com/.

12

Steel Steel wrote in post #977326:

does you CSV file actually really have those square brackets?? If yes,
you can strip them off first

f = File.open("ActionPlan.csv", "r")
f.each_line { |line|
  line = line.gsub!(/^\[\"|\"\]$/,"")
  fields = line.split(',')

}

i am not sure why you are stripping of double quotes from each field,
when the sample csv you supplied doesn't tally with what you are doing.

Actually I have this feeling the posted segment

["Issue,Risk/Opportunity,Compliance,Consequence,Current Status,Due
Date,Priority,Responsibility,Probability"]
["1,Efficiency (Energy),No
Action,Unknown,Neutral,Active,Medium,Possible"]
["Action,Cost,Status,Due Date,Responsibility,"]
["1.a,Maximise use of natural lighting,Active,"]
["1.b,Turn-off unnecessary lights/equipment,Active,"]
["1.c,Install energy efficient lighting,Active,"]
["1.d,Turn-off unnecessary air conditioning,Active,"]

is the result of some File operations in irb...

···

--
Posted via http://www.ruby-forum.com/\.

13

** Attached is the csv data i used. I still dont know how to obtain data
as mentioned in my first post as i have no idea how to tell ruby to
obtain that data (csv data from line 3 , 4, onwards). Any help would be
great. Thank you

Nizam

Hi Nizam,

Saw in the other thread changing to ',' for the regexp works. The
timestamp for that post is earlier than this particular post. Still
having problems? Are you trying to extract data that fits some criteria
from your csv file for some processing? What's the selection criteria
here?

Regards,
yjw

···

--
Posted via http://www.ruby-forum.com/\.

14

The thing is i need to extract those data

and transfer those data into an existing yaml file for some reason. DO
you know how to extract for example :

Maximise use of natural lighting
Turn-off unnecessary lights/equipment
Install energy efficient lighting
Turn-off unnecessary air conditioning

Thank you

Kind Regards,
Nizam

Hi Nizam,

Not too sure about the yaml part (Saw another thread from you on that).
I'm not far into Ruby myself, only spending 1 hr max after work reading
it since it's not part of my day job, so I'm not answering with
FasterCSV or CSV as your file parser, since the concept of extracting
data should be the same regardless of parser(I think).

I think the data you are asking for is actually indexed 1 after you do
the split.

In irb, my code below

f = File.open("ActionPlan.csv","r")
f.each_line{ |line|
   fields = line.split(',')
   puts "Field length = #{fields.length}"
    puts "Fields: 0- #{fields[0]} 1- #{fields[1]}"
}

gives the following output

Field length = 10
Fields: 0- Issue 1-
Field length = 10
Fields: 0- 1 1- Efficiency (Energy)
Field length = 10
Fields: 0- Action 1-
Field length = 10
Fields: 0- 1.a 1- Maximise use of natural lighting
Field length = 10
Fields: 0- 1.b 1- Turn-off unnecessary lights/equipment
Field length = 10
Fields: 0- 1.c 1- Install energy efficient lighting
Field length = 10
Fields: 0- 1.d 1- Turn-off unnecessary air conditioning

You should be able to add some logic to filter out the needless
fields[1] in the 1st 3 row ("", "Efficiency(Energy)","")

if I did not remember wrongly, in the other thread, you were using
fields[2]. That will give you "" considering your current csv structure.

Regards,
yjw

···

--
Posted via http://www.ruby-forum.com/\.

15

You mean something like this?

$ ruby -v bar.rb
ruby 1.8.7 (2009-06-12 patchlevel 174) [universal-darwin10.0]
Maximise use of natural lighting
Turn-off unnecessary lights/equipment
Install energy efficient lighting
Turn-off unnecessary air conditioning
Repair or replace damaged oven/refrigerator seals
Install draught proofing
Consider energy efficiency labels when buying electrical items
Insulate hot water pipes
Insulate/optimise hot water cylinder(s)
Get right size of hot water cylinder
...

$ cat bar.rb
require 'csv'

CSV.open('ActionPlan.csv','r').each do |row|
  puts row[1] if row[0].to_s.match(/\d\.[a-z]/)
end

···

On Tue, Jan 25, 2011 at 8:50 PM, Kamarulnizam Rahim <niezam54@hotmail.com> wrote:

Yeah, iam still having trouble extracting some data from the csv file
(i.e All of the 'actions'). The thing is i need to extract those data
and transfer those data into an existing yaml file for some reason. DO
you know how to extract for example :

Maximise use of natural lighting
Turn-off unnecessary lights/equipment
Install energy efficient lighting
Turn-off unnecessary air conditioning

Thank you

16

how do you specifically obtain data for
example 'Maximise use of natural lighting' only?do i need to filter it
out as you said?
This is because, based on your code above, it will loop over the entire
row in which all action's content will be displayed.

Thank you,

Nizam

Hi Nizam,

Yes you need to add logic to filter out the rest of the rows for which
you have unwanted data.

Thanks to 'Guest' above. His code segment does the filtering by taking
the text data only from rows labelled 1.a, 1.b, 2.a,2.b, etc. Give it a
try it should work.

Regards,
yjw

···

--
Posted via http://www.ruby-forum.com/\.

17

$ ruby baz.rb
["1.a", "Maximise use of natural lighting", nil, "Active", nil, nil,
nil, nil, nil, nil]

$ cat baz.rb
require 'csv'

CSV.open('ActionPlan.csv','r').each do |row|
  next unless row[0].to_s.match(/\d\.[a-z]/)
  next unless row[1].to_s == 'Maximise use of natural lighting'

  p row
end

···

On Tue, Jan 25, 2011 at 9:36 PM, Kamarulnizam Rahim <niezam54@hotmail.com> wrote:

how do you specifically obtain data for
example 'Maximise use of natural lighting' only? do i need to filter it
out as you said?

18

Is the csv file you posted the same as the file you are testing with?

Since you are on Windows, try opening the file with 'rb' (read,
binary) instead of just 'r' (read)?

i.e. "CSV.open('ActionPlan.csv','rb').each do |row|"

···

On Tue, Jan 25, 2011 at 9:52 PM, Kamarulnizam Rahim <niezam54@hotmail.com> wrote:

Tried the code but give this error:

C:/Ruby192/lib/ruby/1.9.1/csv.rb:1892:in `block (2 levels) in shift':
Unquoted fields do not allow \r or \n (line 1). (CSV::MalformedCSVError)

19

Yes im using the same file. And it worked!!! after changing into 'rb'.
Its quite inconvenient to know that different platform requires
different format.

That is related to the traditional difference in line-endings between
Unix (\n) and DOS (\r\n). On Windows, Ruby translates the "\r\n" to
"\n"; sometimes this helps and sometimes it may hinder:

http://www.ruby-doc.org/core/classes/IO.html
"b" | Binary file mode (may appear with
       > any of the key letters listed above).
       > Suppresses EOL <-> CRLF conversion on Windows. And
       > sets external encoding to ASCII-8BIT unless explicitly
       > specified.

···

On Tue, Jan 25, 2011 at 10:38 PM, Kamarulnizam Rahim <niezam54@hotmail.com> wrote: