Csv import - encoding problem


#1

I’m trying to load a CSV file into my rails app using a migration and
FasterCSV and running into problems.

I’ve had this working before when the CSV file has been saved out of
excel, I’d like to do it with numbers now but I can’t quite get it to
work as is. I can’t believe that excel does a better job with CSV than
numbers (I don’t want to believe it)! The option I’m using in numbers to
do the export, is Share -> Export as CSV (set the encoding to UTF8, I
could try with a different option in numbers if that might help).

This seems like an encoding problem, is there a way I can handle this in
the migration.

Here’s the error message the migration spits out….

Unquoted fields do not allow \r or \n (line 2).


Be grateful for any help at all.


And here’s the migration file….

require ‘fastercsv’
class LoadData < ActiveRecord::Migration

def self.up
count=0
FasterCSV.foreach("#{RAILS_ROOT}/db/migrate/fixtures/fire.csv",
:row_sep => “\r”) do |row|
forename,jobtitle,initials,surname,departmentname,jobfunction,companyname,addressline1,addressline2,addressline3,addressline4,town,postalcode,country,region,telephone,fax,email,web,persontelephone,personfax,personemail
= row
# name,url = row
# Contact.create(:name => name, :url => url)
Contact.create(

  :forename => forename,
  :job_title => jobtitle,
  :initials => initials,
  :surname => surname,
  :department => departmentname,
  :job_role => jobfunction,
  :company_name => companyname,
  :address1 => addressline1,
  :address2 =>  addressline2,
  :address3 => addressline3,
  :address4 => addressline4,
  :town => town,
  :postcode => postalcode,
  :country => country,
  :region => region,
  :telephone => telephone,
  :fax => fax,
  :email => email,
  :web => web,
  :personal_phone => persontelephone,
  :personal_fax => personfax,
  :personal_email => personemail

  )

  count += 1
  # puts
  # puts count.to_s + " Name: " + row[1]
  # puts row.to_yaml
  # puts
  # puts row.inspect
  # puts row[1]
  # puts record.url
end
puts count.to_s + " records inserted."
records = Contact.find(:all)
# puts records.count

end

def self.down
puts “not doing anything”
end

end


#2

anyone help with this.

I’m not sure what’s happening here. Maybe my data contains line feeds
within the cells of data itself and that’s throwing it out?

Another thought is that I could go into numbers and do a find replace on
the carriage return character…just not sure?

What does this mean?

Unquoted fields do not allow \r or \n (line 2).


#3

This command did it for me, in the terminal…

tr -d ‘\n’ < edit_unicode_utf8.csv > fire_new.csv

stripped out \n i suppose.


#4

put double quotes around any fields that contain a return character or a
comma. Not sure if you have to escape return characters or not, but
you’ll
find out :slight_smile:

Be sure to escape any double quotes

On Feb 3, 2009 7:08am, bingo bob removed_email_address@domain.invalid
wrote:

within the cells of data itself and that’s throwing it out?

–~--~---------~–~----~------------~-------~–~----~

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.

To post to this group, send email to removed_email_address@domain.invalid

To unsubscribe from this group, send email to
removed_email_address@domain.invalid

For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en


#5

Looks like Ruby has some support for writing csv files!

http://www.ruby-doc.org/stdlib/libdoc/csv/rdoc/classes/CSV/Writer.html

On Feb 3, 2009 10:30am, bingo bob removed_email_address@domain.invalid
wrote:

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.

To post to this group, send email to removed_email_address@domain.invalid

To unsubscribe from this group, send email to
removed_email_address@domain.invalid

For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en