Creating a Terraform variable file from an Excel

Introduction

How to Create a Terraform variable file from an Excel. In the world of infrastructure as code (IaC), Terraform stands out as a powerful tool for provisioning and managing infrastructure resources. Often, managing variables for your Terraform scripts can become challenging, especially when dealing with a large number of variables or when collaborating with others.

This blog post will guide you through the process of creating a Terraform variable file from an Excel spreadsheet using Python. By automating this process, you can streamline your infrastructure management workflow and improve collaboration.

Prerequisites

Before we begin, make sure you have the following installed:

• Python: Download and install Python.
• Pandas and openpyxl library: Install it using pip3 install packename.

Steps to Create a Terraform Variable File from Excel

• Step 1: Excel Setup
• Step 2: Python Script to create Terraform variable file from an Excel
• Step 3: Execute the Script

Step 1: Excel Setup

Start by organizing your variables in an Excel spreadsheet. Create columns for variable names, descriptions, default values, setting value, and any other relevant information.

Setting_value and Variable_name columns will be written to the output file.

In the lab, I only created a sample Excel file for the Terraform VPC variable

Folder structure

• env.xlsx: Excel file

Step 2: Python Script to create Terraform variable file from an Excel

Write a Python script to read the Excel spreadsheet and generate a Terraform variable file (e.g., terraform2.tfvars).

import pandas as pd
from pathlib import Path
import traceback
from lib.header import get_header

parent = Path(__file__).resolve().parent

# Specify the path to your Excel file
excel_file_path = 'env.xlsx'
var_file_name = 'terraform2.tfvars'

def main():
    try:
        env = get_header()
        sheet_name = env["SHEET_NAME"]

        # Read all sheets into a dictionary of DataFrames
        excel_data = pd.read_excel(parent.joinpath(excel_file_path),sheet_name=None, header=6, dtype=str)
        
        # Access data from a specific sheet
        extracted_data = excel_data[sheet_name]
        col_map = {
            "setting_value": env["SETTING_VALUE"],
            "variable_name": env["VARIABLE_NAME"],
            "auto_gen": env["AUTO_GEN"]
        }
        sheet_data = extracted_data[[col_map[key] for key in col_map if key in col_map]]
        sheet_name_ft = sheet_data.query('Auto_gen == "○"')

        # Display the data from the selected sheet
        print(f"\nData from [{sheet_name}] sheet:\n{sheet_name_ft}")

        # Open and clear content of file
        with open(f"{var_file_name}", "w", encoding="utf-8") as file:
            print(f"{var_file_name} create finish")

        # Write content of excel file to file
        for index, row in sheet_name_ft.iterrows():
            with open(f"{var_file_name}", "a", encoding="utf-8") as file:
                file.write(row['Variable_name'] + ' = ' + '"' + row['Setting_value'] + '"' + '\n')
        print(f"{var_file_name} write finish")
        
    except Exception:
        print(f"Error:")
        traceback.print_exc()

if __name__ == "__main__":
    main()
 

You can change the input Excel file name and output file name at these variables

excel_file_path = 'env.xlsx' 
var_file_name = 'terraform2.tfvars'

Depending on the contents of your Excel file, you can change the variables in the header.py file below

import os

def get_header():
    # Description
    os.environ["DESCRIPTION"] = os.environ.get("DESCRIPTION", "Description")
    # Description
    os.environ["DATA_TYPE"] = os.environ.get("DATA_TYPE", "Data_type")
    # setting value
    os.environ["SETTING_VALUE"] = os.environ.get("SETTING_VALUE", "Setting_value")
    # variablename
    os.environ["VARIABLE_NAME"] = os.environ.get("VARIABLE_NAME", "Variable_name")
    # genaration
    os.environ["AUTO_GEN"] = os.environ.get("AUTO_GEN", "Auto_gen")
    # variable file name location
    os.environ["FILE_NAME_LOCATION"] = os.environ.get("FILE_NAME_LOCATION", "4")

    return os.environ

Step 3: Execute the Script

python3 excel/main.py 

Output

Conclusion

By following these steps, you’ve automated the process of creating a Terraform variable file from an Excel spreadsheet. This not only saves time but also enhances collaboration by providing a standardized way to manage and document your Terraform variables.

Feel free to customize the script based on your specific needs and scale it for more complex variable structures. Thank you for reading the DevopsRoles page!