Getting Your CSV data into Drupal & into a View using Feeds & Data (ready to migrate!)

NOTE: This approach has been superceded by our work developing the bridge module for using the Drupal Migrate module to migrate legacy data in to CiviCRM. Read about it here

 

Often data that you need to import into CiviCRM is presented to you in an Excel or CSV format. Of course there are various ways to do this but the Data / Feeds module combo provides a nice method if you are likely to be uploading a similar csv more than once, or if the person actually uploading doesn't have the access or knowledge to directly edit the database. This method is also really nice for nightly uploads from another system because the most technical thing you have to do is write a script to ftp your csv onto the server. Follow on blogs from this will describe how to migrate data from these imports into CiviCRM & UberCart

Feeds allows you to get data from a range of formats (csv, RSS etc) and put them into a range of formats (nodes, mySQL table) but here the focus is csv to MySQL table.

Prerequisites:

CSV of the data you want to upload

Schema module installed (drush dl schema / drush en schema)

Data module installed (drush dl data / drush en data, drush dl data_ui / drush en data_ui )

Feeds module installed (drush dl feeds / drush en feeds, drush dl feeds_ui / drush en feeds_ui)

Views module installed (drush dl views / drush en views, drush dl views_ui / drush en views_ui)

Set up the feed config

Start at this URL (under structure/ feeds importers / New Importer on the admin menu or site building/ feeds importers / New Importer)

/admin/build/feeds/create

Give your feed a sensible machine name (pref lower case with underscores but no spaces, not too long) and a descriptive name & click create.

Settings

Many of the settings are self-explanatory - I'm just going to mention those you should change / consider or those that took me a  while to make sense of

1) Select a Fetcher - click on 'Select' next to 'File Upload'

2) Optional - check 'Supply path to file directly' - this is good when you are ftping files up to the server (e.g by script) & you want them to be uploaded automatically. There isn't s downside to this setting

3) Parser - select csv Parser. You can then edit the default separator typeif you wish  it can also be changed at the point of import

4) Processor - select 'Data Processor'

5) Under data processor settings consider whether you want the records created to be removed from your table after a period. I recommend you do select 'Replace Existing Records' which will allow you to do updates as well as inserts

6) Mapping- For each field in your csv enter the field's name in the source and choose the field type under target. If you choose integer or VarChar you will be able to make the field unique. You should select unique for any fields that uniquely identify the record in your csv. You don't have to create mappings for fields you aren't interested in

Note: Leave 'Attach to content type' as standalone form - this setting is really for when you want to have a content type like 'Bob's blog feed and use it to create blog content items from an RSS feed.

That's it to set it up!

Importing your data

Go to /import and click on the feed you set up. From this URL you can select & upload your csv. Any columns in your csv that match will be imported. If you are regularly importing this is the ONLY step you need to keep doing. One URL, 3 clicks.

Note that you can look at the csv that was used if you want to edit it and re-upload. There is also a 'delete items' option

 

Viewing your data

A View will have been created for you - you can navigate to it by selecting structure / site configuration in the admin menu/ data tables/ overview. Click on the link to 'visit the Data table Content Page' (I always overlook this link and go round a loop getting there)

You will now be at /admin/content/data. Click on View and you will be in your table's view. This is a normal view which you can edit.

Using your data

I will look at importing the data into CiviCRM in a separate blog