Comparison of common data file formats

How to Open Data Files: Common Formats, Limitations, and Notes

🌐 Switch language: EN, 漢字

Quick Comparison Table of Common Formats

Format	Hierarchical	Human-Readable	Row Count / Size Limit	Common Issues
CSV	No (tabular)	High	No inherent limit in the format itself; depends on the software used to open it (Excel: ~1.04 million rows)	Garbled text in non-English characters; newline characters within field values can cause row misalignment; data type coercion (e.g., leading zero in phone numbers gets dropped)
TSV	No (tabular)	High	Same as CSV; no inherent limit in the format itself	Same issues as CSV, but avoids delimiter misinterpretation when field values contain commas
Excel	No (tabular)	High	Theoretical limit of 1,048,576 rows × 16,384 columns; 32,767 character limit per cell; performance lags once row count exceeds ~100,000	Long numbers get converted to scientific notation; certain strings get misinterpreted as dates
JSON	Yes	Medium	No inherent row limit in the format itself; the bottleneck is that the entire file must be loaded into memory when read	Large files (250MB+) are prone to out-of-memory errors; JSONL is recommended for streaming/line-by-line reading instead
SQLite	No (relational)	Low (requires a tool to open)	Theoretical limit of 2⁶⁴ rows, but in practice constrained by the 281 TB database file size limit (roughly 2×10¹³ rows)	Requires a database tool for reading/writing; not convenient for direct viewing
Parquet	No (columnar)	Low (requires a tool to open)	No theoretical row limit in the format itself (organized by Row Groups, default limit of 1 million rows per group, but a file can contain any number of Row Groups)	The practical bottleneck is disk space rather than the format itself; there are documented cases of successfully writing 500 million to 1 billion rows
SQL file	No	Medium	No inherent limit as plain text; the limit comes from the tool used to import it	Text editors can crash or become unresponsive when the file is too large; SQL syntax compatibility issues across different database engines

Recommendations:

1. Need nested/free-form fields → JSON (use JSONL for large datasets)
2. Data exceeds a million rows and you only need to move the data → Parquet or CSV
3. Need to restore the full database structure (including relations and indexes) → SQL or SQLite
4. General use with spreadsheet software → CSV / Excel

Common Data Formats and Limitations

CSV Files

CSV files: different field values are separated by commas. The CSV format itself has no row limit — the limit comes from the software used to open it.

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

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. 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.

Common issues include Excel automatically converting long numbers (e.g., order IDs) into scientific notation, or misidentifying certain strings (such as 1-2 or MAR3) as dates, resulting in data loss or corruption.

JSON Files

JSON files: suitable for complex or flexibly structured field definitions. A JSON file containing multiple records with a size of approximately 250 MB has been encountered in practice; a read error occurred and the file had to be split before processing. The JSON format itself has no row limit, but the bottleneck is that the entire file must be loaded into memory when read — the larger the file, the more likely an out-of-memory error will occur.

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).

Parquet has no hard row limit. The format stores data in units called Row Groups — the horizontal partitioning unit within a Parquet file. Each Row Group contains a contiguous segment of rows stored column by column. For example, a dataset with 1,000,000 rows and 10 columns would be split into 10 separate column chunks within a single Row Group, rather than stored row by row. This design allows queries to read only the required columns without scanning entire rows, greatly improving read performance. Each Row Group defaults to a limit of 1,000,000 rows, but a single file can contain any number of Row Groups, so there is no theoretical row limit for the file as a whole. In practice, people have successfully written 500 million to 1 billion rows, with the bottleneck typically being disk space rather than the format itself.

SQL Files

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

SQL files themselves have no row limit; the format is plain text, and limitations again come from the tools used to open or import them.

Common issues include:

• (1) Large file size: bulk data exports can easily reach several gigabytes; opening such files in a text editor may cause it to freeze or become unresponsive.
• (2) Cross-database compatibility: different database engines (such as MySQL, PostgreSQL, and SQLite) have differences in SQL syntax; a .sql file exported from engine A may not be directly importable into another engine. See also: Troubleshooting MySQL Technical Issues — Migrating Data from MySQL to MSSQL (written in Mandarin).

If the goal is simply to migrate data rather than restore a complete database structure, it is recommended to use CSV or Parquet instead, in order to avoid the compatibility issues described above.

References

Data factory flow

• 🔍 Data Collection: 1. How to extract content from websites
• 🧹 Data Cleaning: 2. Data cleaning, 3. Regular expression
• ⚙️ Data Processing: 4. Json encode and decode, 5. Convert between date and unix timestamp, 6. MySQL commands
• 📊 Data Analysis: 7. Data exploration
• 📤 Data Output: 8. Comparison of common data file formats, 9. Export MySQL query to Excel file, 10. Microsoft Excel
• 🔧 Troubleshooting: 11. Troubleshooting of MySQL errors