File generation using web2py

[rahulserver]

I wish to generate a few reports in csv or txt or other file formats based on some database data maintained through a crud application. Previously, it was done through an asp.net application with vb as scripting language.But as I explored the wonderful capabilities of web2py, I have become a fan of this terrific framework.
Is it possible to do it in web2py. And if it is, then how and where should the code be placed.In the view or model or controller?

[Alan Etkin]

Hi

The book example:
http://web2py.com/books/default/chapter/29/10#CSV

You might want to check the recipes site for other ideas.
http://www.web2pyslices.com/home

Making a "csv output" search at this group's search feature could be helpful too.

[Brian M]

Yep, I've got a processing app that spits out all sorts of csv files based on data gathered from multiple sources.

Here's a little helper function I use

def csv_export(records, column_names, fields, mode = 'dal'):
    """Export DAL result set, list of dicts or list of lists to CSV stream for returning to user
    Arguments:
    records = the data to be returned
    column_names (list)= the column names/headings for the first row in the CSV file
                    Example ['First Name', 'Last Name', 'Email']
    fields (list) = the names of the fields (as they appear in records) in the order they
                    should be in the CSV. Example ['f_name', 'l_name', 'email']
                    or ['table_a.f_name', 'table_a.l_name', 'table_b.email']
                    If mode = 'list' and your records are in the correct order then fields may be None
                    otherwise use [1,3,0] if you list is in a different order
    mode (string) = what type of data is in records? 'dal' (Default), 'dict' or 'list'
                    'dal' if records came from a regular dal query (Default)
                    'dict' if records are a list of dicts (for example using db.executesql() with as_dict = True)
                    'list' if records are a list of lists/tuples (for example using db.executesql() with as_dict = False)

    """

    #create fake file object
    import cStringIO
    file = cStringIO.StringIO()
    #setup csv writer
    import csv
    csv_file = csv.writer(file)
    #write first row withspecified column headings/names
    csv_file.writerow(column_names)
    #which mode - dal or dict?
    if mode.lower() == 'dal' or mode.lower() == 'dict':
        for record in records:
            csv_file.writerow([record[field] for field in fields])
    elif mode.lower() == 'list':
        if fields == None:
            csv_file.writerows(records)
        else:
            for record in records:
                csv_file.writerow([record[field] for field in fields])
    return file



Then in a controller you can have something like

    csv_stream = csv_export(processed_dataset, column_names, fields, mode = 'dict')
    response.headers['Content-Type']='application/vnd.ms-excel'
    response.headers['Content-Disposition']='attachment; filename=data_for_%s.csv' % date.today()
    return csv_stream.getvalue() 

which will cause browser to download the csv file with your chosen filename

you could also turn around and save the datafile to the filesystem if you wanted.

Hope this helps!
Brian

[RAHUL PRIYADARSI]

Dear All,
Thanks for your answers.But what I was looking for was,putting into simple words,implementing a use case similar to the following "The system maintains the data related to each transaction.Each transaction includes the list of items demanded per transaction with each item belonging to a category.The system also generates an aggregated report per transaction as well as an overall report of the total items belonging to each category demanded.".Now "the report" here need not be in csv only.It may be txt file.Since I am not sure as to whether is it possible to print formatted strings(e.g. the string "report" printed right aligned in a total field of 15 characters,something likd printf(%15s)) in web2py,my question is that how we do file IO in web2py. Can we generate a txt file in web2py. And can we print formatted strings to it?
Since the controller runs on server and my application would be deployed in an intranet lan and it needs to generate files to be used in local file system of the system on which the user will access the app , where must this file IO code be written, in controller or view.

[Khalil KHAMLICHI]

Raul, when working with the web you always (most of the times) create the files server side and invite user to download it and save it to his file system, this sais you don't need to access the file system yourself, just invite the user to download your file and you are done.

for csv versus txt files, they are both the same thing IF the user is not going to import them into some other software that needs only csv content,

python has a module called StringIO, you can use it as a file and write to it mixed content : simple text and csv data.

[RAHUL PRIYADARSI]

Thanks Mr.Khalil!
I will surely give it a try!

[Brian M]

Yep, it is the web, you can't automatically write a file directly to the user's computer (Holy security issues Batman!) but you can provide them the prepared file as a download that they can then save to wherever they want. Doesn't matter if it is CSV or TXT or HTML or PDF or RTF whatever. Definitely note that reponse.header content-type and content-disposition portion of my sample code above, that is what will help you cause the user's browser to download the file with the correct name.  There is some StringIO stuff in there too which Khalil mentioned and you'd definitely want for generating the files.

As far as the formatting of strings you bet python & web2py can do it. If you're worried about text alignment and other layout stuff, why not just use HTML templates, that's what web2py is for. If you look around on this list there is also some stuff on generating PDF reports - I think there is a markmin to PDF converter available too.   I believe that pyrtf is also in web2py's contrib so you could use that to do Rich Text Format files.  Plenty of options out there for you.

On Monday, April 30, 2012 2:54:28 AM UTC-5, Khalil KHAMLICHI wrote:

Raul, when working with the web you always (most of the times) create the files server side and invite user to download it and save it to his file system, this sais you don't need to access the file system yourself, just invite the user to download your file and you are done.

for csv versus txt files, they are both the same thing IF the user is not going to import them into some other software that needs only csv content,

python has a module called StringIO, you can use it as a file and write to it mixed content : simple text and csv data.

[RAHUL PRIYADARSI]

Dear Mr.Brian,
Thank you very much for you reply.

" If you're worried about text alignment and other layout stuff, why not just use HTML templates"

Well because I wish to get prints from them using dot matrix printer, which prints only txt files!
But any way your response did help me very much.

With Regards,
rahulserver.

[Andrew W]

Hello Brian,

Thanks very much for sharing your script.  It works a treat and saved me a lot of time and confusion.

Andrew

[Brian M]

Happy to help!