Mike Dolt Enterprises Logo Mike Dolt Enterprises

Topic: How to Automate Configuration Updates in ETAP via Excel Data Import

User: AzzELL, Location: France

Asked on: Thursday, November 7, 2024 at 11:38 am EST
Answered on: Thursday, March 6, 2025 at 4:56 am EST

Question: Hello everyone,I’m working with ETAP (version 21.0.1C) to manage and tweak configurations for interconnected elements. I can export the data (IDs and configurations) to an Excel file, make edits there, and now I’m looking for a streamlined way to import those changes back into ETAP to update each element automatically. Has anyone set up a process for re-importing modified Excel data to update configurations? Any tips or best practices would be super helpful!Thank you

You're on the right track! Directly importing data from Excel to update ETAP models isn't a standard feature, but it's definitely achievable through a few different approaches. Here's a breakdown of common methods, along with their pros, cons, and best practices

1. ETAP Automation API (Python or .NET)
How it Works

This is the most powerful and flexible method. You use ETAP's Automation API (accessible via Python or .NET languages like C#) to write a script that reads your Excel data and programmatically updates ETAP elements.


Read Excel Data

Use a Python library like `openpyxl` or `pandas` to read your Excel file into a data structure. With .NET you can use libraries like `EPPlus`

Connect to ETAP

Establish a connection to your ETAP project through the Automation API.


Identify Elements

Use the element IDs from your Excel data to locate the corresponding elements in the ETAP model using API methods (e.g., `GetElementByID`).

Update Properties

Use the API to modify the desired properties of each element with the values from your Excel file.


Save Changes

Save the changes to your ETAP project.

Pros



Highly Customizable

Complete control over what gets updated and how.

Robust Error Handling

You can implement error checking and logging to ensure data integrity.


Batch Processing

Efficiently update large datasets.

Automated

Can be fully automated to run regularly.


Version Control Friendly

Scripts are text files that can be version controlled.

Cons



Requires Programming Knowledge

You need to be comfortable with Python or .NET programming.

Learning Curve

Learning the ETAP Automation API takes time.


More Setup

More initial development effort.

Best Practices



Backup Your ETAP Project

Always create a backup of your ETAP project before running any automation scripts.

Thorough Testing

Test your script on a development or test version of your model first.


Error Handling

Implement robust error handling to catch potential issues (e.g., invalid data types, missing elements). Log errors clearly to a file or console.

Data Validation

Validate the data from the Excel file before writing it to the ETAP model. Check data types, ranges, and other constraints.


Comments

Comment your code thoroughly to explain what each section does.

Modular Design

Break your script into smaller, reusable functions.


Use Element IDs Consistently

Ensure the element IDs in your Excel file are accurate and consistent with the ETAP model.

Documentation

Document the purpose of your script, input data requirements, and potential issues.


Example (Python - conceptual)

pythonimport etap.etlimport openpyxl# ConfigurationETAP_PROJECT_PATH = "path/to/your/project.otl"EXCEL_FILE_PATH = "path/to/your/data.xlsx"SHEET_NAME = "Sheet1"try:# 1. Read Excel Dataworkbook = openpyxl.load_workbook(EXCEL_FILE_PATH)sheet = workbook[SHEET_NAME]data = {}  # Dictionary to store element ID: {property: value}header_row = [cell.value for cell in sheet[1]]  # Get header rowfor row_num in range(2, sheet.max_row + 1):  # Start from row 2 (data)row_values = [cell.value for cell in sheet[row_num]]element_id = row_values[0]  # Assuming ID is in the first columndata[element_id] = {}for i in range(1, len(header_row)):data[element_id][header_row[i]] = row_values[i]# 2. Connect to ETAPproject = etap.etl.EtapProject(ETAP_PROJECT_PATH)# 3. Iterate through data and update elementsfor element_id, properties in data.items():try:element = project.FindElement(element_id)  # Assuming FindElement existsif element:for property_name, property_value in properties.items():# Example: Type conversion if needed (important!)if property_name == "NominalKV":property_value = float(property_value)element.SetPropertyValue(property_name, property_value) # Example: Use actual API methodprint(f"Updated element: {element_id}")else:print(f"Element not found: {element_id}")except Exception as e:print(f"Error updating element {element_id}: {e}")# 4. Save Changesproject.SaveProject()print("ETAP project updated successfully.")except Exception as e:print(f"An error occurred: {e}")finally:#Close project regardless of success or failuretry:if project:project.CloseProject()except:pass


2. Using ComplyWise (Third-Party Tool)
What it is

ComplyWise is a third-party software often used with ETAP that provides an interface to help manage and sync data between ETAP models and external databases or spreadsheets.


How it Works

It typically involves setting up a connection to your ETAP model and then configuring mappings between the columns in your Excel file and the corresponding properties of ETAP elements.

Pros



More User-Friendly

Usually a GUI-based interface, making it easier for non-programmers.

Pre-built Functionality

Handles many common data synchronization tasks.


Data Validation

Often includes built-in data validation and error checking.

Cons



Cost

Requires purchasing a license for the software.

Dependency

Relies on the third-party tool being compatible with your ETAP version.


Less Customization

May not be as flexible as the Automation API if you have very specific requirements.

Best Practices



Contact ComplyWise Directly

They can provide specific guidance and best practices for using their software with ETAP.

Follow ComplyWise Documentation

Thoroughly read the ComplyWise documentation for data mapping and synchronization.


Test Carefully

Always test the data synchronization process thoroughly before applying it to your production model.

3. Manual Updates (Not Recommended for Large Datasets)
How it Works

Open the ETAP model, manually find each element based on its ID, and update the properties using the ETAP GUI.


Pros



No Programming Required

No need for scripting or third-party tools.


Simple for Small Datasets

If you only have a few elements to update, this might be the quickest approach.

Cons



Time-Consuming

Very inefficient for large datasets.

Error-Prone

High risk of human error when manually updating properties.


Not Scalable

Impractical for frequent updates or large models.

Best Practices (If You Must)



Double-Check

Carefully double-check each property value before applying the changes.

Document Changes

Keep a record of the changes you make manually.

Choosing the Right Approach


The best approach depends on your technical skills, the size of your dataset, the frequency of updates, and your budget:
Small Dataset, Infrequent Updates, No Programming Skills

Manual updates (but highly discouraged due to error potential).

Medium to Large Dataset, Frequent Updates, Programming Skills

ETAP Automation API (Python or .NET).


Medium to Large Dataset, Frequent Updates, Limited Programming Skills

ComplyWise (or other similar third-party tool).

General Tips



Data Consistency

Ensure the data in your Excel file is consistent with the data types and units expected by ETAP. For example, if ETAP expects voltage in kV, make sure your Excel file provides values in kV,
not volts.

Element IDs

The Element IDs in your Excel file are critical. Double-check that they exactly match the IDs in your ETAP model. Consider using a consistent naming convention for your elements.

Unit Conversion

Be mindful of unit conversions. ETAP and your Excel data might use different units. You'll need to handle these conversions in your script or ComplyWise configuration.


Start Small:
Begin with a small subset of your data and elements to test your script or configuration thoroughly before applying it to the entire model.Remember to consult the ETAP documentation for the Automation API and any third-party tools you are using. Good luck! Flag for review

Previous Question Next Question