A brief history of the CSV file
Ahh… the trusty CSV (comma separated values) file! Knocking around ten years before the first personal computer (that’s since 1972), the CSV file is one of the most ubiquitous and useful file types there is. One of the most common uses: transferring data between databases, often CSV to SQL.
Just what is a CSV file?
A CSV file stores tabular data in plain text format. Each line is a data record and each record has one or more fields, separated by commas.
However, CSV files aren’t standardized (although there are some basic rules). The use of ‘comma’ is misleading because you’ll often find colons, spaces, or single or double quotes around data, which makes decoding fields ambiguous. It’s best to think of CSV files as a wider family of files with similar traits. It’s often still possible to easily convert data, for example CSV to SQL or any other DB converter.
W3C recently tried to standardize the CSV file, introducing a set of recommendations for ‘creating tabular data’.
Why is CSV still around?
The IBM Fortran first supported CSV files in 1972.
WikiCommons
This was largely to do with the fact that it’s easier to type CSV lists on punched cards (which is how the Fortran was coded) than it was field-column-aligned data. CSV lists were also less prone to producing incorrect results.
WikiCommons
It just so happened RDBMS were developed around this same time and that CSV is an excellent way of transferring data between incompatible (read: proprietary) formats. For example, exporting from one database system to CSV, converting CSV to SQL, then importing that SQL into another database system. Many database technologies have native support for CSV files, for example MySQL import to CSV.
The plain text format of CSV avoids incompatibilities and supports extremely large file sizes. Plus, CSV is largely human-readable and therefore easier to deal with in the absence of perfect documentation or clear communication.
Like everything, limitations
Despite widespread use the CSV format has its limitations. The inability to represent hieracrhical data is one, which is why JSON and XML formats have excelled as another form of DB converter / data transfer. CSV also has no way of indicating what character set is in use (e.g. Unicode, ASCII), so that needs to be communicated separately, or determined at the receiving end–if possible. (SQLizer supports both Unicode and ASCII characters.)
Additionally, databases that include multiple relations cannot be exported as a single CSV file.
Around for a while because it works
Just like SQL is still around because it does the job 90% of the time, CSV files are still around and in widespread use because they’re an easy way to transfer data. You only need to look at the wide support for consumer, business, and scientific uses for CSV to truly grasp how often CSV is a standard format.
SQLizer converts CSV files to SQL databases, complete with table definitions and INSERT statements. Convert a CSV file now.
NEW in V3.0.5 We just added support for custom delimiters in CSV files which SQLizer now handles effortlessly.
More from The Official SQLizer blog...
- Learning SQL for Beginners in 2024 It’s a data-driven world, and SQL (Structured Query Language) is akin to a treasure map leading to valuable insights. Whether you’re a budding programmer or...
- SQLizer Pricing Update Dear SQLizer Community, We value your continued support and trust in SQLizer. Today, we’re announcing an important change that affects our pricing structure. Why the...
- How to convert XML to SQL: The Clever Way [Updated 2023] To convert XML to SQL some wizardry is needed. Unlike CSV files and database tables, XML files aren’t naturally organized into rows and columns: XML...
- How to convert JSON to SQL - the quick and easy way [Updated 2023] If you want to convert JSON to SQL there’s no concrete or straightforward way of doing things. Conversion is usually tricky because JSON and SQL...
