Importing many 'dirty' .csv records..what approach is best?

rubyonrails-talk
1

I am tasked with importing many .csv files into an application that has a 'scoped' database that services many distinct customers. I'm sure it comes as no surprise that many of these will be damaged in some way with spurious data :slight_smile: Initially, I'm using FasterCSV in a dbmigrate file to become more familiar with this task.

Here is an example of how I'm approaching this;

class ImportAssets < ActiveRecord::Migration   def self.up      n=0      d=0      FasterCSV.foreach("D:/Rails/assets/convert/assets.csv") do |row|        c = Asset.new        c.asset = row[0] # 0        c.description = row[1] # 1        c.location = row[2] # 2        if c.save          n += 1        else          d = Badboy.new          d.converttable = 'Asset'          d.keystring = row[0]          d.message = 'Asset Record Failed to convert'          d += 1          d.recno = d          d.save        end      end   end end

I find that regardless of the errors in the incoming .csv file, I'm unable to get my logic to branch and save the errors to a 'badboys' exception table. Firstly, does anyone in our group have to deal with this type of situation...do you use a controller approach of some sort or do you load the details for each new customers' data by adding one more dbmigrate file? Why am I not able to create a 'badboy' record when my incoming record doesn't save? I am grateful for any links or suggestions. Thank you, Kathleen

2

I am tasked with importing many .csv files into an application that has a 'scoped' database that services many distinct customers. I'm sure it comes as no surprise that many of these will be damaged in some way with spurious data :slight_smile: Initially, I'm using FasterCSV in a dbmigrate file to become more familiar with this task.

Here is an example of how I'm approaching this;

class ImportAssets < ActiveRecord::Migration def self.up     n=0     d=0     FasterCSV.foreach("D:/Rails/assets/convert/assets.csv") do |row|       c = Asset.new       c.asset = row[0] # 0       c.description = row[1] # 1       c.location = row[2] # 2       if c.save         n += 1       else         d = Badboy.new         d.converttable = 'Asset'         d.keystring = row[0]         d.message = 'Asset Record Failed to convert'         d += 1

There's something fishy here probably partly as a result of using
nondescript variable names like n,c,d You're using d both for some sort of counter and also for your new
instance of Badboy

Fred

3

Fred, Thanks for the observation. I made that change you mentioned but it has no bearing on the issue as the program logic never got down to the Badboy area. Still hoping for some suggestion? Thanks, Kathleen

4

The code looks ok, (with the exception mentioned by Fred) Are there any validations on Badboy that may prevent saving? (only thing I could imagine that could go wrong here). Main suspect is "keystring", which gets data, that just made Asset fail. I guess, that there must be some validations on Assets, since you expect them to fail on save.

Otherwise I would debug the thing (or use the log) and see, where it ends up after the Asset save. Does it run the Badboy part at all and fails saving or does it never reach it?

5

I am tasked with importing many .csv files into an application that has a 'scoped' database that services many distinct customers. I'm sure it comes as no surprise that many of these will be damaged in some way with spurious data :slight_smile: Initially, I'm using FasterCSV in a dbmigrate file to become more familiar with this task.

Here is an example of how I'm approaching this;

class ImportAssets < ActiveRecord::Migration   def self.up      n=0      d=0      FasterCSV.foreach("D:/Rails/assets/convert/assets.csv") do |row|        c = Asset.new        c.asset = row[0] # 0        c.description = row[1] # 1        c.location = row[2] # 2        if c.save          n += 1        else          d = Badboy.new          d.converttable = 'Asset'          d.keystring = row[0]          d.message = 'Asset Record Failed to convert'          d += 1          d.recno = d          d.save        end      end   end end

I find that regardless of the errors in the incoming .csv file, I'm unable to get my logic to branch and save the errors to a 'badboys' exception table. Firstly, does anyone in our group have to deal with this type of situation...do you use a controller approach of some sort or do you load the details for each new customers' data by adding one more dbmigrate file? Why am I not able to create a 'badboy' record when my incoming record doesn't save? I am grateful for any links or suggestions.

6

Fred, Thanks for the observation. I made that change you mentioned but it has no bearing on the issue as the program logic never got down to the Badboy area. Still hoping for some suggestion? Thanks, Kathleen

Does c.save ever return false (ie do you have validations on it?)?
Have you tried stepping through this in the debugger to see what's up?

Fred