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"