A data file often looks simple: a few columns, rows and values. Problems usually appear when you need to clean, compare, merge or convert it. A column name may change, a CSV delimiter may be interpreted incorrectly, a number may actually be an identifier, or two files may describe the same information with different structures.
This first part of the Mastering data and tables collection explains what a table really contains and how to choose a format before transforming it.
Structure matters more than the file extension
In a typical table, each row represents a record and each column represents an attribute. A customer export might contain id, name, email and country.
The first row often contains headers. Those headers give meaning to the values below them. If one file uses email and another uses email_address, a merge cannot always infer that both columns represent the same thing.
A reliable dataset therefore depends as much on its schema as on its file format.
CSV: simple and portable, but not ambiguity-free
CSV stands for Comma-Separated Values, yet commas are not the only separators found in practice. Depending on software and regional settings, semicolons or tabs may be used instead.
A CSV file is plain text. It does not preserve Excel formatting, formulas, colors or multiple worksheets. That simplicity makes CSV easy to exchange, but it also means you must pay attention to delimiters, quotes, encoding and line breaks embedded inside cells.
Use the CSV Validator to check the structure of a file before continuing with other operations.
TSV: the same principle with tabs
TSV follows the same flat-table model as CSV but uses a tab character as the separator. It can be useful when values frequently contain commas or semicolons.
CSV and TSV are still flat formats: they describe rows and columns, not complex hierarchies.
Excel: more than a grid of values
An Excel workbook can contain several sheets, formulas, dates, cell types, formatting and other information that does not exist in CSV.
When converting Excel to CSV, some of that information will necessarily disappear. The goal is often not to reproduce the workbook exactly but to extract a usable data table.
The Excel, CSV and JSON Converter is useful when a format change is actually needed.
JSON: objects, arrays and nested structures
JSON is not limited to tables. It can represent objects, arrays and nested values.
A CSV table maps naturally to a JSON array of objects: every row becomes an object and each header becomes a property name. The reverse conversion works best when all objects share a consistent set of properties.
If JSON objects contain nested objects or arrays, converting them to a flat table requires choices: flatten properties, omit some values, or keep JSON as the primary representation.