Ruby: data doesn't have quotation mark but CSV.parse raises CSV::MalformedCSVError: Illegal quoting in line x

Viewed 59

I'm using ruby CSV.parse to read live data from Google sheet for my Rails project. In my seeds.rb file, I'm reading csv data like this

point_csv_path = "https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet=points"
CSV.parse(open(point_csv_path), :headers=>true).each do |row|
   Point.create!(
      point_id: row["point_id"].to_i, 
      point_type: row["point_type"], 
      identifier: row["identifier"].to_i, 
      longitude: row["longitude"],
      latitude: row["latitude"],
      location: row["location"],
      ordering: row["ordering"].to_i)
end

For the data in google sheet,I manually copy and paste from online source and the data doesn't contain any quotation mark at all. The EXACT first 3 lines of data in the google sheet is the following:

point_id    point_type  identifier  longitude   latitude    location    ordering
1           sensor      1           -71.050054  42.360152   null        0
2           sensor      2           -71.049428  42.359373   null        0
3           sensor      3           -71.049337  42.358969   null        0

After I ran $ rails db:seed, I get the following error message

rails aborted!
CSV::MalformedCSVError: Illegal quoting in line 3.
/media/anyan/file/rails_project/db/seeds.rb:33:in `<main>'
/media/anyan/file/rails_project/bin/rails:9:in `<top (required)>'
/media/anyan/file/rails_project/bin/spring:15:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:seed
(See full trace by running task with --trace)

I know there are a lot of similar questions on stackoverflow but none of them I tried solve my problem. For example, adding :quote_char => "|" or :encoding => 'bom|utf-8' as argument for CSV.parse(). I thought it was something related to reading csv data stream so I downloaded the data to disk and I have the same error. How can I solve this problem? Thanks!

UPDATE: after download csv file to local disk and open with VSCode, the first 3 lines of data is:

"point_id","point_type","identifier","longitude","latitude","location","ordering"
"1","sensor","1","-71.050054","42.360152","null","0"
"2","sensor","2","-71.049428","42.359373","null","0"
"3","sensor","3","-71.049337","42.358969","null","0"
0 Answers
Related