FCSV importing file to Excel

[Phil Swazey]

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.

 

[Mark Thomas]

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.

 

[James Gray]

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

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

 

[Mark Thomas]

"=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.

 

[James Gray]

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=> [["=HYPERLINK(http://www.radiofreetexas.org/", "ClickHere)"]]

James Edward Gray II

 

[Mark Thomas]

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(/\"/,'""')
}

 

[James Gray]

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!(/=3DHYPERLINK\((.*?)\)/) { |hyp|
hyp.gsub(/\"/,'""')
}

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

"some junk here =3DHYPERLINK(""=85"", ""=85"")"

Your solution damages that content.

James Edward Gray II

 

[Mark Thomas]

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.

 

[Phil Swazey]

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.


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