Ansible: Remote execution with Excel parameter sheets

In IaC (Infrastructure as Code) such as Ansible, yml file is absolutely parameter sheet and to scrap traditional excel parameter-sheets is ideal.
But if clients fix the parameters, using excel parameter-sheets is realistic way.
This post shows how to convert excel parameter-sheets into variables that can be used in Ansible.

The following is summary.

1. Convert excel into csv file
2. Convert csv file into host variables of Ansible
3. Use host variables in Ansible

1. Convert excel into csv file

The format of the excel parameter-sheet is the following.

	A	B	C	D
1	ansible_ip	param1	param2	param3
2	192.168.1.1	x1	y1	z1
3	192.168.1.2	x2	y2	z2

1st line means the name of parameters.
ansible_ip parameter means ip addresses of target servers.
2nd and subsequent lines means parameters.

Parameters of target 192.168.1.1 are:

• ansible_ip = 192.168.1.1
• param1 = x1
• param2 = y1
• param3 = z1

Parameters of target 192.168.1.2 are:

• ansible_ip = 192.168.1.2
• param1 = x2
• param2 = y2
• param3 = z2

Save the foregoing as a csv file. Assume that saved file is parameter.csv

2. Convert csv file into host variables of Ansible

Host variables are defined in host_vars/<hostname or IP address> file and applicable on the target as the following post.
>> Recommended directory structure and using variables of Ansible

The following playbook creates files of host variables of 192.168.1.1 and 192.168.1.2 from parameters.csv.

---
- name: Create host_vars files (yml) from csv
  hosts: localhost

  tasks:
  - name: read csv file
    read_csv:
      path: parameter.csv
    register: parameters
    delegate_to: localhost

  - name: Delete existing host_vars files
    file:
      path: ~/ansible/project01/host_vars/{{ item.ansible_ip }}.yml
      state: absent
    loop:
      "{{ parameters.list }}"

  - name: Ensure file already exists at host_vars files to work around 'invalid selinux context' issue
    file:
      path: ~/ansible/project01/host_vars/{{ item.ansible_ip }}.yml
      state: touch
    loop:
      "{{ parameters.list }}"

  - name: Create host_vars files (yml)
    blockinfile:
      marker: "# {mark} Parameters from csv"
      block: "{{ root | to_nice_yaml }}"
      path: ~/ansible/project01/host_vars/{{ item.ansible_ip }}.yml
    vars:
      root:
        "{{ item }}"
    loop:
      "{{ parameters.list }}"

Save this playbook as csv2yml.yml in the same directory with parameter.csv.
Run csv2yml.yml to create host variables of project01 in host_vars directory.

ansible-playbook csv2yml.yml

The following is the created host_vars/192.168.1.1 file.

# BEGIN Parameters from csv
ansible_ip: 192.168.1.1
param1: x1
param2: y1
param3: z1
# END Parameters from csv

Form 19th to 24th lines are workaround of the error “invalid selinux context”

  - name: Ensure file already exists at host_vars files to work around 'invalid selinux context' issue
    file:
      path: ~/ansible/project01/host_vars/{{ item.ansible_ip }}.yml
      state: touch
    loop:
      "{{ parameters.list }}"

Use blockinfile module to output to yml file.

    blockinfile:
      marker: "# {mark} Parameters from csv"
      block: "{{ root | to_nice_yaml }}"
      path: ~/ansible/project01/host_vars/{{ item.ansible_ip }}.yml

If you manually modify 192.168.1.1 file, define parameters outside of between #BEGIN Parameters from csv and #END Parameters from csv so that it is independent if you run csv2yml.yml many times.

# BEGIN Parameters from csv
ansible_ip: 192.168.1.1
param1: x1
param2: y1
param3: z1
# END Parameters from csv

custom_param: xx1    <-- It is independent if you run scsv2yml.yml many times.

3. Use host variables in Ansible

You can use variables that is applicable on the targets of project01 as the following post.
>> Recommended directory structure and using variables of Ansible

That’s about it.