How Many Entities Can A CSV Hold?

Focus On the Structure

CSV, Excel, and JSONL need more validation love

Tabular data can be handled with as much rigor, quality, and efficiency as document-form, containment models (e.g. JSONSchema, XSD) or tables based data (e.g. SQL). And it should be!

As one example, CsvPath Validation Language can accommodate multiple entities living in a single tabular document using a sophisticated schema syntax as capable as DDL. In fact, it can do this at least four different ways. Each of them common enough in the wild.

1. Mixed parent-child relationships by line adjacency and order
2. Multiple entities' data lines grouped one entity after another
3. Entities side-by-side, line-by-line
4. Entities in sub-table like clusters organized visually and floating in a tabular landscape (e.g. Excel files with ancillary table/boxes for sub-calculations, dimensions, etc.)

The image above outlines each of these.

When is any of this really cool data-geek stuff actually meaningful? Some examples:

• Download your monthly bank statement as a CSV. (No, I'm not suggesting you process your personal accounts using CsvPath Framework, bazooka-meet-ant style). More often than not, there is a set of header information in a multi-line block that has a completely different tabular structure to that of the transactions below
• In insurance case files and hospital pricing disclosures it is not uncommon for blocks of data with different headers to live in the same document. Radiation treatments separate from Radiology. Multiple entities with separate headers and value ranges.
• Many Excel spreadsheets, including this example from the USDA about consumer price changes, have multiple core entities, keys, metadata about the sheet, and other types of complimentary or supporting entities. The red, blue, and yellow highlighted blocks in this example are likely all entities (though I'm just looking quickly, the authors might disagree).

The ability of CsvPath Validation Language to add semantics, relationships, and clear boundaries is a whole new world for CSV and Excel. It has the power to make a lot of documents a lot more machine interpretable. And it offers new ways for AI to conceptualize and explain things to users faced with a spreadsheet or CSV that made sense, once, back in the day, only to the person who created it, long since retired.

Can you think of more ways to position multiple entities in a tabular CSV, Excel, or JSONL data file?

Have a look at this article on CSV validation schemas to see the syntax and some approaches used in the validation part of CsvPath Framework's data preboarding architecture.