2. Importing CAE simulation results

CAE solvers rarely produce data in a form that is immediately ready for analysis. A typical structural or thermal run emits a mixture of fixed-width tables, whitespace-aligned nodal results, comma-separated probe logs, and multi-block reports with repeated headers. Before any pivot table, chart, or automated report can be trusted, the raw solver output must be imported with a repeatable, auditable procedure. This chapter focuses on that import layer: recognizing the file formats solvers actually produce, parsing them correctly in Excel, and using Power Query to turn recurring solver exports into a refreshable data pipeline.

Recognizing the formats solver output actually takes

Three families of text-based output dominate CAE post-processing work. The first is delimited text, most commonly comma-separated values (CSV) or tab-separated values (TSV). These appear when a solver or post-processor offers an "export table" or "export probe history" function, and they are the easiest to import because a single delimiter separates every field. The second is fixed-width output, where each column occupies a defined character range and fields are padded with spaces. Classic Nastran punch and f06-style tables, many Abaqus .dat summaries, and legacy solver reports fall into this category. The third is semi-structured block output: repeated header lines, unit annotations, page breaks, and summary blocks interleaved with numeric data. Power Query is the right tool for the second and third families, and often for the first as well once the import must be repeated.

A practical first step is to open the file in a plain text editor rather than double-clicking it into Excel. Inspect the first 30–50 lines and answer four questions: Is there a header row, and how many lines precede it? Is the separator a comma, a tab, a semicolon, or runs of spaces? Are numeric fields aligned by character position (fixed-width) or separated by a single delimiter? Does the file contain repeated blocks, page headers, or unit lines that must be filtered out? The answers determine the import strategy and prevent the most common failure: Excel silently shifting columns because a delimiter appears inside a field or because fixed-width data was split on whitespace.

Importing delimited text and CSV correctly

For a clean CSV, Data > From Text/CSV is preferable to double-clicking the file, because it opens the Power Query preview where delimiter, encoding, and data types can be confirmed before loading. Set File Origin to the correct encoding (UTF-8 for most modern exports; legacy Windows-1252 or a localized code page for older solver tools). Confirm the delimiter, then check the preview for misaligned columns. A frequent symptom is a numeric column arriving as text because the source used a comma as the decimal separator while the comma was also the field delimiter; in that case choose semicolon as the delimiter or set the locale explicitly in the import dialog.

When a CSV contains a delimiter inside a quoted field—for example a load case named "LC1, static"—the quoting must be respected. Power Query handles standard RFC 4180 quoting automatically, but hand-edited files often contain unbalanced quotes that break parsing. If a row shows an unexpected extra column, inspect that specific line in the text editor before adjusting anything in Excel.

For tab-separated probe histories, the same dialog works, but a subtle issue arises with trailing tabs: empty trailing fields can be dropped, shifting the last populated column. Verify the column count against the header and, if necessary, use Remove Empty Columns only after confirming which columns are genuinely empty across all rows.

Parsing fixed-width solver tables

Fixed-width files cannot be split reliably on spaces, because a negative number, a missing value, and a wide exponent can all produce different numbers of space runs. The correct approach is to define character positions. In Power Query, Data > From Text/CSV offers a Fixed Width option, or you can use Split Column > By Positions after loading the raw lines. The workflow is: load the file as a single-column table of lines, filter out header, unit, and page-break lines, then split by explicit character ranges derived from the header alignment.

Consider a representative fixed-width nodal displacement table (values are illustrative, not from a specific run):

NODE      UX          UY          UZ
   101   0.000E+00   0.000E+00  -1.245E-03
   102   2.310E-04  -8.700E-05  -1.198E-03
   103   4.620E-04  -1.740E-04  -1.102E-03

Here the node ID occupies columns 1–6, UX columns 7–18, UY columns 19–30, and UZ columns 31–42. Splitting by these positions yields clean numeric columns regardless of sign or exponent width. After splitting, set the data type of each result column to Decimal Number; if any value fails conversion, the error identifies the exact row and column, which usually points to a filtered header line that slipped through or a truncated line at a page break.

Building a Power Query pipeline for recurring solver output

The real value of Power Query appears when the same solver export arrives every iteration. Instead of repeating a manual import, build a query once and refresh it. A robust pattern for block-structured solver output is:

1. Load the raw file with Data > From File > From Text/CSV or From Folder if multiple result files must be combined.
2. Keep the raw lines as a single column and remove page headers, unit lines, and separator rows using Filter Rows with conditions such as "does not start with PAGE" or "contains a digit."
3. Split by delimiter or by character positions, depending on the format identified earlier.
4. Promote headers only after the correct header row has been isolated; for repeated blocks, promote headers per block or add a block identifier column before combining.
5. Change types explicitly—node ID as Whole Number, results as Decimal Number—so downstream formulas and pivots behave predictably.
6. Add derived columns such as load case, solver run ID, or unit conversion, ideally sourced from the file name or a parameter table rather than typed by hand.

For multi-block files, a useful technique is to add an index column, detect block boundaries where a header pattern recurs, then use Fill Down on a block label so each data row carries its originating block. This preserves the association between, for example, a load step and its nodal results without manual cutting and pasting.

Verifying the import before analysis

An import is only trustworthy after verification. Check the row count against the solver's reported number of nodes or increments, confirm that the minimum and maximum of each result column are physically plausible for the load case, and spot-check two or three rows against the raw text file. A quick sanity table is often enough:

The diagram below summarizes how the three format families map onto import decisions and where verification fits.

Once the query is stable, save it as a connection-only query and load the cleaned table to the Data Model or a worksheet table. Subsequent solver runs then require only Refresh All, and every downstream pivot, chart, and report inherits the corrected parsing logic. This separation—raw export untouched, transformation captured in the query, verification performed on the loaded table—is what makes automated CAE reporting defensible when results are reviewed or audited.