Query Multi-Record CSV Files with SQL

See everything Query Streams can do beyond this guide Get Started Free

Supports

+more

Live Data

Replace exports with on-demand SQL

Excel Sheets

BI & AI Analytics

Ask Nova, get SQL + charts

Meet Nova

+more

Automation

Scheduled sync to 6+ platforms

Explore

+more

API to SQL

Query APIs with SQL, no code

Explore

Query Streams is a secure, real-time database integration platform, and its Multi-record CSV connector reads the awkward machine export files that every ordinary CSV tool refuses — the ones where each line begins with a record-type tag and each tag has its own columns. Learn more at QueryStreams.com and sign up for free to point it at your first folder of exports.

Most people who own one of these files do not know the format has a name. They know only that it arrives with a .csv extension, that opening it in a spreadsheet produces something unreadable, and that every tool they try either fails outright or quietly mangles it. So the file gets processed by hand, or by a script somebody wrote years ago and nobody wants to touch.

The file that breaks every CSV tool

Here is the shape. Notice that there is no header row, and that the number of columns changes from line to line.

HDR,2026-08-24,MC-07,v2.1
ORD,A-10021,ACME Joinery,2026-08-26
ITM,A-10021,Door blank 18mm,4,62.50
ITM,A-10021,Edge banding 22mm,12,3.20
ORD,A-10022,Bramble Interiors,2026-08-27
ITM,A-10022,Shelf panel 15mm,20,18.75
TRL,2,3,412.90

The first field on each line is not data. It is a record-type tag, and it says what the rest of the line means. HDR is a file header with an export date, a machine identifier and a format version. ORD is an order with a reference, a customer and a due date. ITM is a line item with a quantity and a unit price, so it has five fields where ORD has four. TRL is a trailer with the counts and a total, there so the receiving system can check nothing was lost in transit.

This is a record type CSV, sometimes called a mixed record CSV or an EDI style CSV. It is common in places that predate the assumption that a file is a rectangle: woodworking and manufacturing machinery, warehouse management systems, banking statement formats, freight and customs interchange, and line-of-business software written when a flat file with typed lines was the obvious way to move a whole document in one go.

Every ordinary CSV tool fails on it at the first line, and for the same reason. Microsoft Excel, Power Query, a Python read_csv call and a database bulk loader all assume one header and one shape per file. Given a file with four shapes in it, the best case is an immediate error about the column count; the worse case is a table where 62.50 has landed in a column of customer names and nobody notices for a month.

One table per record tag

Query Streams does not try to force the file into one table. It reads the tag, and gives every tag its own table with its own columns. Four tags means four tables, and each one is a normal, well-shaped SQL table you can query without thinking about the file it came from.

The folder machinery is the same as every other File Set connector, and is described in how to query a folder of files with SQL: the agent scans the folder, samples up to 64 files, pins what it found, and thereafter reads only what changed. The difference here is what gets pinned. Instead of one table definition, the connector pins one table definition per tag, each carrying the tag it belongs to and how many fields a line of that tag must have.

Types are worked out per tag and per position from the sampled data, using a deliberately small ladder: whole numbers become BIGINT, decimals become an exact DECIMAL rather than a floating point number so money does not drift, 2026-08-24 becomes a DATE, a date with a time becomes a TIMESTAMP, and anything else stays text. An empty field reads as NULL.

Where the table names come from

A table is named data_ followed by the tag, lower-cased, with anything that is not a letter or a digit turned into an underscore and an underscore inserted where letters meet digits. That last rule matters for machine formats, which love numbered tags: a Homag panel export tagged PNLHDR1, PNL1, PNL2 and PNL3 produces data_pnlhdr_1, data_pnl_1, data_pnl_2 and data_pnl_3, which read far better in a query than the raw tags would.

The data_ prefix is fixed, and that is why this card has no Table name field: the names have to come from the pinned manifest, because only the manifest knows which tags the folder actually contains. You cannot choose the prefix, so write your saved queries against data_ord rather than expecting a name of your own.

If two different tags would produce the same table name, the connector refuses to pin and names both tags rather than merging them. Silently combining two record types that the machine deliberately kept apart is exactly the kind of wrong answer that looks right.

The sequence column that rejoins a group

