1. CAE results data landscape

Before a single pivot table is built, the analyst must confront a deceptively simple question: what does CAE output actually look like when it lands on disk? Solver results are not born in a spreadsheet-friendly shape. They arrive as flat text dumps, binary containers, or zipped archives whose internal structure reflects the solver's memory layout rather than an analyst's mental model. Understanding this landscape — the file formats, the physical quantities they carry, and the structural mismatches that make Excel struggle — is the foundation for every downstream statistical operation in this course.

Common Output Formats and What They Imply

Three format families dominate practical CAE post-processing workflows, and each imposes a different cost on the Excel analyst.

CSV (comma-separated values) is the most pivot-friendly format because it is already tabular: one row per entity, one column per quantity. A typical nodal export from a structural solver might look like this:

NODE_ID,X,Y,Z,SIG_VM,DISP_MAG,TEMP_C
1001,0.000,0.000,12.500,182.4,0.312,68.2
1002,0.000,0.000,12.750,201.7,0.298,69.1
1003,0.000,0.000,13.000,176.9,0.305,67.8
...

The catch is scale. A 2-million-node mesh exported at full precision produces a file in the hundreds of megabytes; Excel's grid limit of 1,048,576 rows means a single sheet cannot hold it. Analysts routinely discover this only after the import stalls.

XLSX appears convenient but is often a trap. Many solvers and third-party tools write XLSX with merged header cells, multi-row titles, and repeated block structures — one block per load case, stacked vertically. This "report-style" layout is readable by humans and hostile to pivot tables, which require a single rectangular table with one header row. The first real task is usually reshaping, not analyzing.

DAT files are the native text output of solvers such as Abaqus, LS-DYNA, and Nastran. They are compact and precise but structurally irregular: fixed-width or whitespace-delimited columns, section headers, and quantity blocks that change meaning depending on a preceding keyword line. A displacement block might read:

 NODE FOOT-NOTE   1X        2X        3X
   1001             0.000E+00 0.000E+00 3.120E-01
   1002             0.000E+00 0.000E+00 2.980E-01

Note the scientific notation, the blank footnote column, and the absence of a temperature field — the columns present depend entirely on which result request was active. Parsing DAT reliably requires either a solver-specific reader or a disciplined text-to-columns routine with fixed widths.

Result Types and Their Statistical Character

The physical quantity determines how it should be aggregated, and aggregating the wrong way produces confidently wrong conclusions.

  • Stress (von Mises, principal, tensor components) is nodal or elemental, strictly non-negative for von Mises, and heavily right-skewed. Mean stress is rarely meaningful; percentiles and maxima near geometric features are what matter.
  • Displacement is a vector with signed components and a magnitude. Component values cancel under naive averaging; magnitude does not. Direction conventions must be fixed before any comparison.
  • Frequency (modal eigenvalues) is a short list per mode, not a per-node field. It belongs in a narrow table and is analyzed by mode number, not by spatial aggregation.
  • Temperature is a scalar field, often with a steady-state and transient variant. Transient results add a time or step dimension, which multiplies row count and demands a time-key column for any pivot.
A practical rule: if a quantity has a sign and a direction, never average its components across a region without first projecting onto a defined axis. If a quantity is a magnitude or an invariant, averaging is defensible but should be reported alongside the maximum.

Why Excel Struggles — and Where It Still Wins

The friction is structural, not a matter of skill. Four mismatches recur:

  1. Row limits versus mesh size. Full-field exports exceed the sheet grid; sampling, filtering to a region of interest, or aggregation at export time is mandatory.
  2. Wide versus long layout. Solvers often emit one column per load case (wide); pivot tables want one row per observation with a load-case key column (long). This unpivot step is the single most common preprocessing task.
  3. Mixed types in one column. DAT files interleave headers, units, and numbers; Excel coerces or truncates silently, and a text-formatted number will not aggregate.
  4. Precision and locale. Scientific notation imports as text under some locale settings, and decimal-comma locales corrupt comma-delimited files outright.

None of this means Excel is the wrong tool. For regional summaries, design-space comparisons, sensitivity sweeps, and report-ready statistics, its pivot engine and charting are fast and transparent. The discipline is to treat Excel as an analysis layer over a curated extract, not as a viewer for raw solver dumps.

From Solver Output to Pivot-Ready Table Output Formats CSV tabular, pivot-friendly risk: row-limit overflow XLSX report-style blocks risk: merged headers DAT fixed-width, keyword-led risk: mixed types Result Types Stress non-negative, right-skewed Displacement signed vector + magnitude Frequency per-mode, not per-node Temperature scalar; adds time key Excel Constraints 1,048,576 rows full-field export exceeds grid wide → long unpivot load-case columns mixed types numbers stored as text locale / precision decimal comma, E-notation Pivot-Ready Table one header row one observation per row keys: node, load case, step numeric fields typed curated extract (sampled / filtered) Each format carries a distinct parsing cost; each result type demands a different aggregation rule.

A Reproducible Preprocessing Check

Consider a concrete example. A static structural run exports nodal results for three load cases as a wide CSV: columns NODE_ID, X, Y, Z, LC1_SIG_VM, LC2_SIG_VM, LC3_SIG_VM, with 850,000 rows. The goal is a pivot summarizing mean and maximum von Mises stress per load case, grouped into ten Z-bands.

  1. Import with explicit types. Use Data → From Text/CSV, set the delimiter, and confirm that NODE_ID is text (identifiers should never be summed) while stress columns are decimal. Watch for a preview showing "Text" where a number is expected — that is the silent failure mode.
  2. Unpivot to long form. In Power Query, select the three LC*_SIG_VM columns and choose Unpivot Columns. Rename the resulting attribute column to LoadCase and the value column to Sigma_VM. Row count triples to 2.55 million — beyond one sheet, which is exactly why the next step matters.
  3. Add the grouping key before loading. Create a custom column Z_Band using integer division, e.g. Number.IntegerDivide([Z], 5) * 5, producing bands of 5 mm. This keeps the aggregation in the query engine rather than in a million-row sheet.
  4. Load to the Data Model, not a sheet. Choose Close & Load To → Only Create Connection and add to the Data Model. The pivot table then reads the full 2.55 million rows without ever materializing them in cells.

Verification criteria: the pivot's grand total row count should equal 850,000 × 3; the maximum Sigma_VM per load case should match the solver's reported global maximum to within export precision; and no Sigma_VM cell should appear as text (check with COUNT versus COUNTA on the column).

Typical failure symptoms and their causes: a pivot that shows a single "Count" instead of "Sum" means the value field is text — recheck the import type. A row count lower than expected usually indicates duplicate NODE_ID values collapsed by a merge, or rows silently dropped at the 1,048,576 limit during a sheet-based load. Stress values off by orders of magnitude often trace to scientific notation parsed as text and then coerced to zero.

The through-line is consistent: the solver speaks in fields and blocks, while the pivot engine speaks in rows and keys. Every technique in the chapters that follow — unpivoting, banding, Data Model loading, calculated fields — exists to bridge that gap cleanly and verifiably. Get the extract right, and the statistics become trustworthy; skip it, and elegant pivot tables will faithfully summarize corrupted numbers.

← Previous 📖 Contents Next →