15,047
edits
Comparison of common data file formats: Difference between revisions
Jump to navigation
Jump to search
→Common Data Formats and Limitations
No edit summary |
|||
| Line 2: | Line 2: | ||
{{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }} | {{LanguageSwitcher | content = [[Comparison of common data file formats | EN]], [[Comparison of common data file formats in Mandarin | 漢字]] }} | ||
== Quick Comparison Table of Common Formats == | |||
<table class="wikitable"> | |||
<tr> | |||
<th>Format</th> | |||
<th>Hierarchical</th> | |||
<th>Human-Readable</th> | |||
<th>Row Count / Size Limit</th> | |||
<th>Common Issues</th> | |||
</tr> | |||
<tr> | |||
<td>'''CSV'''</td> | |||
<td>No (tabular)</td> | |||
<td>High</td> | |||
<td>No inherent limit in the format itself; depends on the software used to open it (Excel: ~1.04 million rows)</td> | |||
<td>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)</td> | |||
</tr> | |||
<tr> | |||
<td>'''TSV'''</td> | |||
<td>No (tabular)</td> | |||
<td>High</td> | |||
<td>Same as CSV; no inherent limit in the format itself</td> | |||
<td>Same issues as CSV, but avoids delimiter misinterpretation when field values contain commas</td> | |||
</tr> | |||
<tr> | |||
<td>'''Excel'''</td> | |||
<td>No (tabular)</td> | |||
<td>High</td> | |||
<td>Theoretical limit of 1,048,576 rows × 16,384 columns; 32,767 character limit per cell; performance lags once row count exceeds ~100,000</td> | |||
<td>Long numbers get converted to scientific notation; certain strings get misinterpreted as dates</td> | |||
</tr> | |||
<tr> | |||
<td>'''JSON'''</td> | |||
<td>Yes</td> | |||
<td>Medium</td> | |||
<td>No inherent row limit in the format itself; the bottleneck is that the entire file must be loaded into memory when read</td> | |||
<td>Large files (250MB+) are prone to out-of-memory errors; JSONL is recommended for streaming/line-by-line reading instead</td> | |||
</tr> | |||
<tr> | |||
<td>'''SQLite'''</td> | |||
<td>No (relational)</td> | |||
<td>Low (requires a tool to open)</td> | |||
<td>Theoretical limit of 2⁶⁴ rows, but in practice constrained by the 281 TB database file size limit (roughly 2×10¹³ rows)</td> | |||
<td>Requires a database tool for reading/writing; not convenient for direct viewing</td> | |||
</tr> | |||
<tr> | |||
<td>'''Parquet'''</td> | |||
<td>No (columnar)</td> | |||
<td>Low (requires a tool to open)</td> | |||
<td>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)</td> | |||
<td>The practical bottleneck is disk space rather than the format itself; there are documented cases of successfully writing 500 million to 1 billion rows</td> | |||
</tr> | |||
<tr> | |||
<td>'''SQL file'''</td> | |||
<td>No</td> | |||
<td>Medium</td> | |||
<td>No inherent limit as plain text; the limit comes from the tool used to import it</td> | |||
<td>Text editors can crash or become unresponsive when the file is too large; SQL syntax compatibility issues across different database engines</td> | |||
</tr> | |||
</table> | |||
'''Recommendations:''' | |||
# Need nested/free-form fields → JSON (use JSONL for large datasets) | |||
# Data exceeds a million rows and you only need to move the data → Parquet or CSV | |||
# Need to restore the full database structure (including relations and indexes) → SQL or SQLite | |||
# General use with spreadsheet software → CSV / Excel | |||
== Common Data Formats and Limitations == | == Common Data Formats and Limitations == | ||