Splitting one file into four tables is only half the job. The records were related when they were lines in a file, and that relationship has to survive the split or you have simply traded one unusable file for four disconnected ones.

Every table therefore carries a column called _seq. Its definition is precise and worth reading twice: _seq is the zero-based occurrence index of that tag within that file. The first ITM line in a file has _seq = 0, the second has _seq = 1, and so on, counted independently for each tag.

That is what makes the repeating-group formats work. A great many machine exports are built as a fixed set of record types that repeat together, one of each per logical item — the panel export above writes a PNL1, a PNL2 and a PNL3 for every panel in the job. The tenth panel’s three records all land with _seq = 9, in three different tables, so putting the panel back together is an ordinary join on _source_file and _seq.

SELECT a._source_file,
       a._seq        AS panel_no,
       a.field_1     AS panel_ref,
       b.field_2     AS width_mm,
       c.field_3     AS material
FROM   data_pnl_1 a
JOIN   data_pnl_2 b
  ON   b._source_file = a._source_file
 AND   b._seq         = a._seq
JOIN   data_pnl_3 c
  ON   c._source_file = a._source_file
 AND   c._seq         = a._seq
ORDER  BY a._source_file, a._seq;

_source_file belongs in the join because _seq restarts at zero in every file. Without it, the tenth panel of Monday’s export would join to the tenth panel of Tuesday’s.

Joining a header to its line items

Look again at the sample file and you will see that the machine has already solved this for you. Each ITM line repeats the order reference from the ORD line above it, in its first data field. That is standard practice in these formats precisely because the receiving system is expected to reassemble the document, and it means the join is the one you would write against any relational database.

SELECT o.field_1                        AS order_ref,
       o.field_2                        AS customer,
       o.field_3                        AS due_date,
       COUNT(*)                         AS line_count,
       SUM(i.field_3 * i.field_4)       AS order_total
FROM   data_ord o
JOIN   data_itm i
  ON   i._source_file = o._source_file
 AND   i.field_1      = o.field_1
GROUP  BY o.field_1, o.field_2, o.field_3
ORDER  BY order_total DESC;

That query is the whole point of the connector. A folder of machine export files, none of which any spreadsheet will open, now answers a question about order values with eleven lines of ordinary SQL — and it answers it across every file in the folder at once, not one file at a time.

If your format does not repeat a key on the child records, the line ordering is still available to you. Alongside _seq, every row carries a _row_id built from the file name and the physical line number the row was read from, zero-padded so it sorts correctly. Because the line number is the real one, a row identifier and a warning about a bad line refer to the same coordinates, and window functions over _row_id can attribute each child record to the most recent preceding header.

Why the parsing happens in C#

This is the only File Set driver that parses files itself. Every other one — CSV, Parquet, Excel, SQLite, JSONL — hands the file to DuckDB, which is very good at reading files quickly and is the reason a folder of Parquet feels instant. The Multi-record CSV driver deliberately does not.

The reason is the same assumption that defeats every other tool. DuckDB’s CSV reader is built around one rectangular table per file: one column list, one type per column, applied to every line. Point it at a file with four record shapes and there is no set of columns it could be given that would be correct, so it would either error or produce garbage. Speed is no use if the answer is wrong.

So the agent reads the file line by line, splits each line with the same quote-aware splitter the CSV connector uses, looks at the tag, checks the line against that tag’s pinned definition, and routes it to the right table. The rows are then written into the local cache in batches inside a single transaction per file, so the query side still gets a real database to work against.

There is an incidental benefit. Because DuckDB never sees the raw bytes of these files, a whole class of encoding hazard that the CSV Folder connector has to guard against cannot arise here at all. Character encoding is still detected per file and still has to agree across the folder, and a file containing bytes that are not valid in the detected encoding is held back rather than decoded into nonsense.

When a line does not match the pin

Once the tags and their shapes are pinned, every line of every file is checked against them before anything is written. Three things can disagree, and all three are treated the same way.

• A tag the pin has never seen — the machine started writing a record type that was not present when the folder was sampled.
• A known tag with the wrong number of fields — the format changed, or the tag column is not where the connector was told to look.
• A value that does not fit its pinned type — text in a column that was pinned as a number, or a date in an unexpected format.

