test_xls.yml

#!/usr/bin/ansible-playbook
---
#
#      Copyright (c) 2019 World Wide Technology, Inc.
#      All rights reserved.
#
#      author: Joel W. King, World Wide Technology
#
#      usage: ./test_xls.yml -e 'dest=/tmp/' --tags play2
#
#  ------
#  Play 1
#  ------
- hosts: python
  gather_facts: no
  connection: local
  tags: [play1, xls_to_csv]

  vars:
    spreadsheet: '{{ playbook_dir }}/files/aci/ACI_DHCP_configuration.xlsx'
    dest: '{{ playbook_dir }}/files/aci/'

  tasks:
    - name: Extract the sheets from the Excel file, creating CSV files
      xls_to_csv:
        src: '{{ spreadsheet }}'
        dest: '{{ dest }}'
        sheets:
          - 'DHCP Relay'
          - 'data_centers'
        warn: True

    - debug:
        msg: 'File {{ dest }}{{ item }}.csv has been created'
      loop: '{{ sheet_filenames }}'
      tags: [debug]

    #
    #  variable 'sheet_filenames' is referenced in subsequent play(s)
    #

# ------
# Play 2
# ------
- hosts: python
  gather_facts: no
  tags: [play2, csv_to_facts]

  tasks:
    - name: Default behavior of csv_to_facts
      csv_to_facts:
        src: '{{ playbook_dir }}/files/aci/DHCPRelay.csv'
    - debug:
        msg: '{{ item.DC }} {{ item.Tenant }}'
      loop: '{{ spreadsheet }}'

# ------
# Play 3
# ------
- hosts: python
  gather_facts: no
  tags: [play3, csv_to_facts]

  tasks:
    - name: Create virtual spreadsheet of data centers (DC) and tenants
      csv_to_facts:
        src: '{{ playbook_dir }}/files/aci/DHCPRelay.csv'
        vsheets:
          - TENANTs:
             - DC
             - Tenant

# ------
# Play 4
# ------
- hosts: python
  gather_facts: no
  tags: [play4, csv_to_facts]

  vars:
    dest: '{{ playbook_dir }}/files/aci/'
    datacenter: DC1
    vsheets:
      data_centers:
          - TENANTs:
             - DC
             - Tenant
             - address1
      DHCPRelay:
          - RELAYs:
             - DC
             - Tenant
             - BD
             - AppProfile
             - EPG
             - DHCP

  tasks:
    - name: Create summarized virtual sheets, loading the variables in a namespace using the filename
      csv_to_facts:
        src: '{{ dest }}{{ item }}.csv'
        table: '{{ item }}'
        vsheets:
          '{{ vsheets[item] }}'
      loop: '{{ sheet_filenames }}'

    - debug:
        msg: '{{ item }}'
      loop: '{{ TENANTs }}'
      tags: [debug]

    - debug:
        msg: '{{ item }}'
      loop: '{{ RELAYs }}'
      when:
        - item.DHCP|bool
        - item.DC == datacenter
      tags: [debug]