Comparison of common data file formats: Difference between revisions

{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }}

=== CSV Files ===

Common issues arise because most people open CSV files with Microsoft Excel: (1) '''Garbled Chinese characters''': double-clicking may result in garbled text; a BOM character needs to be prepended to the file, or the file must be imported rather than opened directly. (2) '''Newline characters in field values''': if a field value contains a newline, Excel tends to misinterpret it, causing data from the same record to appear on the next row and resulting in column misalignment. (3) '''Forced type conversion''': a common issue is that the leading zero of a phone number is dropped because the value is coerced into an integer.

If you receive a CSV file containing newline characters in field values, it is not recommended to use Excel; use LibreOffice Calc instead.

TSV files: different field values are separated by tab characters. If field values contain commas, TSV can be used as an alternative.

TSV and CSV are both plain-text formats; the row limit likewise depends on the software used to open them, not the format itself.

=== Excel Files ===

Excel files: a widely used file format, but with limits on cell text length and column count: a single cell cannot contain more than 32,767 characters, and the maximum number of columns is 16,384.<ref>Microsoft Support — Excel specifications and limits: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3</ref> Although the theoretical row limit is 1,048,576 rows (= 2²⁰), performance becomes noticeably sluggish beyond 100,000 rows, making analysis inconvenient. Older versions of Excel (2003 and earlier) allowed even fewer rows — only 65,536 rows (= 2¹⁶) × 256 columns.<ref>[Did You Know] Microsoft Excel has a Limit of Maximum 1,048,576 Rows and 16,384 Columns – AskVG: https://www.askvg.com/did-you-know-microsoft-excel-has-a-limit-of-maximum-1048576-rows-and-16384-columns/</ref><ref>What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User: https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx</ref>

=== JSON Files ===

In practice, it is recommended to avoid storing multiple records in a single JSON file and to use JSONL instead — that is, JSON with newline characters delimiting individual records. This format is suitable for streaming line-by-line with a program, preventing out-of-memory errors.

=== SQLite or Parquet Files ===

SQLite or Parquet files: when the data volume exceeds Excel's row limit, these two formats are worth considering.

The theoretical row limit per table in SQLite is 2⁶⁴ rows (approximately 1.8 × 10¹⁹), but this number is practically unreachable because the database file size limit of 281 TB will be hit first. At maximum database capacity, the number of storable rows is approximately 2 × 10¹³ (assuming no indexes and minimal row size).<ref>SQLite official documentation — Implementation Limits For SQLite: https://sqlite.org/limits.html</ref>

SQL files: the export format for relational databases, consisting of multiple lines of SQL statements (e.g., <code>INSERT INTO ...</code>), which can be imported directly into a database.

[[Category: Spreadsheet]]