Creating a Terraform variable file from an Excel

Introduction

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.

Terraform variable file

Prerequisites

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

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

image 25

Folder structure

  • env.xlsx: Excel file
image 26

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

image 27

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!

, , ,

About Dang Nhu Hieu

I'm Vietnamese. In the past, I'm a software developer, now working in Japan on an Infra team. Skill : AWS, VMware, HA architech,setting Database : Oracle DB, PostgresDB ,.. Programming language : Java, C#, Python, Bash linux, Batch windows, powershell ,... Hobbies: badminton, film photo, travel. https://www.linkedin.com/in/hieu-dang-15a0561a6/
View all posts by Dang Nhu Hieu →

2 thoughts on “Creating a Terraform variable file from an Excel

  1. Hi,

    What things we need to mention in header.py file?

    Could you please let me the code for this?

    1. thank you for reading my blog.
      header.py file content as 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.