Forum: Ruby FCSV importing file to Excel

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
69b9e8ecaf928189112d3885dea14c58?d=identicon&s=25 Phil Swazey (swampy)
on 2009-04-29 06:13
I have a csv file that I am inputting into an excel spreadsheet. One of
the fields is a hyperlink:
=HYPERLINK("http://www.radiofreetexas.org/","ClickHere")

When FCSV comes across the file to process it and insert pass the data
to be inserted into the Excel file I get an error:
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1592:in
`shift': Unclosed quoted field on line 1. (FasterCSV::MalformedCSVError)
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1512:in
`loop'
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1512:in
`shift'
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1457:in
`each'
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1003:in
`foreach'
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1175:in
`open'
from
C:/ruby/lib/ruby/gems/1.8/gems/fastercsv-1.2.3/lib/faster_csv.rb:1002:in
`foreach'

I've looked around and can't seem to find anyone that has any
suggestions to get around this issue in FCSV. Anyone have any
suggestions.
Here is how I process the data from the csv file:

# Open the csv data file being used to input the data to the Excel
Template
# Set the Column index to "A" this resets the colmn to "A" for each new
# line in the data file.
file_in = FCSV.foreach(file_excel_input) do |line|
   col_index = 'a'
   line_index += 1
   #Index to the Active line where data will be Input in the Excel file
   #Get the line with an index number for the file the index was for the
colmn
   #cell number but used a col_index instead.
  line.each_with_index do |element,i|
     #Put the data into the Spread sheet here!!
     begin
     worksheet.Range("#{col_index}#{line_index}")['Value'] =
["#{element}"]

Greatly Appreciate any help or suggestions, I am relatively new to Ruby
and programming. Thanks in Advance.
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2009-04-29 14:46
(Received via mailing list)
On Apr 28, 2009, at 11:13 PM, Phil Swazey wrote:

> (FasterCSV::MalformedCSVError)
> from

FasterCSV is complaining that your data isn't valid CSV data.  If you
showed the entire field above, that's true.

In CSV, a field that contains commas or quotes must be enclosed in
quotes ad have the inner quotes escaped.  Thus, to be valid the above
field should be:

"=HYPERLINK(""http://www.radiofreetexas.org/"", ""ClickHere"")"

Unfortunately, I can't think of an easy way to break up data like
that.  You would likely need to build your own parser to handle it.

Sorry I wasn't more help.

James Edward Gray II
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2009-04-30 00:50
(Received via mailing list)
> When FCSV comes across the file to process it and insert pass the data
> to be inserted into the Excel file I get an error

I've read and re-read this sentence, but I'm not understanding it.
What is your input, and what is your output? Is the problem with
parsing or creating your output? Where does Excel come into the
picture (CSV != Excel).

A minimal but complete program exhibiting the problem would be helpful.
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2009-04-30 13:31
(Received via mailing list)
On Apr 29, 8:46 am, James Gray <ja...@grayproductions.net> wrote:

> "=HYPERLINK(""http://www.radiofreetexas.org/"", ""ClickHere"")"
>
> Unfortunately, I can't think of an easy way to break up data like  
> that.  You would likely need to build your own parser to handle it.

You mean like this?

str.gsub(/"(?!")/, "")

-- Mark.
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2009-05-01 03:08
(Received via mailing list)
On Apr 30, 2009, at 6:30 AM, Mark Thomas wrote:

> On Apr 29, 8:46 am, James Gray <ja...@grayproductions.net> wrote:
>
>> "=HYPERLINK(""http://www.radiofreetexas.org/"", ""ClickHere"")"
>>
>> Unfortunately, I can't think of an easy way to break up data like
>> that.  You would likely need to build your own parser to handle it.
>
> You mean like this?
>
> str.gsub(/"(?!")/, "")

I'm not real sure what you are aiming for here.  I doubt this helps
read the data in the intended manner:

$ irb -rubygems
 >> require "faster_csv"
=> true
 >> str = %Q{=HYPERLINK("http://www.radiofreetexas.org/","ClickHere")}
=> "=HYPERLINK(\"http://www.radiofreetexas.org/\",\"ClickHere\")"...
 >> FCSV.parse(str.gsub(/"(?!")/, ""))
=> [["=HYPERLINK(http://www.radiofreetexas.org/", "ClickHere)"]]

James Edward Gray II
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2009-05-01 14:36
(Received via mailing list)
On Apr 30, 9:06 pm, James Gray <ja...@grayproductions.net> wrote:
>
> > str.gsub(/"(?!")/, "")
>
> I'm not real sure what you are aiming for here.  I doubt this helps  
> read the data in the intended manner:

That's what I get for reading too quickly to get the entire context.
The regex de-escapes the string above. I realize now you were talking
about parsing the invalid CSV. The way I see it, the OP has two
options:
1. Not use FCSV
2. Make his CSV valid before parsing

If the above is his only problem, he can do #2 this way:

line.gsub!(/=HYPERLINK\((.*?)\)/) { |hyp|
  hyp.gsub(/\"/,'""')
}
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2009-05-01 16:38
(Received via mailing list)
On May 1, 2009, at 7:35 AM, Mark Thomas wrote:

>>> You mean like this?
> 1. Not use FCSV
> 2. Make his CSV valid before parsing
>
> If the above is his only problem, he can do #2 this way:
>
> line.gsub!(/=HYPERLINK\((.*?)\)/) { |hyp|
>  hyp.gsub(/\"/,'""')
> }

That's not a general solution.  What if I had a field like this?

   "some junk here =HYPERLINK(""…"", ""…"")"

Your solution damages that content.

James Edward Gray II
134ea397777886d6f0aa992672a50eaa?d=identicon&s=25 Mark Thomas (Guest)
on 2009-05-02 04:35
(Received via mailing list)
On May 1, 10:37 am, James Gray <ja...@grayproductions.net> wrote:
>
> > That's what I get for reading too quickly to get the entire context.
> > }
>
> That's not a general solution.

True, but I am assuming the poster wants a quick and dirty solution
since he has "a file" that has the problem with the HYPERLINK()
function.

> What if I had a field like this?
>
>    "some junk here =HYPERLINK(""…"", ""…"")"

If he had the above, he wouldn't need a fix because it is already
properly formatted. What my gsub does is double the quotes within the
hyperlink function (inside the parens only). It works for the OP's
stated case. It may be all that's needed.

-- Mark.
69b9e8ecaf928189112d3885dea14c58?d=identicon&s=25 Phil Swazey (swampy)
on 2009-06-09 23:42
Mark Thomas wrote:
> On May 1, 10:37�am, James Gray <ja...@grayproductions.net> wrote:
>>
>> > That's what I get for reading too quickly to get the entire context.
>> > }
>>
>> That's not a general solution.
>
> True, but I am assuming the poster wants a quick and dirty solution
> since he has "a file" that has the problem with the HYPERLINK()
> function.
>
>>�What if I had a field like this?
>>
>> � �"some junk here =HYPERLINK(""�"", ""�"")"
>
> If he had the above, he wouldn't need a fix because it is already
> properly formatted. What my gsub does is double the quotes within the
> hyperlink function (inside the parens only). It works for the OP's
> stated case. It may be all that's needed.
>
> -- Mark.

Sorry it took me so long to get back to this, but some things cannot be
helped.

Mark and James,
I used both of your ideas and since the quotes and commas are
constant/consistent, basically I put the data into the .csv properly
formed. I actually gather the data over time and put it into a file.
Then I wrote a subsitution for that cell as it is inserted into the
spreadsheet. So, I basically worked around the FCSV issue, or maybe its
my issue trying to put malformed data into a .csv file? If you all want
I can try to put the code up here. Thanks again for the time and ideas.
Phil
This topic is locked and can not be replied to.