6. Network Inventory Automation: Excel to Network Automation JSON

🚀 Preparation Steps

1. Install Python and Required Libraries

# Recommended: Use a virtual environment
python3 -m venv network_inventory_env
source network_inventory_env/bin/activate  # On Mac/Linux
# OR
network_inventory_env\Scripts\activate  # On Windows

# Install required libraries
pip install pandas openpyxl

2. Prepare Your Excel File

Create an Excel file named network_inventory.xlsx with the following columns:

• HOSTNAME

• IP_ADDRESS

• DEVICE_TYPE

• USERNAME

• PASSWORD

• COMMANDS (Optional)

Sample Excel Content

🖥️ Running the Script

Method 1: Direct Execution

# Run the script directly
python excel_to_network_json.py

Method 2: Specify Custom Inputs

# Modify script parameters
excel_to_network_json(
    excel_file_path="your_custom_inventory.xlsx",
    sheet_name="Your_Sheet_Name",
    output_json_path="custom_network_devices.json"
)

🔍 Script Execution Options

Command-Line Customization

# Basic execution
python excel_to_network_json.py

# Specify custom Excel file
python excel_to_network_json.py --excel_file custom_inventory.xlsx

# Specify custom sheet name
python excel_to_network_json.py --sheet_name "Custom_Sheet"

# Specify custom output file
python excel_to_network_json.py --output network_devices_custom.json

💡 Troubleshooting Common Issues

1. Missing Libraries

# If you see an import error
pip install pandas openpyxl

2. Excel File Not Found

• Double-check file path

• Ensure file is in the same directory

• Use full path if necessary

3. Incorrect Sheet Name

• Verify exact sheet name in Excel

• Check for hidden spaces or special characters

🛡️ Full Script with Error Handling

import pandas as pd
import json
import argparse
import os

def excel_to_network_json(excel_file, sheet_name, output_json):
    try:
        # Validate file exists
        if not os.path.exists(excel_file):
            raise FileNotFoundError(f"Excel file not found: {excel_file}")

        # Read Excel file
        df = pd.read_excel(excel_file, sheet_name=sheet_name)

        # Required columns check
        required_columns = [
            'HOSTNAME', 
            'IP_ADDRESS', 
            'DEVICE_TYPE', 
            'USERNAME', 
            'PASSWORD'
        ]

        # Check for missing columns
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            raise ValueError(f"Missing columns: {missing_columns}")

        # Prepare records for JSON
        records = []
        for _, row in df.iterrows():
            # Default commands
            default_commands = [
                "show version", 
                "show running-config", 
                "show interfaces"
            ]

            # Handle commands
            commands = row.get('COMMANDS', default_commands)
            if isinstance(commands, str):
                commands = [cmd.strip() for cmd in commands.split(',')]

            # Create device record
            device_record = {
                "hostname": row['HOSTNAME'],
                "ip": row['IP_ADDRESS'],
                "device_type": row.get('DEVICE_TYPE', 'cisco_ios'),
                "username": row['USERNAME'],
                "password": row['PASSWORD'],
                "commands": commands
            }

            records.append(device_record)

        # Write to JSON file
        with open(output_json, 'w') as json_file:
            json.dump(records, json_file, indent=4)

        print(f"Successfully converted {excel_file} to {output_json}")
        return True

    except Exception as e:
        print(f"Error converting Excel to Network JSON: {str(e)}")
        return False

def main():
    # Set up argument parser
    parser = argparse.ArgumentParser(description='Convert Excel to Network Device JSON')
    parser.add_argument('--excel_file', 
                        default='network_inventory.xlsx', 
                        help='Path to Excel inventory file')
    parser.add_argument('--sheet_name', 
                        default='Network_Devices', 
                        help='Excel sheet name')
    parser.add_argument('--output', 
                        default='network_devices.json', 
                        help='Output JSON file path')

    # Parse arguments
    args = parser.parse_args()

    # Convert Excel to JSON
    excel_to_network_json(
        excel_file_path=args.excel_file,
        sheet_name=args.sheet_name,
        output_json_path=args.output
    )

if __name__ == "__main__":
    main()

🔒 Security Best Practices

1. Never Commit Credentials

   • Use .gitignore to exclude Excel and JSON files

   • Consider using environment variables

   • Implement secure credential management

2. Limit File Access

   • Set strict file permissions

   • Store inventory files securely

🚀 Integration with Network Automation

# In your network automation script
from excel_to_network_json import excel_to_network_json
from network_device_script import process_devices

# Convert Excel to JSON
excel_to_network_json(
    excel_file_path='network_inventory.xlsx',
    sheet_name='Network_Devices',
    output_json_path='network_devices.json'
)

# Process devices using previous automation script
process_devices('network_devices.json')

Call to Action

👉 Ready to transform your network inventory?

• Download the script

• Prepare your Excel file

• Start automating today!

What's your biggest challenge in managing network device information? Share below!

#NetworkAutomation #InventoryManagement #NetworkEngineering