Introduction to Importing from CSV Discussion
David Zhu
Mike Goggin
I had to come up with something similar at work a few years ago.
We have clients that need to mass create auction lots and send in Excel spreadsheets full of lot data. We needed a way to easily throw the XLS converted to CSV at the app and have it create the Lot objects. Supports blacklisting of columns that can be assigned from the CSV and typecasting column data.
This code is almost 3 years old and needs a serious refactor, but we don't use it enough to warrant sinking more time in to it right now.
(Using the end of the video to reference line numbers)
Why did you choose to use `user = User.create(...params...)` on L14, followed by `counter+=1 if user.persisted?` on L16 rather than `user = User.new(...params...)` and `counter+=1 if user.save`. I don't necessarily feel one is better than the other but I've always tended towards the latter. Is there an advantage one way or the other?
Great question. Either way works just the same in this case.
The only difference is that you have to worry about where the .save happens if you use that. With create, it doesn't really matter what order the next code is in. If you did save, you'd have to make sure it came first. Also, where the "save" happens is less clear if you have it on the right side of a line that has an "if" statement tacked on it.
In the end it doesn't make much difference at all because this code is very rough anyways. Ideally this gets refactored so that responsibilities are separated in a much cleaner fashion. More on that in a future episode though.
I'd love to see some information about how to generate some more robust dummy data as well perhaps using something like Faker (https://github.com/stympy/f...
Routine Tracker
Hey @excid3:disqus. Has your caching just not caught up with the typo in the title? Seems like it should be "Introduction to Importing from CSV".
Andrew Cockerham
Thanks for a great tutorial on CSV's it's just what I needed. As an aside, I was recently going through my gems and noticed FasterCSV as a dependency in a couple of them. Have you had a chance to play with it much?
I believe FasterCSV got rolled into Ruby 2.x at some point so you don't need to use it anymore.
Kohl Kohlbrenner
@excid3:disqus when you do email,first,last = row , how is each variable assigned? On each comma in the csv row?
Yeah, it's the items in the row in order. I just used a Ruby trick to split the array of items into separate variables.
irb(main):001:0> a, b, c = ["a", "b", "c"]
=> ["a", "b", "c"]
irb(main):002:0> a
=> "a"
irb(main):003:0> b
=> "b"
irb(main):004:0> c
=> "c"
A big difficulty is gathering the errors and viewing them in the resulting rails view...
Definitely. This is where form objects tend to make a lot more sense because it's that much more complicated. An intermediate step of uploading, processing, and finding errors before doing the actual import can be a good thing for this too.
Hey Chris, i'm setting up this feature for my app now. I've used the commentable episode to help me structure the app so that users can create a category and then add a startup(s) to that category. Essentially, startups in my app are startupables I'd like to be able to upload a csv of startups for a particular category. Any ideas?
Sophie Zheng
I wrote a few rake tasks to import csv but i wonder if it is possible to write tests to make sure whether or not the data point in the right table and right cell...
milley
Hey Chris,
I have added the functionality to import CSV files inside model. I want to rollback the transaction if any of the value is invalid in CSV file. How can i achieve that?
You should be able to just wrap it in a transaction block like so: http://api.rubyonrails.org/...
Hey Chris,
Just joined your site. Thanks for all the great videos. Was working through importing a CSV and I had a quick question regarding removing headers from the file.
In my application I had to use the following code for the loop:
CSV.foreach(filename, "r:ISO-8859-1") do |row|
When I tried adding the argument to ignore the header it didn't work. Is there something I'm missing?
The second argument there needs to be hash I'm pretty sure. You're passing in a string, so that's probably going to mismatch the arguments it expects. Try changing that to hash and that should fix it for you!
sigumagwa
Hi Chris,
I'm stuck trying to import records from an excel spreadsheet.
Is there a way to convert my xls file into a csv file so I can follow along with your tutorial?
If not, please can you recommend a source of an excel importing tutorial.
In addition to trying to adapt your tutorial, I have tried each of these and can't get any of them to work:
http://www.mattmorgante.com...
https://richonrails.com/art...
https://deepakrip007.wordpr...
The problem seems to be one that other users here have encountered with their attempts. All of my attempts at solving this are outlined here: http://stackoverflow.com/qu...
Thanks
Mel
I have now found the roo, creek, spreadsheet and dullard gems. They talk about needing to convert xls files (which I have) to xlsx format. It all looks confusing. Maybe you could add another video to show how to change xls into csv so that these tutorials can be used to import data or you could do an overview of whichever of these gems is most suitable for importing data from xls files. I'm about to start trying with each of them now.
Hi Chris, I think I might have converted my xls file to a csv file and saved it to my root file. I've tried to follow this tutorial but keep getting errors when I try to run the rake command in the console. When you use the line be rake import:users, is the reference to 'users' a reference to the name of the model, the name of the task or something else? My task is called 'for_codes', my model is called Randd::Field. I've tried both in the console but keep getting errors that say: "NameError: undefined local variable or method `for_codes' for main:Object"
I've asked here to see if anyone else can see what's wrong: http://stackoverflow.com/qu...
Is anyone getting this error when trying to rake the task?
rake aborted!
ArgumentError: invalid byte sequence in UTF-8
So - it seems that saving my .xls file with a .csv extension inside my rails app (root level) didn't work to make the file eligible to be treated as a CSV file. I've now tried copying and pasting the content of my 2 column excel file into the .csv file saved in my rails app.
That pasted several hundred lines but without the | separating the cell content.
This is the first 3 lines (the first one is the header).
Is there a way to insert the pipes without having to go line by line to paste them in?
anz_referencetitle
010104pCombinatorics and Discrete Mathematics (excl. Physical Combinatorics)
010107pMathematical Logic, Set Theory, Lattices and Universal Algebra
Can anyone make sense of these instructions for selecting the first column of a long list of text? https://atom.io/packages/su...
I'm trying to manually add the "|" to separate the first and second column of the content of my xls file which is now pasted into a .csv file in my rails app. there are 1000s of lines.
I can't understand what it's telling me to do so that I can select the first column and then press paste to separate it from the second bit of content with the "|"
So - it seems that saving my .xls file with a .csv extension inside my rails app (root level) didn't work to make the file eligible to be treated as a CSV file. I've now tried copying and pasting the content of my 2 column excel file into the .csv file saved in my rails app.
That pasted several hundred lines but without the | separating the cell content.
This is the first 3 lines (the first one is the header).
Is there a way to insert the pipes without having to go line by line to paste them in?
anz_referencetitle
010104pCombinatorics and Discrete Mathematics (excl. Physical Combinatorics)
010107pMathematical Logic, Set Theory, Lattices and Universal Algebra