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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
