Here a primer to xlrd : http://scienceoss.com/read-excel-files-from-python/
And a documentation ressource : http://www.simplistix.co.uk/presentations/python-excel.pdf I think that you will need to upload the excel file repeatedly (until it pass validate_and_insert) except if you build your own validators and parse the file on the client side with javascript and trigger a error. I would do instead of this pathway (to not have to create custom validator in js) go for this : Create a function that parse your excel file and return a dict then create an other function to be use with form accept onvalidation that will call the parser_generating_dict function and try to validate_and_insert and if one row not validating trigger a error to the form upload field. It maybe possible to imagine way to create a custom validator that parse a excel file enter in a upload field... That would be even better, but I think my first suggestion is simpler for a draft. Pros use web2py validators simple workflow Cons You maybe can't tell your user what are the error in the file (not sure about that maybe you get output from validate_and_insert and know what was the error and maybe return it to the user). Richard On Fri, May 4, 2012 at 12:37 PM, Richard Vézina <[email protected] > wrote: > I need to do something very similar, but didn't have time to work on it... > But my search on the question bring me to discover : > > validate_and_insert, validate_and_update > > > http://web2py.com/books/default/chapter/29/6?#validate_and_insert,-validate_and_update > > So you process the rows contained in your excel file and if there is one > row rejected, you db.rollback() insetead of db.commit(). > > You can use xlrd python module to parse your excel file and translate it > into a dict that you pass to db.table.validate_and_insert(**dict). > > Hope it help. > > Richard > > On Fri, May 4, 2012 at 11:58 AM, rahulserver <[email protected]>wrote: > >> I want to import data from excel sheets to the database in web2py. I wish >> to validate the excel sheet before importing data from it.My table >> structure is similar to >> >> db.define_table('xlsheets',Field('name'),Field('xl','upload'),Field('filename')) >> From this table, i will obtain the filename field using >> form.vars.fieldname after sqlform has uploaded the file. From this file, i >> wish to read the data and store to another table. >> Suppose the user has entered some wrong values or inappropriate data in >> the file, and we detect it after uploading it, I can not think of a way to >> undo the upload other than deleting the tuple and upload again. >> Hence to avoid this situation, I wish to validate the file by reading >> its values before uploading it. >> >> Another question, I wish the transaction to be 'all or nothing' .i.e. >> either all rows of excel sheet get imported, or none must be. Can anyone >> suggest me how to do the rollback once an invalid tuple in excel sheet is >> obtained? >> > >
