SEEKING FREELANCER - Importing CSV file
I've tried to follow along with Chris' tutorial on importing records from a CSV file.
I cant get the file to upload.
I've posted loads of quesitons on the discussion board for the first video in the tutorial series.
If you are able to answer those questions - I'd be happy to pay for help to get the answers.
Thanks
https://gorails.com/episodes/intro-to-importing-from-csv?autoplay=1
Hey Melanie, Just to be clear, you can't get your code to parse (read) the CSV data or you can't get the CSV file to upload to your server?
What have you tried so far?
Hi John
I tried all the things I set out in the posts.
Here is my most recent SO post trying to find a way to get this tutorial working - which sets out the most recent error and the problems I'm having in following the tutorial.
http://stackoverflow.com/questions/41204967/rails-5-rake-task-to-import-data-from-csv-file
The following line in your stack trace usually means that your CSV file is not encoded to the UTF-8 specifications.
ArgumentError: invalid byte sequence in UTF-8
You would need to specify the encoding you're using in your CSV.foreach line. For example, if you're using MS Excel to save your CSV file, you might try the following:
CSV.foreach(file, "r:windows-1250", headers: true)
Notice the "r:windows-1250" ... That sets the encoding that the CSV library should read.
Or, you could use Google Spreadsheets to create the CSV and then download that file to use. I believe it uses UTF-8 Encoding.
Another thing you should do is explicitly tell Ruby what the column separator is. By default, it's a comma. So, if you use something else, you could throw the CSV library off.
You can do that by adding col_sep: "|" to your foreach line. For example
CSV.foreach('file.csv', headers: false, col_sep: '|')
```
I created my own CSV file with UTF-8 encoding and I tested your code using the following and it works just fine:
```
require 'csv'
namespace :import do
desc "import research fields from csv"
task :fields do
counter = 0
CSV.foreach('file.csv', headers: true, col_sep: '|') do |row|
p row
counter += 1
end
puts "Imported #{counter} field codes"
end
end
```
Try this:
Resave the information in that CSV file using Google Spreadsheets, replace the current one you have, and then try again with the following:
```
require 'csv'
namespace :import do
desc "import research fields from csv"
task rannd_fields: :environment do
filename = File.join Rails.root, 'for_codes.csv'
counter = 0
CSV.foreach(filename, headers: true, col_sep: '|') do | row |
p row["anz_reference"]
for_code = Randd::Field.create(anz_reference: row["anz_reference"], title: title)
counter += 1 if randd_field.persisted?
end
puts "Imported #{counter} field codes"
end
end
```
If you receive an error, please post the backtrace here.
Oh, this is more of a personal preference but you may want to try adding "header_converters: :symbol" to that foreach line as well. It turns your header row into symbols so that you can call them using something like row[:header_name] - For example:
```
CSV.foreach(file, headers: true, header_converters: :symbol)
```
Sorry, I just realized that if you're using Google Spreadsheets to create and save the CSV file, then you don't need to use the col_sep: '|' option. You can emit those from your the line:
CSV.foreach(filename, headers: true, col_sep: '|') do | row |
So that is reads:
CSV.foreach(filename, headers: true) do | row |
Hi John
I'm not using google spreadsheets. I tried moving the xls content to the google drive and then using that google drive sheet download to upload as a csv file. I get this error message:
undefined method `[]' for nil:NilClass
That line points to an error with the create action in the controller, which has:
def create
# @field = Randd::Field.new(randd_field_params)
Randd::Field.create(params[:randd_field][:file])
redirect_to action: "index", notice: "Data imported"
end
I originally was trying with xls. That didnt work at all.
I then tried to convert my xls file to a csv. That gave me the UTF 8 error that I posted.
I then tried manually copying and pasting data from the spreadhseet into a file I created in my app (root path) and saved as a CSV. I enterted the | separators manually. That didnt work either. In that case the error I get is the most recent post I made on the tutorial discussion thread.
I just tried your suggestion to specify the form of separator as col_sep: '|'. I get the same error as I get when I try without that component.
Thanks anyway for trying to help me.
Mel
I've had a break from trying to solve this for a few weeks and am now back. I cant get the process Chris used in his CSV upload video to work. I've just finished attempting to get the records into the table with the roo gem but that hasnt worked either.
Can you recommend a tutorial, text or topic that i can search for to figure out how to uopload records from an xls file into my db?
Thank you
Hi Ryan
Thanks a lot for the suggestion.
I'm the Melanie that has filled up the comments section on that article. I couldnt get that working. It seems from the comments that a few people had issues - although they had problems different to my own.
Thanks again for the suggestion.
Melanie
Hi Mel, in your post on StackOverflow you have the route
collection {post :import }#do
# post :create
end
have you tried changing that to
collection {post :import }
The end at the end might have been causing problems.
Hi David,
Thanks very much for taking a look.
The routes are:
namespace :randd do
resources :maturities
resources :activities
resources :purposes
resources :fields do
collection {post :import }
end
end
One 'end' is for the "fields" and the other is for the "randd".
It's probably unclear because one of the other tutorials I tried to use showed to do it using the post :create line that I showed as commented out (which needed the extra end).
I think its right the way it is above or do you think there is another way to try to express this?
I've compared it to code that I have to do the same thing and it's identical so can't see anything else unfortunately. Only other thing is to check your CSV file. Open it in a text editor and try to use only 2 or 3 rows.
I had problems with CSV importing myself but it turned out to be the CSV file rather than the code.
Thanks very much David.
One of the things I tried was to manually type a couple of lines in by hand and try to rake that file instead of an imported CSV that i saved into the app.
I still get the same error.
NameError: undefined local variable or method `title' for main:Object
Someone on SO suggested that error might be attributable to:
not specifying row for the title field.
referencing randd_fields (table) instead of for_codes (csv file) in the import counter method.
They suggested that I try changing the rake task to add a row reference around the title attribute and replacing the reference to randd_fields in the counter line as follows:
require 'csv'
namespace :import do
desc "import research fields from csv"
task rannd_fields: :environment do
filename = File.join Rails.root, 'for_codes.csv'
counter = 0
CSV.foreach(filename, headers: true) do | row |
p row ["anz_reference"]
for_code = Randd::Field.create(anz_reference: row["anz_reference"], title: row['title'])
counter += 1 if for_code.persisted?
end
puts "Imported #{counter} field codes"
end
end
but - I still get the same error.
Thanks anyway for trying to help me.
It might now be faster if I enter my 2000 records by hand instead of trying to learn to import them via a CSV upload.
Sharing - just in case it helps anyone else (although I think I'm bringing up the rear in the learning curve)
This is the rake task that worked for me:
require 'csv'
namespace :import do
desc "import research fields from csv"
task randd_fields: :environment do
filename = File.join Rails.root, 'for_codes.csv'
counter = 0
CSV.foreach(filename, headers: true) do | row |
p row ["anz_reference"]
for_code = Randd::Field.create(anz_reference: row["anz_reference"], title: row['title'])
counter += 1 if for_code.persisted?
end
puts "Imported #{counter} field codes"
end
end
The key changes are:
put a separate row reference in for the second field.
check what is being used in the counter. Instead of the table name, count from the file field.
Thanks very much again to everyone for helping me