Why Does Editing CSV Files Always Feel So Difficult?

CSV files are the backbone of data exchange—simple, universal, and incredibly versatile. It’s not flashy, it’s not trendy, and yet, it is everywhere. But if CSV files are so simple, important and universal then why do they feel so painful to work with?

What Are CSV Files (a quick reminder) ?

The origins of the CSV file date back to the early days of computing when simplicity was key. Developers needed a lightweight, platform-agnostic way to store and share tabular data, and the CSV was born.

CSV stands for Comma-Separated Values. At its core, it’s a simple text file where data is organized into rows and columns, with commas acting as the dividers. For example:

Name, Age, Favorite Food
Alice, 30, Pizza
Bob, 25, Sushi

A text file of tabular data
Columns of each row are delimited by commas
Hence the name, CSV: Comma-Separated Values

What Are CSV Files Used for Today ?

Fast forward to today, and CSV files are everywhere. They are a cornerstone of the digital world, acting as a universal format for data portability across systems. Whether it's accessing massive datasets from the company cloud, or transferring contacts from a phone, CSV files have you covered. Widely used in the ETL (Extract, Transform, Load) field, they enable seamless data transfer between databases with differing proprietary formats. data-engineers take advantage of their simplicity for storing and retrieving tabular data as backups. CSV files integrate well with tools like Excel, Python, or R for data-analists and data-scientists use them as input to machine learning models. Many APIs offer CSV as an exchange format due to its simplicity, and their text-based structure makes them a popular choice on platforms like GitHub, where developers use them to store configurations or small datasets in repositories. Finally, they are the go-to file format when trying to broadcast data publicly.

Database import/export, backup & API
Input format to data analysis/modeling tools
Configuration tables for code

... Just about anything

Why do CSV files remain truly indispensable?

What makes the CSV file truly indispensable is its combination of simplicity, age-old reliability, and non-proprietary nature. There’s beauty in its straightforwardness. You don’t need fancy software to open, read and understand a CSV file—the most lightweight text editors will do (notyepad, fim, emacs...). You can even check their content from the system's command line: cat ./path-to/filename.csv. It’s the file format that is guaranteed to interface seamlessly with any existing data handling software, from legacy systems to cutting-edge platforms. Sure, XML and JSON formats have their moments in the spotlight, but when it comes to reliability that will stand the test of time, the CSV is unmatched.

Simple
Reliable
Timeless
Human-Readable
Universal

Common Problems Working with CSV Files

Working with CSVs isn’t always smooth sailing. Here are some common gripes:

Encoding Nightmares: Non-ASCII characters (such as é and ü) can wreak havoc if encoding isn’t detected properly. It is worth mentioning that the recommended default encoding for CSV files is utf-8.

Comma Confusion: What happens when your data values contain commas? (Spoiler: It can get messy if not handled with the right standard.)
Standard misalignment: Over time, people have tweaked the CSV standards to better fit their needs. An example of this is in european countries where the commas are used for decimal numbers in european countries, the comma was replaced by a semicolon.

Misuse of Excel: Microsoft Excel is a great data analysis tool but poorly suited for CSV files. This holds especially true in the fields of ETL and data-engineering. It often misinterprets CSV standards or value data-types. This often ends up in data corruption and even more so when users have the auto-save mode activated. Furthermore, CSV files or often database extracts that are too large to be handled by Excel making it feel clunky or even crash upon opening a file. What hold for Excel is also true of other major spreadsheet editors such as Google Sheets, Libre Office Calc, or Mac's Numbers.

Poor tooling due to negligence in following standards

Essential Yet Painful to work with – Why?

If CSV files are so important and universal then why does it always feels unnecessarily complicated to open them in a any spreadsheet editor?

Here is a hypothesis:

1. A spreadsheet editor needs a fancy feature to differentiate itself and be competitive.
2. Fancy features require complexity that CSV files can't handle.
3. Editors come up with a proprietary file format to enable this differenciation.
4. Editors leave out CSV file handling (more or less purposly) to push their user base towards their proprietary format.
5. Users can't find a descent CSV file editor.

The solution: An alternative spreadsheet editor dedicated to CSV files

Nanocell-csv is a free, cross platform, spreadsheet editor dedicated to CSV files. Its source code is available on Github to be community driven. Nanocell-csv, pledges to focus only on CSV files and their real-world use cases.

The Nanocell-csv file editor strives to embrace CSV core values:

• Simple
• Reliable
• Universal

Find out more at https://www.nanocell-csv.com/