CSV / Delimited Text

CSV (Comma-Separated Values) and other delimited text files are simple, widely-used formats for tabular data.

What You Can Upload

• .csv files (comma-separated)
• .tsv files (tab-separated)
• .txt exports delimited by commas, tabs, semicolons or pipes
• ZIP archives with multiple files

What You Get Out

DataMeans converts your data into multiple modern formats:

How to Export / Obtain Files

Most applications support CSV export:

• Excel/Sheets: File > Save As > CSV
• Databases: Export to CSV option
• Applications: Look for "Export Data" feature

Supported Features

• Delimiter detection (comma, tab, semicolon, pipe)
• Character encoding detection (UTF-8, Latin1, Windows-1252)
• Quotes read as Excel and Python read them: a quoted value may hold the delimiter, doubled quotes ("") and line breaks, and a quote inside an unquoted value, such as 12" pipe, is text
• A value that opens a quote and never closes it is read up to the next delimiter, its quote kept, instead of failing the file; the conversion report counts them
• Lines may end in Windows, Unix or old Mac style, mixed within one file
• The first row is read as the column names; a blank one is named by its position, and a repeated one gets a suffix (Amount, Amount_1)
• Title or comment lines above the column names that hold a single value (Sales report 2024, # exported by ...) are left out and listed in the conversion report
• A row holding more values than there are column names keeps them, in columns named by position (Column5), and the conversion report says so
• Type detection from every row: whole numbers, decimals, yes/no, dates and times in ISO form, and text
• Codes with leading zeros, such as ZIP 02134, stay text with their zeros

Known Limitations

• No built-in type system (types are detected from the data)
• No nested/hierarchical data support
• A value whose quote is never closed may have been meant to run over several lines; it is read as one line, its quote kept, so check the rows the report counts
• Dates are read as dates only in ISO form (2024-01-15). Others, such as 01/02/2024, stay text: a CSV does not say which number is the day and which the month
• Rows with no values at all are left out and counted in the conversion report

Best Practices

• Always include a header row
• Use UTF-8 encoding when possible
• Quote fields containing delimiters or newlines
• Write dates as YYYY-MM-DD so they are read as dates

Note on the Excel formats. An .xlsx sheet holds at most 1,048,575 rows of data and 16,384 columns, and a legacy .xls sheet 65,535 rows and 256 columns. Rather than ship a file that looks complete but is silently cut short, DataMeans leaves a table out of any Excel format it does not fit and notes it in the job report. The .xlsx copy is also left out for tables of more than 8,000,000 cells (1,048,575 rows of 12 columns is 12.6 million), which take too long and too much memory to write, and the .xls copy for tables of more than 150,000 cells, which the legacy format takes far too long to write. The CSV, JSON and SQL outputs always contain every row.

Overview

CSV (Comma-Separated Values) is a simple text format for tabular data exchange, using delimiters to separate fields and line breaks to separate records. Documented by RFC 4180, which also registers the text/csv MIME type, CSV files are widely used for data import/export between applications due to their simplicity and universal support. While not a database format per se, CSV serves as a common interchange format for relational data, with variations in delimiters, quoting, and escaping across implementations.

History and Background

• 1972: IBM's Fortran (level H extended) compiler under OS/360 supports list-directed input/output with commas between values.
• 1978: ANSI approves FORTRAN 77, which formally standardises list-directed I/O using * as the format specifier; commas or spaces separate values, and quoted character strings may contain commas.
• 1983: The manual for the Osborne Executive computer documents the CSV quoting convention of the bundled SuperCalc spreadsheet.
• 2005: RFC 4180 published through the Internet Engineering Task Force (IETF), documenting the common format and registering the text/csv MIME type.
• 2013: W3C charters the CSV on the Web Working Group on 10 December; the group closes on 29 February 2016 after delivering its recommendations.
• 2014: RFC 7111 defines URI fragment identifiers (row, column, cell) for the text/csv media type.
• 2015: W3C publishes the CSV on the Web recommendations, including the Model for Tabular Data and Metadata on the Web, in December.
• Present: Ubiquitous format supported by virtually all major applications and programming languages.

File Format Specifications

CSV files are plain text with specific structural rules.

Basic Structure:

• Records: Lines separated by line breaks (CRLF per RFC 4180; LF widely accepted in practice)
• Final record: The last record in the file may or may not have an ending line break (RFC 4180)
• Fields: Values within records separated by delimiters (comma by default)
• Headers: Optional first record with column names
• Encoding: UTF-8 common today (RFC 4180 cites US-ASCII as common usage); a BOM is sometimes used to signal Unicode

Delimiters and Quoting:

• Field delimiter: Comma (,), semicolon (;), tab (\t), pipe (|)
• Quote character: Double quote (") for fields containing delimiters, quotes, or line breaks

Field Encapsulation:

• Fields containing commas, quotes, or line breaks must be enclosed in double quotes
• Double quotes within fields are escaped by doubling ("")
• Leading/trailing whitespace preserved unless trimmed by parser
• The W3C tabular data model strips leading and trailing whitespace when parsing cells whose datatype is not a string type

Variations:

• Delimiter: Tab (\t) for TSV, semicolon (;) in locales where comma is the decimal separator, pipe (|)
• Quote character: Single quotes (') sometimes used
• Escape sequences: Backslash escaping in some implementations

Key Specifications:

• Maximum field length: Unlimited (implementation-dependent)
• Maximum record length: Unlimited
• Maximum file size: Limited by filesystem and application
• Character encoding: UTF-8 recommended, but varies; the W3C tabular data model further advises Unicode Normalization Form C
• MIME type parameters: charset selects the character set; header takes the values present or absent (RFC 4180); when charset is absent, UTF-8 should be assumed (per the IANA text/csv registration, last updated 2014-01-17)
• CSVW datatype aliases: number maps to xsd:double, binary maps to xsd:base64Binary, datetime maps to xsd:dateTime, and any maps to xsd:anyAtomicType (W3C CSVW Metadata Vocabulary)

Data Types and Structures

CSV has no native data types - all fields are stored as strings. Data typing is determined by the importing application.

File Structure:

• Optional header row with column names
• Data rows with equal number of fields
• Trailing empty lines ignored
• Comments not standardized (some use # prefix)

Version Differences

Compatibility Notes:

• No formal versioning - dialects vary by application
• Character encoding differences cause issues (UTF-8 vs ANSI)
• Line ending differences (CRLF vs LF) between Windows/Unix
• Quote handling varies (required vs optional)
• Field count mismatches common error
• Spreadsheet grid limits constrain imports: an Excel worksheet holds at most 1,048,576 rows by 16,384 columns, with up to 32,767 characters per cell
• CSV injection (formula injection): fields beginning with =, +, -, @, tab, carriage return, or line feed may be interpreted as formulas by spreadsheet software
• TSV constraint: the IANA text/tab-separated-values registration explicitly prohibits tab characters within field values, so TSV has no quoting mechanism for tabs; CSV is preferred when field data may contain tab characters

Technical References

• RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files
• RFC 7111: URI Fragment Identifiers for the text/csv Media Type
• W3C: Model for Tabular Data and Metadata on the Web
• IANA: text/tab-separated-values Media Type Registration
• Wikipedia: Comma-separated values