Any of these parks the file: none of its rows are written to any of the tables, and the reason is recorded against the file name, the line number and the tag. Parking is the general File Set behaviour described in the hub guide, and you find parked files by querying files_events for drift.

SELECT relative_path, detail, occurred_at
FROM   files_events
WHERE  event_type = 'drift'
ORDER  BY occurred_at DESC;

Two details are specific to this driver and make the report unusually useful. First, the whole file is validated before a single row is inserted, so a parked file has genuinely contributed nothing anywhere — you can never be looking at a table that holds an order header whose line items were rejected. Second, the check does not stop at the first problem: up to twenty violations are listed with their line numbers, then a count of the rest, so one pass tells you whether you have a single stray line or a format that has moved on.

An unknown tag is the one you will meet most often, and the message names both the tag it found and the tags it knows about. If the new record type is one you want, changing any connector option re-samples the folder and pins the new tag as its own table. If it is one you do not care about, exclude the files that carry it with a file pattern.

Every multi-record setting, and when to change it

The Multi-record CSV card carries four format-specific settings, and most folders need only one of them touched, if any. Everything else on the card — root folders, recursion and max depth, exclude patterns, symlinks, the filename parse pattern, folder tokens, scan interval, hashing, the delete policy, event retention, the scan limits and the four ingest caps — is shared with every other folder type and is documented once in the File Set connector guide. This section covers only what is particular to a tagged-line file.

Note what is not here, because it is a common expectation. There is no header option, since these files have no header row at all. There is no null strings or date format option, and no table layout choice. And there is no table name field: multi-record CSV is a multi-table driver, so table names come from the record tags in the pinned manifest rather than from anything you type.

Record tag column (number)

Default: blank, meaning the first column. This is the setting that defines the format, and it is worth reading carefully because of one detail: it is a 1-based column number, not a column name. Enter 1, 2, 3 — never record_type.

That is deliberate rather than an oversight. A multi-record file has no header row, so its columns have no names; a name would be meaningless because there is nothing in the file for it to match. The connector therefore refuses a non-numeric value by name when you save, rather than guessing which column you meant.

When to change it: when the tag is not the leading field. Leave it blank for the overwhelming majority of these formats, which put the tag first. Set it to 2 or 3 for the exports that lead with something else — a timestamp, a batch identifier, a site or machine code — and carry the record type after it. To find the number, open one file in a text editor and count the fields from the left, starting at one, until you reach the one holding HDR, ITM, PNL1 or whatever your tags are.

2026-08-24T06:11:02,BATCH-7741,HDR,MC-07,v2.1
        1                2         3     4    5
                                   ^
                        Record tag column = 3

Delimiter

Default: a comma. Type the character itself, so ; or |. This matters more here than on an ordinary CSV folder, because the machine and European exports that use the multi-record shape are frequently semicolon-separated — if your file came off a panel saw, a CNC controller or a continental ERP, ; is the first thing to try.

The dialect rules are the same ones the plain CSV folder follows, including why the delimiter is pinned rather than re-guessed per file. Those are set out in combine multiple CSV files into one table and are not repeated here.

Get it wrong and you will know quickly. A semicolon file read with a comma delimiter gives every line a single field, which means a single “tag” containing the whole line — so you get one table per distinct line, or a refusal. Neither looks like success.

Quote character

Default: a double quote. This is the character wrapped around a field that contains the delimiter, so "Smith, John" stays one field rather than becoming two. Change it only for the unusual export that uses single quotes instead. If you are seeing addresses or free-text descriptions split across positions, and the field counts on some lines therefore fail to match the pin, this is the setting to look at.

Encoding

Default: auto-detect, which is right almost always. It checks for a byte-order mark, tries a strict UTF-8 decode, falls back to Windows-1252 heuristics, and pins the answer. The explicit choices are UTF-8, Windows-1252 (cp1252), UTF-16 LE and Latin-1.

When to override: when detection refuses because the sampled files disagree and you know which reading is correct. For this format specifically, Windows-1252 is the usual answer — older industrial controllers and European line-of-business systems write cp1252 without a byte-order mark, which is precisely the case detection has to infer rather than read.

