CSV Data Import from the Web2py CLI

Importing Data

It is possible to manually import CSV files from the web2py shell (CLI = command line interpreter, a Python shell with the web2py environment).

Start the web2py CLI with the Eden data model environment:

python web2py.py -S eden -M

Override authorization:

auth.override = True

Define the target resource:

resource = s3mgr.define_resource("project", "activity")

Import data:

success = resource.import_xml(open("filename.csv", "rb"),                   # CSV format requires file handle, XML accepts filename
                              format="csv",                                 # "xml", "json" or "csv"
                              stylesheet="activity.xsl",                    # filename (with path) is sufficient
                              extra_data=dict(Organisation="Example.org"),  # extra columns to be added to each row
                              files={"image.png":open("image.png", "rb")},  # file attachments, filenames are the keys of the dict
                              ignore_errors=True)                           # skip invalid records (False rolls back the import on error)

See errors (error message and error tree are returned in any case, even with ignore_errors=True):

print success                 # prints a JSON message with the error message and the JSONified error tree
print resource.error          # prints the error message

print s3mgr.xml.tostring(resource.error_tree, pretty_print=True) # prints the error tree as XML

Do not forget to commit the import (if you want to keep it):

db.commit()

If you were only testing (e.g. to see validation errors), and now want to discard the import, you can either leave the shell without calling db.commit() - or you do an explicit:

db.rollback()

Tools to develop and debug XSLT stylesheets

If you want to test your XSLT stylesheet, you can also use:

python static/scripts/tools/csv2xml.py mycsvfile.csv mystylesheet.xsl

This converts the CSV file into XML, transforms it with the specified stylesheet and prints the result to the standard output.

If you redirect the output into a file, you can also use this script to produce S3XML sources from CSV files (e.g. in order to test web services):

python static/scripts/tools/csv2xml.py mycsvfile.csv mystylesheet.xsl > myxmlfile.xml

If you want to see the XML before transformation, just omit the stylesheet parameter:

python static/scripts/tools/csv2xml.py mycsvfile.csv