Jason, put your csv file in the private subfolder of your application
and call it "paint.csv" and then add the following code to your
controller and it should work. (Well at least it imported the few
records you provided, I haven't taken the time to totally check that
everything lines up.
~Brian
def import_formulas():
import csv
import os.path
csv_path = os.path.join(request.folder,'private','paint.csv')
paint_formulas = csv.reader(open(csv_path))
header = None
#variable to store the paints we've already put in
#database (avoiding duplications and/or having to
#constantly query db)
paints = dict()
#variable to store the colorants already put in database
known_colorants = dict()
#we'll use them to help get all the colorants
#these are the indexes that have colorant names
colorant_indexes = [2,4,6,8,10]
db.paint.truncate()#forget all paints for now so don't duplicate.
for formula in paint_formulas:
formula_colorants = [] #will hold the colorants in this
formula, however many there are
if not header:
header = formula #give value
#and do nothing else cause it's just the column names
else:
#it's a paint formula
#print formula
#print "=================="
name = formula[0]
base_coat = formula[1]
#deal with the colorants
for c in colorant_indexes:
if formula[c] <> None and formula[c] <> '':
colorant = formula[c]
amount = formula[c+1] #next index is the amount
#there's a colorant value to add
if colorant in known_colorants:
#already have this colorant in the database
colorant_id = known_colorants[colorant]
#retreive id from our knowns
else:
#new colorant, add to database
known_colorants[colorant]=db.colorant.insert
(name=colorant)
colorant_id = known_colorants[colorant]
#print "colorant id", colorant_id
#now add to this formula's list of colorants
formula_colorants.append([colorant_id, amount])
else:
#no more colorants
pass
#add the paint
if not name in paints:
paints[name]=db.paint.insert(name=name, base_coat =
base_coat)
#then add each of the colorants
for colorant in formula_colorants:
db.paint_colorants.insert(paint_id=paints[name],
colorant_id=colorant[0], units = colorant[1])
my_paints = db((db.paint.id>0) & (db.paint_colorants.paint_id ==
db.paint.id) & (db.colorant.id ==
db.paint_colorants.colorant_id)).select(orderby=db.paint.name|
db.colorant.name)
return dict(my_paints = my_paints)
On Jan 11, 7:42 am, Jason Brower <encomp...@gmail.com> wrote:
> Your requested some more data...
> "paint_name","base_paint","colorant","amount","colorant","amount","colorant
> ","amount","colorant","amount","colorant","amount"
> "10 JORASDUN",C,DD1,56,fS1,42,So1,110,AA1,8,,
> "10 SVFGH",W,To1,180,,,,,,,,
> 1002-DG5R,R,Co2,30,la1,4,To1,5,,,,
> 1008-Y24R,A,DD2,88,la1,17,Fo1,60,,,,
> "147 BARWSSFD",E,do1,74,fa1,38,Ha1,44,,,,
> "148 DUNKNMFRUN",D,Do1,56,Fa1,42,Go1,110,ti1,8,,
> Thank you for your help in this regard, it's a complicated process
> relative to what I am used to doing.
> BR,
> Jason Brower
>
>
>
> On Sat, 2010-01-09 at 07:32 -0800, Brian M wrote:
> > paint_formulas is supposed to get the contents of the csv file. (See
> >http://docs.python.org/library/csv.html) It should be populated by the
> > line:
> > paint_formulas = csv.reader(csvfile)
> > where csv file should be the path to your csv file.
>
> > Actually, I think that line should read like this instead:
> > paint_formulas = csv.reader(open(csvfile)) #was missing the open()
> > before
>
> > Try this as a test of reading the csv file in a controller (should
> > only show the first few records [0:10] instead of all 14K or whatever
> > you've got).
>
> > def read_csv():
> > import csv
> > paint_formulas = csv.reader(open(csvfile))
> > return dict(paint_formulas = paint_formulas[0:10], count = len
> > (paint_formulas))
>
> > On Jan 9, 1:36 am, Jason Brower <encomp...@gmail.com> wrote:
> > > It is much more understandable. But it seems that the name
> > > paint_formulas is not defined. What is supposed to be populated there?
> > > Best Regards,
> > > Jason
>
> > > On Fri, 2010-01-08 at 20:43 -0800, Brian M wrote:
> > > > Jason,
> > > > What's the syntax error?
> > > > Try this, it's untested but should be verbose enough to get you there.
> > > > If you have trouble perhaps provide a sample file (few dozen records)
> > > > that we can test against.
>
> > > > import csv
> > > > paint_formulas = csv.reader(csvfile)
> > > > header = None
>
> > > > #variable to store the paints we've already put in
> > > > #database (avoiding duplications and/or having to
> > > > #constantly query db)
> > > > paints = dict()
>
> > > > #variable to store the colorants already put in database
> > > > known_colorants = dict()
>
> > > > #these are the indexes that have colorant names
> > > > #we'll use them to help get all the colorants
> > > > colorant_indexes = [2,4,6,8,10]
>
> > > > for formula in paint_formulas:
> > > > formula_colorants = [] #will hold the colorants in this formula,
> > > > however many there are
> > > > if not header:
> > > > header = formula #give value
> > > > #and do nothing else cause it's just the column names
> > > > else:
> > > > #it's a paint formula
> > > > name = row[0]
> > > > base = row[1]
> > > > #deal with the colorants
> > > > for c in colorant_indexes:
> > > > if row[c] <> None:
> > > > colorant = row[c]
> > > > amount = row[c+1] #next index is the amount
> > > > #there's a colorant value to add
> > > > if colorant in known_colorants:
> > > > #already have this colorant in the database
> > > > colorant_id = known_colorants[colorant]#retreive
> > > > id from our knowns
> > > > else:
> > > > #new colorant, add to database
> > > > known_colorants[colorant]=db.colorant.insert
> > > > (name=colorant)
> > > > colorant_id = known_colorants[colorant]
>
> > > > #now add to this formula's list of colorants
> > > > formula_colorants.append(dict(id = colorant_id, amount
> > > > = amount))
> > > > else:
> > > > #no more colorants
> > > > pass
>
> > > > #add the paint
> > > > if not name in paints:
> > > > paints[name]=db.paint.insert(name=name)
>
> > > > #then add each of the colorants
> > > > for colorant in formula_colorants:
> > > > db.paint_colorants.insert(paint_id=paints[name],
> > > > base_coat = base,
> > > > colorant_id=colorant[id], units = colorant
> > > > [amount])
>
> > > > Good Luck,
> > > > Brian
>
> > > > On Jan 8, 7:57 pm, Jason Brower <encomp...@gmail.com> wrote:
> > > > > I tried the script but couldn't get through the syntax error. Sorry,
> > > > > lots of commands I don't know there. :/
> > > > > BR,
> > > > > Jason
>
> > > > > On Fri, 2010-01-08 at 09:17 -0800, Brian M wrote:
> > > > > > I have been working on using web2py to import csv files and find
> > > > > > that
> > > > > > in general it works very well. You will need to be careful with
> > > > > > pulling in the colorant and amount fields - if you use the
> > > > > > csv.DictReader() to refer to columns in the CSV file by name instead
> > > > > > of index you'll find that you only get the value for the *last*
> > > > > > colorant and amount. In other words, just using row['Colorant'] and
> > > > > > row
> > > > > > ['Amount'] will very likely cause you to loose data - you've got 5
> > > > > > 'Colorant' entries in a single row, so each one in a row simply
> > > > > > over-
> > > > > > writes the earlier ones. You will most likely need to use the
> > > > > > indexes, or just rename the column headers in the csv file so they
> > > > > > are
> > > > > > unique.
>
> > > > > > ~Brian
>
> > > > > > On Jan 8, 4:51 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > > > > > > Something like this?
>
> > > > > > > import csv
> > > > > > > reader = csv.reader(csvfile)
> > > > > > > header = None
> > > > > > > paints = {}
> > > > > > > colorants = {}
> > > > > > > for line in reader:
> > > > > > > if not header: header = line
> > > > > > > else:
> > > > > > > row = dict([header[i],item) for i,item in
> > > > > > > enumerate(line)])
> > > > > > > name = row['Color Name']
> > > > > > > colorant = row['Colorant']
> > > > > > > if not name in paints:
> > > > > > > paints[name]=db.paint.insert(name=name)
> > > > > > > if not colorant in colorants:
> > > > > > > colorants[colorant]=db.colorant.insert(name=colorant)
> > > > > > > db.paint_colorants.insert(paint_id=paints
> > > > > > > [name],colorant_id=colorants[colorant])
>
> > > > > > > On Jan 8, 4:10 am, Jason Brower <encomp...@gmail.com> wrote:
>
> > > > > > > > I have data like this in a csv file....
> > > > > > > > "Color Name", Base, Colorant, Amount, Colorant, Amount,
> > > > > > > > Colorant,
> > > > > > > > Amount, Colorant, Amount, Colorant, Amount
> > > > > > > > "10 PORDRR",G,fo1,76,da1,32,ro1,111,yi1,1,,
> > > > > > > > It is for a paint database.
> > > > > > > > Is it at all possible to import that into this model?
> > > > > > > > Normally I would just take a few minutes and do it by hand.
> > > > > > > > But I have
> > > > > > > > 14,000+ entries, so that's out of the question. Any ideas? In
> > > > > > > > particular, how do you handle the variance in how many kinds of
> > > > > > > > colorants are added. Notice in this example that is only 4
> > > > > > > > colorants,
> > > > > > > > and 5+ could be in the page. (Currently from this extraction,
> > > > > > > > only 5
> > > > > > > > colorants and their amounts.
>
> > > > > > > > db = SQLDB('sqlite://paint.sqlite')
>
> > > > > > > > db.define_table('paint',
> > > > > > > > Field('name', length=30, requires=IS_NOT_EMPTY(),
> > > > > > > > unique=True),
> > > > > > > > Field('base_coat', length=3, requires=IS_NOT_EMPTY()),
> > > > > > > > Field('hex_color', length=6))
>
> > > > > > > > db.define_table('colorant',
> > > > > > > > Field('name', length=3, requires=IS_NOT_EMPTY()))
>
> > > > > > > > db.define_table('paint_colorants',
> > > > > > > > Field('paint_id', db.paint),
> > > > > > > > Field('colorant_id', db.colorant),
> > > > > > > > Field('units', 'integer'))
>
> > > > > > > > db.paint_colorants.paint_id.requires=IS_IN_DB(db, 'paint.id',
> > > > > > > > '%(name)s')
> > > > > > > > db.paint_colorants.colorant_id.requires=IS_IN_DB(db, 'paint.id',
> > > > > > > > '%(name)s')
>
> > > > > > > > Best Regards,
> > > > > > > > Jason Brower
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
