Changes between Version 7 and Version 8 of SpreadsheetImporter


Ignore:
Timestamp:
01/23/10 13:54:11 (12 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • SpreadsheetImporter

    v7 v8  
    1111Options:
    1212=== CSV Import ===
     13Here is a process that works, although is a bit cumbersome, so we should look to automate as much as possible & improve:
     14 1. Get a spreadsheet of good Data together (e.g. using Google Docs)
     15 2. Save as CSV
     16 3. Open in Open Office (MS Excel destroys data - do NOT use!)
     17 4. Export copies of the tables you wish to import to in CSV format, e.g.:
     18  * /gis/location.csv
     19  * /hms/hospital.csv
     20 5. Plan how you are going to deal with duplicates between these datasets.
     21  * e.g. copy exported data into Spreadsheet & then clear table: {{{db.module_resource.truncate()}}}
     22 6. Plan the order of imports:
     23  * Need to import referenced tables before those which refer to them, e.g.:
     24   * pr_person, gis_location, or_organisation, or_office, or_contact
     25 7. Copy the data from the datasheet into the one with the column headers
     26  * NB All column headers need to be present, named correctly, in the correct order
     27  * No blank rows in the sheet
     28 8. Upload the file to the server: /home/haiti/web2py
     29 9. Do a Test Import on Dev
     30  * Check that data looks good
     31 10. Repeat on Prod:
    1332{{{
    14 cd /path/to/web2py
    15 python web2py.py -S sahana -M -N
    16 db.import_from_csv_file(open('db.csv','rb'))
    17 db.or_organisation.import_from_csv_file(open('orgs.csv','rb'))
     33vim /etc/crontab
     34#0-59/1 * * * * www-data cd /home/haiti/web2py/ && python web2py.py -C -D 1 >> /tmp/cron.output 2>&1
     35vim /home/haiti/prod/models/00_db.py
     36 migrate=True
     37rm /etc/apache2/sites-enabled/prod-ssl
     38/etc/init.d/apache2 force-reload
     39/home/haiti/update-prod
     40/home/haiti/shell-prod
     41db(db.gis_location.id>0).select().last().id
     42db.gis_location.import_from_csv_file(open('hospital_locations.csv','rb'))
     43rows = db(db.gis_location.id>0).select()
     44for row in rows:
     45    if row.lon and row.lat and not row.wkt:
     46        db.gis_location[row.id] = dict(wkt = 'POINT(%f %f)' % (row.lon, row.lat))
     47    if not row.deleted:
     48        db.gis_location[row.id] = dict(deleted = False)
     49    if not row.created_on:
     50        db.gis_location[row.id] = dict(created_on = request.utcnow)
     51    if not row.modified_on:
     52        db.gis_location[row.id] = dict(modified_on = request.utcnow)
     53
    1854db.commit()
    1955Ctrl+D
     56Set the initial ID in import sheet to be the last+1 & then continue on
     57/home/haiti/shell-prod
     58db.hms_hospital.import_from_csv_file(open('hospitals_import.csv','rb'))
     59rows = db(db.hms_hospital.id>0).select()
     60for row in rows:
     61    if not row.deleted:
     62        db.hms_hospital[row.id] = dict(deleted = False)
     63    if not row.created_on:
     64        db.hms_hospital[row.id] = dict(created_on = request.utcnow)
     65    if not row.modified_on:
     66        db.hms_hospital[row.id] = dict(modified_on = request.utcnow)
     67
     68db.commit()
     69Ctrl+D
     70ln -s /etc/apache2/sites-available/prod-ssl /etc/apache2/sites-enabled
     71/etc/init.d/apache2 force-reload
     72vim /home/haiti/prod/models/00_db.py
     73 migrate=False
     74vim /etc/crontab
     750-59/1 * * * * www-data cd /home/haiti/web2py/ && python web2py.py -C -D 1 >> /tmp/cron.output 2>&1
    2076}}}
    21   * Limited access through Web at: /admin/import_data (& doesn't seem to work at all?)
    22   * Problems with the file format (e.g. MS Excel chews, OpenOffice 3.1 ok)
    23   * Hard work to get reference fields lined up
    24    * Need to export a dataset first
     77
     78ToDo:
     79 * Provide a Controller to allow web-based access at: /admin/import_data (The current implementation broke at some point)
     80 * Use functions imported from a new {{{modules/s3gis.py}}} for the WKT-creation
    2581=== Google Spreadsheets API ===
    2682 * http://code.google.com/apis/spreadsheets/