I’ve yet to embrace Drupal 7 development, but I know they’ve revamped how data is stored so that you do not need to store everything as nodes. In Drupal 6, such things are not possible. But there are other options.
Here is my use case:
- On my site, represent two tables of data, stored in an external MS Access database
- Relate the data in those tables to content on the site
- Periodically update data, taking fresh copy of Access database
I know what you are thinking. Access? Access! Yes, Access. But wait, all is not lost. The following approach would not work for complex relational databases, lest you spend a year exporting and importing data.
My use case only uses a couple of tables of which only one is updated frequently (once a week). I want to be able to export my Access tables as CSV files, with headers, and then import them into my site.
I need to be able to refresh the data – so delete existing data records on the site and import a fresh set. I don’t want to use the Node API because this data is not that type of data. I don’t need URLs for each record, I don’t need the overhead of the Node API when searching/loading/saving/creating.
Enter Data module
The data module provides an API for dealing with arbitrary tables of data. You can create tables, delete them, adopt them from elsewhere. It’s great. But that is not all. It integrates with Feeds module and Views. Yes, you heard right. Feeds – you can import data reeeaally easily. Views – you can create lists for pages, blocks, views-attach, JSON feeds, whatever-hell-else-views-can-render using your data.
A key thing to remember is to use Feeds to create your tables – don’t use Data to create the table. When you define the mapping in your feed, it creates the table.
Create a new Feed importer. Change the basic settings to never refresh data, but check import on submission.. Change the fetcher to a file upload. Change the parser to CSV file.
Select ‘data processor’ in Feed.
Change the processor to ‘data processor’ and then update the mappings.
Map CSV columns to new table columns.
Add a new mapping for each of your CSV file heading – copy the column heading name from the CSV file exactly. Make sure you set your column types appropriately, otherwise you find it awkward to manipulate the data later (assuming you might need to).
You should now be able to head to http://yoursite/import and test it all out. Make sure your CSV file is appropriately encoded (utf8) and has appropriate line endings (unix or windows, not mac line breaks). You can edit these settings in TextWrangler on the Mac – and equivalent editors on Windows.
Creating data views
When you create your view, just select you data table as the basis for the view. Now do your usual views magic.
Create a data view.
Relating data to Drupal content
Navigate to site building > data tables. Now edit your table. You should be able to specify a join on any column in the table. Note that my example image highlight adding a join to a timestamp field, which would probably be pretty dumb.
If you had a field that matched a taxonomy term, you can add a join to it. Then…. then…. you can specify relationships in your views and link your data to taxonomy and on to nodes and… well, you get the idea.
Join data tables to Drupal tables.
Go forth. Do data things.
