3. Reading CAE output files

Solvers rarely hand you a clean spreadsheet. A typical structural run produces a mix of comma-separated nodal results, fixed-width text tables, and structured containers such as JSON or XML metadata. Before any post-processing, charting, or report generation can happen, those artifacts must be read reliably, typed correctly, and aligned to a consistent schema. This chapter focuses on the ingestion layer: how to pull solver output into Excel using VBA and Power Query, and how to make that ingestion robust enough to survive real solver quirks.

Know your file families before writing a single line of code

CAE output falls into three practical families, and each demands a different parsing strategy:

• Delimited text (CSV/TSV). Nodal displacements, element stresses, and time-history exports. Usually one header row, but solvers often prepend comment lines, use locale-dependent decimal separators, or emit trailing whitespace.
• Fixed-width or whitespace-aligned text. Legacy solver tables where columns are separated by runs of spaces. Splitting on a single space fails; you need either fixed column positions or a whitespace-collapsing split.
• Structured result files (JSON/XML). Metadata, load-case definitions, and nested result sets. These require a parser that understands hierarchy, not a line-by-line reader.

The decision of which tool to use follows from the file family and the refresh cadence. Power Query is the right default for repeatable, refreshable ingestion of delimited and structured files. VBA is the right tool when you need imperative control — conditional file selection, custom tokenizing, or writing results into specific worksheet regions with formatting.

Power Query: the repeatable path for CSV and JSON

Power Query records a transformation pipeline that can be refreshed when the solver writes a new file. The key discipline is to parameterize the file path rather than hard-coding it, so the same query works across load cases.

// Power Query M — parameterized CSV ingestion
let
    SourcePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    Raw        = Csv.Document(File.Contents(SourcePath),
                   [Delimiter=",", Columns=null,
                    Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Promoted   = Table.PromoteHeaders(Raw, [PromoteAllScalars=true]),
    Typed      = Table.TransformColumnTypes(Promoted, {
                   {"NodeID", Int64.Type},
                   {"Ux", type number}, {"Uy", type number}, {"Uz", type number},
                   {"VonMises", type number}}),
    Cleaned    = Table.SelectRows(Typed, each [NodeID] <> null)
in
    Cleaned

Two details matter here. First, Encoding=65001 forces UTF-8; solver exports frequently contain non-ASCII unit symbols that corrupt under the default ANSI code page. Second, Table.SelectRows drops the blank or comment rows that many solvers append after the data block. If your solver writes a comment header (lines beginning with # or *), use Csv.Document with SkipLines set to the number of preamble lines, or filter on the first column before promoting headers.

VBA: imperative parsing when the format fights back

When a file is whitespace-aligned or has irregular structure, VBA gives you line-level control. The pattern below reads a fixed-width displacement table, collapses runs of spaces, and writes typed values into a worksheet.

Sub ImportWhitespaceTable()
    Dim fso As Object, ts As Object, line As String
    Dim parts() As String, r As Long, c As Long
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Raw")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile("C:\cae\run01\disp.txt", 1)  ' 1 = ForReading

    r = 2
    Do Until ts.AtEndOfStream
        line = Trim(ts.ReadLine)
        If Len(line) > 0 And Not line Like "#*" Then
            line = Replace(line, vbTab, " ")
            Do While InStr(line, "  ") > 0
                line = Replace(line, "  ", " ")
            Loop
            parts = Split(line, " ")
            For c = 0 To UBound(parts)
                ws.Cells(r, c + 1).Value = parts(c)
            Next c
            r = r + 1
        End If
    Loop
    ts.Close
End Sub

The whitespace-collapsing loop is the critical step: it converts any run of spaces or tabs into a single delimiter, so Split produces clean tokens regardless of column alignment. The Like "#*" test skips comment lines. After import, apply number formatting and validate that the row count matches the expected node count — a mismatch usually means a truncated file or an unhandled comment block.

Choosing between the two, and the validation step that ties them together

The table below summarizes the trade-off. In practice, many teams use Power Query for the bulk CSV/JSON ingestion and reserve VBA for the odd fixed-width file or for post-import formatting.

Regardless of tool, always run a post-import sanity check before analysis: confirm the row count equals the expected node or element count, confirm the min/max of a known quantity (e.g., maximum displacement) falls within a physically plausible range, and confirm no column arrived as text when it should be numeric. A quick COUNT and MIN/MAX pair on the imported range catches the majority of silent parsing failures — a decimal comma read as text, a truncated final row, or a header promoted from a comment line.

The diagram above makes the routing decision explicit: the file family determines the tool, and both paths converge on the same validation gate before data reaches the analysis worksheet. Treat that gate as non-negotiable. A parser that runs without error but silently misreads a decimal separator is more dangerous than one that fails loudly, because the downstream stress plot will look plausible while being wrong. Once ingestion is trustworthy, the next layer — computing derived quantities and building report-ready summaries — can proceed on solid ground.