2. CAE data sources and formats

CAE solvers rarely produce data in a form that Excel can consume directly. A typical structural run emits a .dat or .txt file with fixed-width columns, a CSV export from a post-processor, and possibly a binary results database that must be converted before analysis. The engineer's first real task is therefore not charting but ingestion: identifying what each file contains, parsing it without corrupting numeric precision, and reshaping it into a flat, tidy table that pivot tables and formulas can operate on reliably. This chapter covers the three common text formats, the practical handling of large result files, and a repeatable structure for raw data before any analysis begins.

Recognizing the three text formats

CSV is the friendliest case: comma- or semicolon-delimited, one header row, one record per line. Post-processors such as ANSYS Mechanical, Abaqus/CAE, and Simcenter export probe or path results this way. The pitfalls are regional: a decimal comma locale will split 1,234 into two fields, and quoted fields containing commas (node sets named "Bolt, M12") require proper quote handling. Always import through Data > From Text/CSV and set the delimiter and decimal separator explicitly rather than double-clicking the file.

TXT is usually whitespace- or tab-delimited and often carries a preamble: solver version, units, load case, and column legends before the numeric block starts. A Nastran .f06 or a CalculiX .dat output may place results after dozens of header lines. The reliable approach is to open the file in a text editor first, count the header lines, and use Data > From Text/CSV > Transform Data to remove them in Power Query rather than deleting rows by hand.

DAT is the least standardized. Depending on the solver it may be fixed-width, may use Fortran-style exponents (1.234-05 meaning 1.234E-05), and may repeat blocks per time step or load case. Fixed-width parsing requires defining column boundaries from the character positions in the header, not from delimiters. The Fortran exponent is a classic silent failure: Excel reads 1.234-05 as text, and every downstream AVERAGE returns zero or an error.

Handling large result files

A transient run with 500,000 nodes across 200 time steps produces 100 million rows — far beyond what a worksheet can hold (1,048,576 rows). Three strategies apply, in order of preference.

Aggregate at the source. Export only the quantities you will analyze: peak von Mises per time step, displacement at a named node set, or a path plot along a defined line. A post-processor can usually compute these before export, reducing 100 million rows to a few thousand. This is almost always the right first move.

Use Power Query as the staging layer. Load the file with Data > Get Data > From File, apply filters (time step = 1.0 s, part = bracket), and load only the filtered result to the worksheet. Power Query streams the file rather than opening it in memory, so a 2 GB CSV can be reduced to a 20,000-row table without exhausting RAM. Keep the query as a connection-only load and reference it from a pivot table.

Downsample deliberately. If you need spatial coverage, sample every n-th node or restrict to a region of interest. Document the sampling rule in a cell so the analysis is reproducible; an undocumented downsample is indistinguishable from a data error later.

Power Query M — filter a large transient CSV to one time step
let
    Source = Csv.Document(File.Contents("D:\runs\bracket\stress_export.csv"),
                          [Delimiter=",", Columns=6, Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted,
              {{"Node", Int64.Type}, {"Time_s", type number},
               {"vonMises_MPa", type number}}),
    Filtered = Table.SelectRows(Typed, each [Time_s] = 1.0)
in
    Filtered

The explicit TransformColumnTypes step is what prevents the Fortran-exponent and text-column problems described above: it forces a type conversion and surfaces errors as a query warning instead of a silent zero.

Structuring raw data for analysis

Once imported, the data must be tidy: one row per observation, one column per variable, no merged cells, no totals inside the data range. A CAE export often violates this by repeating metadata (load case, material, units) in a header block or by spreading time steps across columns. The target structure for a stress history is a long table:

Metadata that applies to the whole file — solver version, unit system, mesh size — belongs in a separate Run_Info sheet keyed by a run ID, not repeated in every row. This keeps the data table narrow and lets pivot tables group by Load_Case or Part without parsing strings. Convert the range to an Excel Table (Ctrl+T) so formulas and charts expand automatically when the query refreshes.

The figure below shows the full ingestion path from solver output to an analysis-ready table, including the decision points where format problems are caught.

A reproducible import checklist

The following sequence is worth applying to every new solver export until it becomes habit. It is deliberately short and ordered so that failures surface early.

1. Inspect before importing. Open the file in a text editor; note the delimiter, the number of preamble lines, and whether exponents use E or Fortran notation.
2. Import through Power Query, not by double-clicking. Set encoding to UTF-8 and the decimal separator to match the file.
3. Remove preamble rows with Remove Top Rows; promote the first data row to headers only if it is genuinely a header.
4. Force column types explicitly. Any column that should be numeric must be typed as type number; a query warning here is a real defect, not noise.
5. Filter and aggregate before loading if the row count approaches the worksheet limit.
6. Load to a table, then run the validation gate: compare the numeric count in the status bar with the expected row count, and check one known column's sum against a hand calculation from the solver log.

With the data staged and validated, the worksheet holds a clean, typed table that behaves predictably under sorting, filtering, and pivoting. That foundation is what makes the subsequent chapters on summary statistics, trend extraction, and automated report generation tractable: every later formula assumes the structure established here, and every later error traces back to a step in this ingestion path that was skipped.