Data importing

As Drupal moves more from a website tool to an application framework, its generic data interfaces and handling tools become more important.

Backup and Migrate: is one of the first tools to look at, allowing manual and automated backup/ restore. A good way to transfer a site from one provider.

As regards data importing, the basic steps are:

Get your data into a MySQL table (Table Wizward can import CSV)
Create tables keys, change column types if needed (via mthe ymsql command line, or the Data module).
Use Table Wizard (tw) module to expose the Table as a View.
Use Migrate to map the a View of external data to native Drupal data, then run the import.

Lets look at the modules found in this domain so far…

The Table Wizard can views-enable any table, even those not in the drupal db and not known to drupal. It performs analysis of the tables it manages, reporting on empty fields, data ranges, ranges of string lengths, etc.. It provides an API for other modules to views-enable their tables. Can work with the Migrate module.

I used it to:
- link in a external table and create a view.
- Import a CSV file to a new table (it creates the new table, but with all field types as text) and create a view.
Issues: The CSV import or external table must have a single (numeric) primary key field though (which can be an issue: Excel can help there). The separator must be a comma (it cannot be ";" for example). Troubleshooting tw's refusal to create some views is not so easy.

The Schema API allows modules to declare their database tables and provides functions for creating, dropping, and changing tables, columns, keys, and indexes. It examines (and can compare) the live database and creates Schema API data structures for all tables that match the live database. Used by tw.

Its useful to analyse the existing dataset.
Limitations: it does not have a way to remove old/unused tables that are identified.

The Data module helps model, manage tables and an API for accessing their contents.

Once a CSV file had been imported with tw above, the table could be 'adopted' then: the type of the key changed to index and marked as primary key. The old field types can be tuned too.
Field type could also be mapped to views handler type.
Once a primary key has been defined, go back to tw.
Issues: The date field type was not available though and an email field could not be defined as a primary key. It can relate data to node types, but not users/profiles. Its views can conflict wuith tw, disable it if in doubt.

The Migrate and Migrate_extras modules (which depend on tw and schema) provide a flexible framework for migrating content into Drupal from other sources.

Once a view has been made with tw, Migrate sees it and can be used to map the source and destination fields. Default values can also be set.
Issues: test on a sample data set, not your live system… Plan lots of time to get this working 100% :-). Multiple source fields cannot be mapped to one destination, key mapping is quite primitive.

The print module allows "printer friendly" pages of nodes or views to be shown.

The views_export_xls module allows a view to be exported in an XML format that Microsoft Excel understands.

Uses cases not yet examined: data export to csv (views_bonus module?). Import/export from/to postgres

& xmlrpc (e.g. openerp). Creating a UI for editing data in non node tables.