Import csv with Fields that contain double quote characters

hi
i import csv files in ruby.
it work good, except when a filed has double quote characters- the
import is failed
The data fields are in Hebrew
thanks for help.

2010/11/14 הדסה גרינוולד [email protected]:

hi
i import csv files in ruby.
it work good, except when a filed has double quote characters- the
import is failed
The data fields are in Hebrew
thanks for help.

Hi,

What library are you using to import the csv? Are the files in utf-8,
or are they windows-1255?

Regards,
Ammar

Ammar A. wrote in post #961324:

2010/11/14 הדסה גרינוולד [email protected]:

hi
i import csv files in ruby.
it work good, except when a filed has double quote characters- the
import is failed
The data fields are in Hebrew
thanks for help.

Hi,

What library are you using to import the csv? Are the files in utf-8,
or are they windows-1255?

Regards,
Ammar

hi
i do it wuth :fasterCSV,
and using- windows-1255, and convert it to-utf-8.
thank you

FasterCSV.open(data_file,“r”).each_with_index do |row,row_idx|
logger.info “row_idx:-----#{row_idx}------”
if row_idx > 0 #if is not a row of titles
vals = []
row.each_with_index do |val, col_idx |
if col_idx > 0 #skip on the first column - a
number for task (not needed)
# unless val.blank?
# val =
Iconv.iconv(‘utf-8’, ‘Windows-1255’, val)
# val = val.first
# end
logger.info val
val = Iconv.iconv(
“utf8”,“Windows-1255”,val)
logger.info val = val.first
if col_idx == indexes[:tsk_type]
vals << check_if_id(val,task_type_ids)
elsif col_idx == indexes[:customer]
vals << check_if_id(val,customer_ids)
elsif col_idx == indexes[:area]
vals << check_if_id(val,area_ids)
elsif col_idx == indexes[:status]
vals << check_if_id(val,status_ids)
elsif col_idx == indexes[:date_for_perform]
if val.blank?
vals << ‘null’
else
vals<< val
end
elsif col_idx == indexes[:cost]
if val.blank?
vals << 0
else
vals<< val
end
else
val = val.gsub("’","’’") unless
val.blank?
vals << “’#{val}’”
end
end
end
if vals.length < indexes.length
(indexes.length - vals.length).times { vals <<
“null” }
end
vals <<
“#{book_id},#{user_id},’#{time_to_db}’,’#{time_to_db}’”
values << “(#{vals.join(”,")})"
end
end

            unless values.blank?
               begin
                  con = DBConnection.open
                  str_query = "insert into tasks 

(date_for_perform,customer_id,order_number,license_number,
company_name,client_name,phone_number,city,street,home_number,tsk_type_id,remarks,area_id,hour,tsk_status_id,
institute,cost,book_id,user_id,created_at,updated_at)
values #{values.join(’,’)}"
logger.info str_query
con.query(str_query)
rescue Exception => ex
return ex
end
end
end

On Sun, Nov 14, 2010 at 1:05 PM, Shir S. [email protected] wrote:

hi
i do it wuth :fasterCSV,
and using- windows-1255, and convert it to-utf-8.
thank you

Sorry, I couldn’t run that code. Maybe pasting in the forum messed it
up. I tried a simple test with double quotes and FasterCSV always
throws:

Illegal quoting on line 2. (FasterCSV::MalformedCSVError)

If that is what you are seeing too, then it’s probably a formating
issue. The best solution is to export the csv file with legal
formatting, but if that’s not possible, you can replace the " with
single quotes as a last resort. But be careful, this is brute force
and can break other things. For example:

read the file and replace the " with ’

data = File.read data_file
data.gsub!(’"’, “’”)

parse instead of open

row_index = 0
FasterCSV.parse(data) do |row|
row_index +=1
next if row_index == 1

converted = row.map {|val| Iconv.iconv(“utf8”,“Windows-1255”,val)}
puts converted.inspect
end

If you have a small 2 to 3 line sample of the input (not real data), I
could try to help more.

Hope that helps,
Ammar