CSV

Although I like the flexibility, ubiquitousness and ease-of use of CSV files, I find they have the following problems

• They are not self describing, especially the data types. If CSV files are exchanged between multiple parties, the data types of each field in a CSV file needs to be communicated separately.
• Even if the data types are known, the data-format can still vary (dot vs. comma in decimal numbers, differing formats dd/mm/yyyy, mm/dd/yyyy, yyyy-mm-dd etc. This is almost always a problem if CSV files are generated from Excel with different locales)
• CSV files cannot be annotated with comments (although I believe there are some standards that address this issue)

These limitations gave raise to the schema.ini specification of Microsoft. Such a schema.ini file can be used when reading a CSV file with OLE DB provider for Microsoft Access database engine in order to specify the characteristics of the CSV files.

Although the C in CSV stands for comma, there are good reasons to use semicolons to delimit fields one from another.

One reason is that there are many countries where a comma is already used as a decimal separator (See problems with CSV files).

Also, semicolons make more sense to be used as separators because they are less likely to occur in ordinary text than commas.

Some function examples in various programming languages that allow to read/write CSV data:

• R: write.csv, read.csv()
• Perl: Text::CSV, DBD::CSV, Parse::CSV
• Python: The csv module, pandas.read_csv and Poor man's CSV reader
• The SQL Server statement bulk insert to insert CSV data into a table and openrowset() to select from a table.
• The PowerShell command noun CSV
• Power Query M formula: Csv.Document; Table.FromList is able to create table from CSVs.

Tabular data

Oracle SQL*Loader examples:

• Insert comma separated data
• load CSV file with a header

Using the SQL function regexp_substr (Oracle) to transform CSV text to individual columns in a select statement.

Oracle's json_table function allows to turn comma separated values into a list of records.

Excel:

• Excel: Importing and displaying CSV data
• Convert CSV files in a directory to workbooks with PowerShell.
• The worksheet function textjoin()

pspg is a command line utility to view CSV data (or generally tabular data). (TODO: Compare with usql).

In .NET, a CSV file might be parsed with the Microsoft.VisualBasic.FileIO.TextFieldParser class.

SQLcl allows to create a resultset in CSV

• With the SQLcl and SQL Developer select hint /*csv*/
• set sqlformat csv

A CSV file can be exported from Access under the menu External Data -> Export

Data exchange formats for tabular data. An alternative to CSV files are Parquet files. These are files with a columnar format as well but can be accessed with better performance and additionally support typed and nested schemas.

CSV (as well as also for example JSON) are prone to data corruption.

xsv is command line program for indexing, slicing, analyzing, splitting and joining CSV files. Commands should be simple, fast and composable:

The @csv instruction of jq.