4. Automating data import from CAE post-processing tools

CAE post-processors are where simulation results become engineering evidence, but they are also where automation most often breaks down. Unlike a solver input deck, which is a single well-defined file, post-processing output is fragmented across binary result databases, text reports, image exports, and vendor-specific APIs. A robust Excel automation pipeline must therefore treat data import as a deliberate extraction layer with explicit contracts, not as an afterthought bolted onto a reporting macro.

This chapter examines the three practical extraction routes — native APIs, structured file exports, and text/CSV dumps — and shows how to normalize each into a consistent tabular schema that downstream Excel logic can consume without modification.

Why Post-Processing Import Is the Fragile Link

Consider a typical workflow: a structural analyst runs a static analysis in Ansys Mechanical, opens Mechanical APDL or the Mechanical post-processor, and manually copies nodal displacement values into a spreadsheet. That manual step is slow, error-prone, and non-reproducible. Automating it requires understanding what the post-processor can actually emit and in what form.

Three properties make post-processing data awkward to import:

• Result databases are binary and versioned. A .rst, .odb, or .op2 file is not directly readable by Excel; it must be queried through the vendor's API or converted by a companion tool.
• Nodal and elemental results have different cardinalities. A mesh with 120,000 nodes and 80,000 elements produces two result sets that cannot be joined row-for-row; they must be keyed by node ID and element ID respectively.
• Units and coordinate systems are implicit. A displacement of 2.41 means nothing until you know it is millimetres in the global Cartesian frame, not inches in a local cylindrical frame.

Any import layer that ignores these three properties will produce a workbook that looks correct but silently misreports results. The extraction contract must therefore capture, for every dataset: the entity type (nodal or elemental), the entity ID, the result quantity, the component, the unit, and the reference frame.

The Three Extraction Routes

The diagram below maps the three routes from the post-processor to a normalized Excel table, showing where each route's raw output must be transformed before it is safe to load.

Route A: Querying Results Through Native APIs

Native APIs are the most reliable route because they return typed arrays alongside the model metadata. For Ansys, PyMAPDL exposes results through the post_processing interface; for Abaqus, odbAccess opens the output database directly. The key discipline is to extract the mesh topology and the result field in the same session so that node IDs and element IDs are guaranteed consistent.

A minimal PyMAPDL extraction that pulls nodal displacement and writes a normalized CSV looks like this:

from ansys.mapdl.core import launch_mapdl
import pandas as pd

mapdl = launch_mapdl()
mapdl.resume("bracket_modal.rst")   # example file

# Nodal displacement, global Cartesian, mm
mapdl.post_processing.nodal_displacement("XYZ")
node_ids = mapdl.mesh.nnum
disp = mapdl.post_processing.nodal_displacement("XYZ")

df = pd.DataFrame(disp, columns=["UX", "UY", "UZ"])
df.insert(0, "entity_id", node_ids)
df["entity_type"] = "NODE"
df["unit"] = "mm"
df["frame"] = "GLOBAL_CARTESIAN"
df["step"] = 1
df.to_csv("nodal_disp_step1.csv", index=False)
mapdl.exit()

Two details matter here. First, nodal_displacement returns rows in the same order as mapdl.mesh.nnum, so the ID column is aligned by position — this is an API contract you should verify once per solver version, not assume forever. Second, the unit and frame are written explicitly into the output rather than left implicit in the filename. When the workbook later aggregates results from three load cases, those columns are what prevent a millimetre value from being averaged with an inch value.

For elemental results, the same pattern applies but the key changes to element ID and the quantity is typically a centroidal or averaged value. Elemental stress from a contour plot is already averaged across the element; if you need unaveraged nodal stress, extract it as a nodal quantity instead, because the two are not interchangeable.

Route B: Structured File Exports

When an API is unavailable — a locked-down workstation, a solver version without Python bindings, or a colleague's legacy model — structured export is the fallback. Most post-processors can export a result table to CSV or XLSX with a header row and a unit row. The import layer's job is to detect and strip the unit row before loading, because Excel will otherwise treat it as a data row and coerce the whole column to text.

A practical Power Query pattern for this is to load the file with PromoteHeaders disabled, inspect the first three rows, and conditionally remove the unit row:

let
    Source = Csv.Document(File.Contents("stress_export.csv"),
                          [Delimiter=",", Encoding=65001]),
    Raw = Table.Skip(Source, 2),          // skip title + unit rows
    Promoted = Table.PromoteHeaders(Raw),
    Typed = Table.TransformColumnTypes(Promoted,
              {{"ElementID", Int64.Type},
               {"S11_MPa", type number},
               {"S22_MPa", type number},
               {"S33_MPa", type number}})
in
    Typed

The Table.Skip(Source, 2) is the fragile line: it assumes exactly two preamble rows. A more defensive version reads the first column of each of the first five rows and skips until it finds a row whose first cell matches a known header token such as ElementID. That check costs a few lines and survives export-format changes across solver releases.

Route C: Parsing Text Dumps

Text listings from PRNSOL, PRESOL, or *GET loops are the least structured route and the most common source of silent errors. A typical listing has fixed-width columns, a header block, and sometimes a page break with repeated headers. Parsing it with a naive split on whitespace fails when a value is negative and the minus sign abuts the previous column.

The reliable approach is to parse by column position, not by delimiter, and to validate the row count against the expected node or element count from the model. If the listing contains 120,000 nodes but the parser returns 119,998 rows, two rows were dropped — usually because a value overflowed its field width and pushed the line into a different layout. That discrepancy must fail the import loudly rather than produce a quietly incomplete table.

Normalizing to a Single Schema

Regardless of route, every dataset should land in one table with the same columns. The schema below is the contract that downstream Excel logic depends on; changing it means changing every pivot, formula, and chart that references it.

The source_hash column deserves emphasis. Hashing the source file at import time and storing the hash lets the workbook detect when a result database has been re-run but the import was not refreshed. A simple check — comparing the stored hash against the current file hash — turns a class of stale-data errors into a visible flag rather than a wrong conclusion.

Verification Before the Data Reaches a Formula

An import is not complete when the rows load; it is complete when three checks pass. First, the row count must equal the expected entity count for that result set. Second, the value range must fall inside a physically plausible band — a displacement of 2.41 mm is reasonable for a bracket, 2.41 m is a unit error. Third, a spot-check of three entity IDs against the post-processor's own probe output must match to the displayed precision.

These checks are cheap to automate and expensive to skip. Embedding them as a validation block at the end of the import routine — raising an error and writing a diagnostic row to a tbl_ImportLog sheet — means that a failed import stops the report rather than corrupting it. The next chapter builds on this normalized table to construct the calculation and aggregation layer that turns raw results into engineering verdicts.