upload an excel sheet to be added to the system

[Neveen Adel]

Hello,

Is there a library that can be used to import an excel file that
insert records in a specific table ?

If there is no a library how can i do that ?

Thanks in Advance

[Kenneth Lundström]

I know you can import/export CSV-files and export XLS files. But I don�t
know if there is a way to import XLS files directly.

Kenneth

[Neveen Adel]

Hello Kenneth,

Thanks for your reply:)

It is ok about import/export CSV files, How can i do this into my
application ?

On Feb 2, 3:39 pm, Kenneth Lundström <kenneth.t.lundst...@gmail.com>
wrote:

[Bruno Rocha]

Take a look at this pack:http://www.python-excel.org/

Bruno Rocha

http://about.me/rochacbruno/bio

[mart]

sticking purely to text, is there a difference between an excel file
and csv? if formatted properly, it should be the same thing. Extension
names are only incidental, if web2py has the extension hard coded in
its look up, then you should be able to simply rename the file...

[Neveen Adel]

Thanks a lot Bruno Rocha for ur reply :)

But these packages are used after uploading the file.

I want to know the step before that, what i want to do simply is to
have a browse button to choose CSV file and then press upload then i
can by using one of the packages u suggested to read the CSV file.

Do you have any idea how can i do that?

Thanks in Advance

[Brian M]

Neveen,

I've got a mini app that I upload excel files to for later parsing, here's now I do it. Getting the file would probably be even easier if you used an upload field in the DB & CRUD forms.

def excel_uploader():

    response.subtitle = "Upload excel file "

    from gluon.sqlhtml import form_factory

    form=form_factory(SQLField('import_xls','upload'))

    if form.accepts(request.vars,session):

        request.flash='Received: %s'%request.vars.import_xls

        path = os.path.join(request.folder,'private','spreadsheet_to_process.xls')

        #I happen to be copying the uploaded file to a known location & filename, but you wouldn't have to

        #the uploaded file gets saved to the /uploads folder with a auto-generated name too

        import shutil

        shutil.copyfileobj(request.vars.import_xls.file,open(path, 'wb'))

        #Then redirect to the next screen (or do the processing now)

        redirect(URL(r=request, f='process_excel'))

    return dict(form=form)

def process_excel():

    import xlrd

    from applications.app_name.modules.readexcel import readexcel as readexcel

    #see http://code.activestate.com/recipes/483742-easy-cross-platform-excel-parsing-with-xlrd/

    excel_filepath = os.path.join(request.folder,'private','spreadsheet_to_process.xls')

    xl = readexcel(excel_filepath)

    sheetnames = xl.worksheets()

    #for sheet in sheetnames:

    #    print sheet

    #for row in xl.getiter('Sheet  name'):

    #            print row

    return dict(rows = xl.getiter('Sheet Contents'))

[Neveen Adel]

Dear Brian,

Thanks a lot for your help.

>     #seehttp://code.activestate.com/recipes/483742-easy-cross-platform-excel-...

[AbortedF]

Microsoft Excel .xls format (as well as the others) saves its files
out in custom binary formats. It is very difficult to import them
unless you have a way of decoding the binary format.
Using csv, you can easily set up auto-importers (as long as the csv
data-structure is fairly static).

This is a bit hacky, but you should be able to get the idea...
########### CSV Example
def importer():
my_inserts=[]
form=SQLFORM.factory(
Field('csv_file', 'upload' ,
uploadfolder=os.path.join(request.folder,'static/temp'))
)
if form.accepts(request.vars, session):
my_file=open(os.path.join(request.folder, 'static/temp',
'form.vars.csv_file), 'r')
line=my_file.readline()
while line !="":
# Be careful how you delimit / split your vars!
my_vars=line.split(",") # or whatever
try:
my_inserts.append(
db.my_table.insert(
field_one=my_vars[0]
)
)
except:
# Error handling...
pass
my_file.close()
## Delete the file...
os.unlink(os.path.join(request.folder, 'static/temp',
form.vars.csv_file))
return dict(form=form, inserts=my_inserts)
###############################################################

Obviously it would be beneficial to do some validation etc. before
hand. There's almost definitely some decent python-based csv-parsing
module out there (and a better way to accomplish this task), but with
the way that the csv files (which I'm importing) are generated, I have
to manually parse them due to poor csv-exporting facilities in the
responsible apps.

Martin

[kesh]

how can i import excel row into a web2py sqlite table

[Dave S]

On Wednesday, July 26, 2017 at 2:30:11 AM UTC-7, kesh wrote:

how can i import excel row into a web2py sqlite table

If the excel data is in CSV format, you can import that directly.

<URL:http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Exporting-and-importing-data>

/dps

[Anthony]

Note, if you have a new question, it is better to simply create a new topic rather than adding to a thread that is several years old.