One deliberate distinction: cp1252 and Latin-1 are not treated as the same encoding, because they disagree on the byte range holding smart quotes, the euro sign and dashes. Choosing the wrong one of the two turns a euro sign into a control character instead of raising an error, so pick the one your exporter actually writes.

Include patterns — keep multi-record files away from ordinary CSVs

Include patterns are a shared setting, but they deserve a word here because of one trap that is specific to this connector. The card seeds the pattern to **/*.csv, and multi-record exports almost always live in a folder alongside perfectly ordinary CSV files. They are not the same connector, and they must not be mixed.

Narrow the pattern so only the tagged files are in scope — **/job_*.csv, exports/machine/**/*.csv, whatever separates them. A plain CSV read as multi-record does not error politely: its first column is a real data column, so every distinct value in it is treated as a record tag, and you get a table per customer name or a table per order reference. If you cannot separate them by pattern, that folder needs two connectors on two roots.

Manifest overrides (JSON) — the way to give columns real names

Behind Show advanced options. This setting is shared with every folder type, but it is worth more on this one than on any other, and for a simple reason: because these files have no header row, your columns arrive with generic positional names — field_1, field_2, field_3. Manifest overrides are how you turn them into names a person can read, once, in the connector, so that every saved query, spreadsheet refresh and Nova question afterwards sees sensible columns.

{
  "renames": { "qty": "quantity" },
  "retypes": { "price": "DECIMAL(18,4)" },
  "exclude": [ "internal_notes" ]
}

• renames — the important one here. Map each positional column onto the name the record type actually means: { "field_1": "order_ref", "field_2": "description", "field_3": "quantity", "field_4": "unit_price" }. You cannot rename onto _source_file, _row_id or _seq, and two columns cannot be renamed to the same thing.
• retypes — force a column’s type. The usual case in machine exports is a part or account code made entirely of digits that was pinned as a number and lost its leading zeros; set it to VARCHAR and it comes back as text. A type you set here is a deliberate instruction, so it is applied without the strict checking that would otherwise park a file.
• exclude — leave columns out of the tables entirely. Useful for the padding and reserved fields that tagged formats are full of.

Overrides are applied when the shape is pinned, so they become part of the table definition rather than something re-applied at query time. Emptying the box clears your overrides and returns the tables to the shape that was sampled. Only plain type names with optional precision are accepted — DECIMAL(18,2), VARCHAR, BIGINT, DATE — and anything unusual is rejected when you save rather than at sync time.

Setting it up

You need a Query Streams account and the Network Agent installed on a machine that can see the folder — often the machine that receives the exports, or a server with the share mounted. Multi-record CSV requires Network Agent 2.6 or newer.

1. Install the agent from the download page if it is not already running.
2. Open one of your files in a text editor first and confirm two things: which column holds the tag, and which tags actually appear. Two minutes here saves a puzzled refusal later.
3. In the portal, add a data connector and choose Multi-record CSV.
4. Enter the folder path as the agent’s machine sees it, plus a file pattern if the folder holds exports of more than one kind.
5. Set Record tag column only if the tag is not the first field, and Delimiter only if the files are not comma-separated.
6. Save. The agent samples the folder and reports the tags it found, one table per tag, with the columns and types of each.
7. Open the query builder and write your first join — or describe the tags to Nova AI and have it written for you.

Because the sampled files must agree on every tag’s shape, this is a connector that tells you something about your data the first time you run it. A refusal naming a tag that has different field counts in two files usually means the machine’s firmware or the export template changed on a date you can now go and find.

From there it behaves like any other data source. The tables appear in Microsoft Excel and Google Sheets through the add-ins, in Airtable, Smartsheet, Baserow and SeaTable through scheduled syncs, in Nova AI, and in Claude, Cursor, ChatGPT or Grok through the Query Streams MCP server. They also join to your databases, so a machine export can be reconciled against the orders table in PostgreSQL in a single read-only statement.

Frequently Asked Questions

Get Started

Related guides: How to query a folder of files with SQL | Combine multiple CSV files into one table | Download the Query Streams Agent | All File Set Connector guides

Category: File Set Connectors

Tags: multi record csv, record type csv, mixed record csv, edi style csv, machine export file, file set connector, sql on files

Meta Description: Query CSV files where every line has a different shape. One SQL table per record tag, rejoined with SQL.