6

Despite of numerous SO threads on the topic, I'm having trouble with parsing CSV. It's a .csv file downloaded from the Adwords Keyword Planner. Previously, Adwords had the option of exporting data as 'plain CSV' (which could be parsed with the Ruby CSV library), now the options are either Adwords CSV or Excel CSV. BOTH of these formats cause this problem (illustrated by a terminal session):

file = File.open('public/uploads/testfile.csv')
 => #<File:public/uploads/testfile.csv> 

file.read.encoding
 => #<Encoding:UTF-8> 

require 'csv'
 => true 

CSV.foreach(file) { |row| puts row }
ArgumentError: invalid byte sequence in UTF-8

Let's change the encoding and see if that helps:

file.close
 => nil 

file = File.open("public/uploads/testfile.csv", "r:ISO-8859-1")
 => #<File:public/uploads/testfile.csv> 

file.read.encoding 
=> #<Encoding:ISO-8859-1> 

CSV.foreach(file) { |row| puts row }
ArgumentError: invalid byte sequence in UTF-8

Let's try using a different CSV library:

require 'smarter_csv'
 => true 

file.close
 => nil 

file = SmarterCSV.process('public/uploads/testfile.csv')
ArgumentError: invalid byte sequence in UTF-8

Is this a no-win situation? Do I have to roll my own CSV parser?

I'm using Ruby 1.9.3p374. Thanks!

UPDATE 1:

Using the suggestions in the comments, here's the current version:

file_contents = File.open("public/uploads/new-format/testfile-adwords.csv", 'rb').read

require 'iconv' unless String.method_defined?(:encode)
if String.method_defined?(:encode)
  file_contents.encode!('UTF-16', 'UTF-8', :invalid => :replace, :replace => '')
  file_contents.encode!('UTF-8', 'UTF-16')
else
  ic = Iconv.new('UTF-8', 'UTF-8//IGNORE')
  file_contents = ic.iconv(file_contents)
end

file_contents.gsub!(/\0/, '') #needed because otherwise, I get "string contains null byte (ArgumentError)"

CSV.foreach(file_contents, :headers => true, :header_converters => :symbol) do |row|
  puts row
end

This doesn't work - now I get a "file name too long" error.

fullstackplus
  • 1,061
  • 3
  • 17
  • 31
  • can you provide an example of the file you are trying to parse? – benjaminjosephw Dec 21 '13 at 10:41
  • can you just `puts file.read` without the exception? – Малъ Скрылевъ Dec 21 '13 at 10:49
  • @benjaminjosephw Here's the exact file I'm using: http://jamesabbottdd.com/examples/testfile.csv – fullstackplus Dec 21 '13 at 11:55
  • @majioa `CSV.foreach(file) { puts file.read }` produces the exact same error, at the same line: from ../.rvm/rubies/ruby-1.9.3-p374/lib/ruby/1.9.1/csv.rb:2058:in `=~' – fullstackplus Dec 21 '13 at 12:00
  • @majioa If you look closely at the code I've provided in the question, you'll see `file.read.encoding` being successfully executed. Do you think this would be the case if `file.read` would result in an error being thrown? – fullstackplus Dec 21 '13 at 12:13
  • 1
    @user906230 - does this help? http://stackoverflow.com/a/8873922/2463468 – benjaminjosephw Dec 21 '13 at 13:09
  • Did you read [the documentation](http://ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV.html#class-CSV-label-CSV+and+Character+Encodings+%28M17n+or+Multilingualization%29)? – Sergei Dec 21 '13 at 13:23
  • @Sergey Yes, and I am none the wiser: "Anyway, the practical side of this is simple: make sure IO and String objects passed into CSV have the proper Encoding set and everything should just work." Everything doesn't "just work" - hence the original question. – fullstackplus Dec 21 '13 at 17:01
  • @benjaminjosephw Thanks for the link - I thought the ´iconv´ approach was outdated but will try. – fullstackplus Dec 21 '13 at 17:03
  • Can you try `File.open( filename, "r:ISO-8859-1:utf-8")`? This reads as ISO-8859-1 (are you sure that's the correct encoding?) and converts it to utf-8. CSV then gets a UTF-8 string. – knut Dec 21 '13 at 20:02

3 Answers3

18

Looking at the file in question:

 $ curl -s http://jamesabbottdd.com/examples/testfile.csv | xxd | head -n3
0000000: fffe 4300 6100 6d00 7000 6100 6900 6700  ..C.a.m.p.a.i.g.
0000010: 6e00 0900 4300 7500 7200 7200 6500 6e00  n...C.u.r.r.e.n.
0000020: 6300 7900 0900 4200 7500 6400 6700 6500  c.y...B.u.d.g.e.

The byte order markffee at the start suggests the file encoding is little endian UTF-16, and the 00 bytes at every other position back this up.

This would suggest that you should be able to do this:

CSV.foreach('./testfile.csv', :encoding => 'utf-16le') do |row| ...

However that gives me invalid byte sequence in UTF-16LE (ArgumentError) coming from inside the CSV library. I think this is due to IO#gets only returning a single byte for some reason when faced with the BOM when called in CSV, resulting in the invalid UTF-16.

You can get CSV to strip of the BOM, by using bom|utf-16-le as the encoding:

CSV.foreach('./testfile.csv', :encoding => 'bom|utf-16le') do |row| ...

You might prefer to convert the string to a more familiar encoding instead, in which case you could do:

CSV.foreach('./testfile.csv', :encoding => 'utf-16le:utf-8') do |row| ...

Both of these appear to work okay.

matt
  • 78,533
  • 8
  • 163
  • 197
2

Converting the file to UTF8 first and then reading it also works nicely:

iconv -f utf-16 -t utf8 testfile.csv | ruby -rcsv -e 'CSV(STDIN).each {|row| puts row}'

Iconv seems to understand correctly that the file has a BOM at the start and strips it off when converting.

Tom De Leu
  • 8,144
  • 4
  • 31
  • 30
0

There are two things to solve here when dealing with the AdWords Keyword Planner download. For one is the encoding.

$ file Keyword\ Stats\ 2019-02-12\ at\ 19_04_53.csv
Keyword Stats 2019-02-12 at 19_04_53.csv: Little-endian UTF-16 Unicode text, with very long lines

And the fact that the delimiters are tabs and not commas!

So stepping over the CSV file is as simple as this:

CSV.foreach('Keyword Stats 2019-02-12 at 19_04_53.csv', col_sep: "\t", encoding: 'utf-16le:utf-8') do |row|
  puts row
end

FYI: The \t must be in double quotes so it would be interpreted as a tab and not the string \t.

Nothus
  • 1,127
  • 8
  • 7