1

I am learning Ruby and trying to manipulate Excel data.

my goal:

To be able to extract email addresses from an excel file and place them in a text file one per line and add a comma to the end.

my ideas:

i think my answer lies in the use of spreadsheet and File.new.

What I am looking for is direction. I would like to hear any tips or rather hints to accomplish my goal. thanks

Please do not post exact code only looking for direction would like to figure it out myself...

thanks, karen

UPDATE::

So, regex seems to be able to find all matching strings and store them into an array. I´m having some trouble setting that up but should be able to figure it out....but for right now to get started I will extract only the column labeled "E Mail"..... the question I have now is:

`parse_csv = CSV.parse(read_csv, :headers => true)`

The default value for :skip_blanks is set to false.. I need to set it to true but nowhere can I find the correct syntax for doing so... I was assumming something like

`parse_csv = CSV.parse(read_csv, :headers => true :skip_blanks => true)`

But no.....

Improve this question

3 Answers 3

Reset to default
1

save your excel file as csv (comma separated value) and work with Ruby's libraries

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks so much for the comments guys...Once I get a grasp on working with CSV I will give remote_table a try and see which one I would rather use... So this is what I have now.. I am able to read and parse the CSV file and print out the contents of ['E Mail'] to screen.. The next step would be to exclude the blank lines and write to csv file which thanks to @Seamus Abshere I believe can be done using .to_csv.. This is what I have.. not sure how to use the code tags..
#!/usr/bin/env ruby require "csv" read_csv = File.read(ARGV[0]) parse_csv = CSV.parse(read_csv, :headers => true) parse_csv.each do |row| puts "#{row['E Mail']}" end
very nice!! i was just doing the same with open-uri, lots of fun :) try to checkout regular expressions for removing whitespace , specific chars, etc. they are arguments used with methods that can be added to your program objects object.scan(/\d/) would result in digits. for instance, the /\w/ and /\W/ commands are specifically pertaining to dealing with whitespace by excluding them or finding them, plus there is always a wealth of good examples especially here on SO.
0

besides spreadsheet (which can read and write), you can read Excel and other file types with with RemoteTable.

gem install remote_table

and

require 'remote_table'
t = RemoteTable.new('/path/to/file.xlsx', headers: :first_row)

when you write the CSV, as @aug2uag says, you can use ruby's standard library (no gem install required):

require 'csv'
puts [name, email].to_csv
Improve this answer

Comments

0

Personally, I'd keep it as simple as possible and use a CSV.

Here is some pseudocode of how that would work:

read in your file line by line
extract your fields using regex, or cell count (depending on how consistent the email address location is), and insert into an arry
iterate through the array and write the values in the fashion you wish (to console, or file)

The code in the comment you had is a great start, however, puts will only write to console, not file. You will also need to figure out how you are going to know you are getting the email address.

Hope this helps.

Improve this answer

2 Comments

It does very much. I´m learning that there are many ways of doing things in Ruby. I´ll post back as I progress. Thats also a great point about all emails being held in the same column. better to search by string..
There are a lot of ways to do things in most languages. That is what makes programming an art. Knowing how/why to do thing, in the most efficient and effective manner. As you learn, you'll grow and get better :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.