Migrating Date Ranges from CSV into Date Range Module

Posted by Gerardo Hernández on January 30, 2017

As you might know, the Datetime Range module is currently an experimental module within Drupal 8 core. This module allows you to create specific time intervals, such as: the creation date and the expiration of a product, a schedule of a workshop or event which has different blocks of hours, or hotel room reservations, etc.

Using it is very simple, but how does a migration to a datetime range field work? In response to this question I have created an example consisting of several scenarios, each migration will be supported by a pending core patch that creates a properly formatted date string; The first scenario will be from subfields, and the second will be done using the iterator process plugin.

First though, we have to create content to migrate, so we will make use of two CSV files inside the sites/default/files/csv directory. The first row in each one contains the machine names headers of each column to speed up the configuration process.

#Drugs.csv
id,name,description,date_range,type
1,Acetaminophen,"this is a common medicine very effective",20/04/2015-20/04/2016,"Analgesics,Antipyretics"
2,Diclofenac,"This is topical application medicine",06/07/2014-06/07/2015,Anti-inflammatory
3,Dicloxacillin,"Synthetic penicillin",28/12/2016-28/12/2017,Antibiotics
4,Iodo,"Low cost antiseptic",11/06/2015-11/06/2016,Antiseptics
5,Prednisolone,"Anti-inflammatory activity",08/10/2014-08/10/2015,Anti-inflammatory
6,Peroxide,"Does not cause tissue irritation, stimulates blood clotting",24/04/2016-24/04/2017,Antiseptics
7,Bisolgrip,"Cough syrup effective",10/03/2015-10/03/2016,Analgesics
8,Amoxicillin,"Prevents bacterial infections",17/09/2014-17/09/2015,Antibiotics
9,Dipyrone,"Of oral use, with anti-inflammatory qualities",25/12/2016-25/12/2017,"Analgesics,Antipyretics,Anti-inflammatory"

This defines each row with an identifier, the name of a common drug in Central America, a range of fictitious dates, simulating its beginning and expiration in the market, and the medicin category or type.

#Training.csv
id,topic,description,teacher,days,hours
1,Node.js,"Learn about this excellent JavaScript runtime breaking paradigms.",John Miller,"21/11/2016,23/11/2016,25/11/2016,29/11/2016,01/12/2016","07:00:00-08:45:00,15:00:00-16:45:00"
2,Html5,"Integrating new tags for a more dynamic and easy interaction for the developer.",Cloe Nutter,"22/11/2016,24/11/2016,28/11/2016,30/11/2016,02/12/2016","11:00:00-12:45:00,09:00:00-10:45:00"
3,CSS3,"Creating new projection of websites.",Mary Ludwing,"21/11/2016,23/11/2016,25/11/2016,29/11/2016,01/12/2016","09:00:00-10:45:00,13:00:00-14:45:00"
4,Javascript,"Learns its advanced form of programming.",Gerald Schneider,"22/11/2016,24/11/2016,28/11/2016,30/11/2016,02/12/2016","13:00:00-14:45:00,11:00:00-12:45:00"
5,PHP 5 to 7,"Interacts with the backend and you have your websites service easily.",Kevin Robson,"21/11/2016,23/11/2016,25/11/2016,29/11/2016,01/12/2016","15:00:00-16:45:00,07:00:00-08:45:00"

Here we will show possible trainings or courses, where each one has an identifier, the name of the course, a brief description, who will give the training, the dates course, in the form of a matrix, and two possible blocks of hours on those dates where the source is taking place.

Download the patch above core patch and apply to the project. This can be with any tool you want but I recommend patchutils and use the command:

patch -p1 < patch_example.patch

Migration Configuration 1: Drug Creation and Expiration

Since the CSV file contains a field for the drug type, you'll need to create a vocabulary that will contain that data. After having defined the vocabulary, we will add a new Content type called Medicines with the following fields:

Field Name Field Type
Body Text(formatted, long with summary)
Expire Date Date Range
Type Entity reference (taxonomy term)

 

 

 

 

Export your configurations with drush, thus keeping track of site changes; Use the command:

drush cex -y

If you have done all of the above, we have reached the point of creating the YAML configuration file inside the /sync directory which we will call migrate_plus.migration.medicines.yml. If you wish, you can review Migrating CSV data to paragraphs where you will notice some settings are very similar.

id: medicines
migration_tags:
  - CSV
migration_group: null
label: 'Example of migration with Datetime Range'
source:
  plugin: csv
  path: 'public://csv/drugs.csv'
  header_row_count: 1
  keys:
    - id
process:
  type:
    plugin: default_value
    default_value: medicines
  title: name
  body: description
  field_expire_date/value:
    -
      plugin: explode
      source: date_range
      delimiter: '-'
    -
      plugin: extract
      index:
        - '0'
    -
      plugin: format_date
      from_format: d/m/Y
      to_format: Y-m-d
  field_expire_date/end_value:
    -
      plugin: explode
      source: date_range
      delimiter: '-'
    -
      plugin: extract
      index:
        - '1'
    -
      plugin: format_date
      from_format: d/m/Y
      to_format: Y-m-d
  field_type:
    -
      plugin: explode
      source: type
      delimiter: ','
    -
      plugin: entity_generate
destination:
  plugin: 'entity:node'
migration_dependencies:
  require: { }
  optional: { }
  • field_expire_date/value and field_expire_date/end_value: As you can see we define the subfields and the Explode plugin will return an array of values, from which we select the necessary one using the Extract plugin. The value's index is '0' and that of end_value is '1'. Lastly, we make use of the FormatDate plugin that allows any date format or time be converted into ISO 8601 as needed by the destination.
  • field_type: It is possible to have one or more categories, so we use Explode to cut up the data into separate terms, then Entity Generate to lookup and create entries for those terms; For a better perspective you can read Entity Lookup & Generate migrate process plugins.

Import the configuration so that it is recognized by drush with the command:

drush cim -y

Migration Configuration 2: Training Schedule.

Since it does not use any type of Entity Reference, we simply create a content type necessary to store the contents of the CSV.

Field Name Field Type
Body Text(formatted, long with summary)
Date Date range
Teacher Text(Plain)

Always have an export of configurations with drush, thus you avoid any conflict. Now create the following YAML file in the configuration directory, named migrate_plus.migration.training.yml.

id: training
migration_tags:
  - CSV
migration_group: null
label: 'Example of migration with Datetime Range'
source:
  plugin: csv
  path: 'public://csv/training.csv'
  header_row_count: 1
  keys:
    - id
process:
  title: topic
  body: description
  field_teacher: teacher
  day1:
    -
      plugin: explode
      delimiter: ','
      source: days
    -
      plugin: extract
      index:
        - '0'
  day2:
    -
      plugin: explode
      delimiter: ','
      source: days
    -
      plugin: extract
      index:
        - '1'
  day3:
    -
      plugin: explode
      delimiter: ','
      source: days
    -
      plugin: extract
      index:
        - '2'
  day4:
    -
      plugin: explode
      delimiter: ','
      source: days
    -
      plugin: extract
      index:
        - '3'
  day5:
    -
      plugin: explode
      delimiter: ','
      source: days
    -
      plugin: extract
      index:
        - '4'
  hours1:
    -
      plugin: explode
      delimiter: ','
      source: hours
    -
      plugin: extract
      index:
        - '0'
  hours2:
    -
      plugin: explode
      delimiter: ','
      source: hours
    -
      plugin: extract
      index:
        - '1'
  start_time1:
    -
      plugin: explode
      delimiter: '-'
      source: '@hours1'
    -
      plugin: extract
      index:
        - '0'
  end_time1:
    -
      plugin: explode
      delimiter: '-'
      source: '@hours1'
    -
      plugin: extract
      index:
        - '1'
  start_time2:
    -
      plugin: explode
      delimiter: '-'
      source: '@hours2'
    -
      plugin: extract
      index:
        - '0'
  end_time2:
    -
      plugin: explode
      delimiter: '-'
      source: '@hours2'
    -
      plugin: extract
      index:
        - '1'
  start_date1:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day1'
        - '@start_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  end_date1:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day1'
        - '@end_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  start_date2:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day2'
        - '@start_time2'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  end_date2:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day2'
        - '@end_time2'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  start_date3:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day3'
        - '@start_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  end_date3:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day3'
        - '@end_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  start_date4:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day4'
        - '@start_time2'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  end_date4:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day4'
        - '@end_time2'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  start_date5:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day5'
        - '@start_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  end_date5:
    -
      plugin: concat
      delimiter: ' '
      source:
        - '@day5'
        - '@end_time1'
    -
      plugin: format_date
      from_format: 'd/m/Y H:i:s'
      to_format: 'Y-m-d\TH:i:s'
  union1:
    plugin: get
    source:
      - '@start_date1'
      - '@end_date1'
  union2:
    plugin: get
    source:
      - '@start_date2'
      - '@end_date2'
  union3:
    plugin: get
    source:
      - '@start_date3'
      - '@end_date3'
  union4:
    plugin: get
    source:
      - '@start_date4'
      - '@end_date4'
  union5:
    plugin: get
    source:
      - '@start_date5'
      - '@end_date5'
  field_date:
    -
      plugin: get
      source:
        - '@union1'
        - '@union2'
        - '@union3'
        - '@union4'
        - '@union5'
    -
      plugin: iterator
      process:
        value: '0'
        end_value: '1'
destination:
  plugin: 'entity:node'
  default_bundle: training
migration_dependencies:
  require: {  }
  optional: {  }

In comparison to the previous migration configuration, this requires certain temporary variables, since some of the content will be taken from data sets from the days column and combined with hours column to project the ranges at different times for the days.

  • The variables day1, day2, day3, day4, day5 get a different date from the array, according to the index. The variables hours1, and hours2 will get two matrices, which will contain different time intervals. It is still necessary to divide them, so start_time1, end_time1, start_time2, and end_time2 are used. I then pass them to the source like '@hours1', each one will be left with a single data time, so you can make the necessary combinations with hours.
  • With start_date1, end_date1, start_date2, end_date2, start_date3, end_date3, start_date4, end_date4, start_date5, end_date5, we combine the Concat plugin to a date at two different hours, thus creating intervals of time. Lastly, the ranges have not yet been established, so we create union1, union2, union3, union4, union5 and here combine the time lapses thus creating the expected values to migrate.

That was a lot, but it goes to show how powerful the migrate API is. You can cut, slice and dice the values, assign them to temporary variables and recombine them again. Now, on to the final mapping into the destination fields.

  • The field field_date will make a call to the Get plugin to get the values that have each union variable and these are deposited consecutively in the Iterator plugin, which iterates over the array and retrieves and assigns the subfields.  From the union, index '0' is mapped to the value and end_value is retrieved from index '1'.

To finish you you should re-import config again, since there is a new configuration; then run the two migrations either at the same time or individually like this:

drush cim -y
drush mi --all
drush mi medicines
drush mi training

These are two examples of how to do this type of migration. I hope you find these helpful.

Are you looking for help with a Drupal migration or upgrade? Regardless of the site or data complexity, MTech can help you move from a proprietary CMS or upgrade to the latest version–Drupal 8.

Write us about your project, and we’ll get back to you within 48 hours.