What is a .CSV file?
The CSV (Comma-Separated Values) is an example of plain-text table format in which all records occupy separate lines while the fields of every one are separated with a comma. This format appeared during the evolution of computer data processing techniques and is not supported by any vendor company as a proprietary technology. At first it got formalization in the text/csv content type of IETF's RFC 4180 with the goal of defining a standard mechanism to parse CSV text files and became RFC 7111-supported with fragment identifiers added to it. The contents of CSV files includes nothing but values stored in cells, the formulae are unavailable, no charts or multiple sheets included into them together with colors of cells, filters, notes, pivot tables, scripts (macros). These files usually come out in exporting from spreadsheets, databases, accounting systems, CRM software solutions, address books, analytic solutions, eCommerce stores, scientific equipment.
Excel supports its own variant of CSV format, with localizing capabilities regarding separators and UTF-8 recognition on the base of Byte Order Mark in old version of Excel. Sometimes in some localities CSV file will be actually semi-colon-separated since there a comma works as the separator of decimal numbers; that means, it will be still text table but noncompliant with RFC 4180. One more related family is CSV on the Web format defined by W3C; that is a combination of the CSV text table and additional file that describes this information with JSON metadata (describing schemas, datatypes, etc.). The quickest way to differentiate the formats is to inspect the file with a text editor.
How to open a .CSV file
Open a .CSV file on Windows
- 1 Go to the folder with your file in File Explorer. Usually, downloaded files end up in Downloads.
- 2 Double-click your .CSV file if you have an app for .csv files on your computer. In case it does not open, move on to step three.
- 3 Right-click your .CSV file -> select .
- 4 Choose Microsoft Excel in the list (Windows 11: choose Always). For Windows 10 -> put a check on "Always use this app to open .csv files" and click OK.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for Windows
- LibreOffice Calc open-source
- Apache OpenOffice Calc open-source
- RStudio Desktop free
- Python pandas open-source
- CSVkit open-source
- Visual Studio Code free
- Notepad++ open-source
- OpenRefine open-source
Open a .CSV file on macOS
- 1 In the Finder app, locate your .CSV file.
- 2 Just double-click your .CSV file in order to automatically launch the program which is connected with it.
- 3 Need another app on your Mac? Control-click the file (or right-click with a mouse). In the pop-up menu select .
- 4 If it isn't in the list, choose and find the application manually.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for macOS
- LibreOffice Calc open-source
- Apple Numbers free
- Apache OpenOffice Calc open-source
- RStudio Desktop free
- Python pandas open-source
- CSVkit open-source
- Table Tool open-source
- Visual Studio Code free
Open a .CSV file on Linux
- 1 Launch your file manager. Depending on the desktop it may be named Files (GNOME), Dolphin (KDE Plasma), Nemo (Cinnamon) or Thunar (Xfce).
- 2 Double-click the file.
- 3 Or choose another program by right-clicking the file -> .
- 4 Install LibreOffice Calc (if it is not installed on your computer) from the software centre, repository or Flathub.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for Linux
- LibreOffice Calc open-source
- Apache OpenOffice Calc open-source
- RStudio Desktop free
- Python pandas open-source
- CSVkit open-source
- Visual Studio Code free
- OpenRefine open-source
Open a .CSV file on Android
- 1 Launch Files or another file management app.
- 2 In Downloads, find the .CSV file and open it by tapping.
- 3 A list of apps capable of opening the file will be shown. Tap Google Sheets and press Always to permanently connect the file type with this app.
- 4 No apps able to open this file? Download Google Sheets from the Google Play Store.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for Android
- Google Sheets freemium
- Airtable freemium
- Microsoft Excel paid
Open a .CSV file on iPhone / iPad
- 1 Use the Files app and go to On My iPhone, iCloud Drive or Downloads.
- 2 Tap the .CSV file you want to open.
- 3 If the file can be opened by a built-in iOS app (supported format), it opens automatically.
- 4 Touch and hold the file to get the context menu -> choose -> select Google Sheets from the list of apps.
- 5 If there is no such option, download the app first from the App Store and then tap the document again.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for iPhone / iPad
- Google Sheets freemium
- Apple Numbers free
- Airtable freemium
- Microsoft Excel paid
Open a .CSV file online
- 1 Move to the online viewer at the top of this page.
- 2 Drag the file there or select it, and its contents will appear in your browser tab. The file is not uploaded.
Still not opening? See common problems and fixes, or view it in your browser.
Programs for Online
- Google Sheets freemium
- Apple Numbers free
- Visual Studio Code free
- Airtable freemium
- Microsoft Excel paid
More details about opening .CSV files
The most common default application for .csv files on Windows is Microsoft Excel and typically the files can be opened just by double-clicking. Some reliable substitutes where the import options are possible to configure and handle the text file in a more customized way are: LibreOffice Calc, Apache OpenOffice Calc, Notepad++, Visual Studio Code, OpenRefine, Python pandas, and csvkit. MS Excel is good to quickly browse the content of the file, whereas Notepad++ and VS Code are better to handle the actual plain text in case you need to deal with CSV file format, and also it is better to perform data cleaning and normalizing with OpenRefine.
In case you have CSV files on a Mac operating system, they can be handled using MS Excel, Apple Numbers application, LibreOffice Calc, a Table Tool application, Visual Studio Code, RStudio Desktop, and different command-line tools. Quick view with Numbers is easier, but you should keep in mind that the program will automatically guess the format as date or number for some fields, like 00123 or 03/04/2024 which will become changed after the importing.
In a case of Linux OS systems, CSV file handling is possible via applications such as: LibreOffice Calc, Gnumeric, Apache OpenOffice Calc, VS Code, RStudio Desktop, pandas, sqlite3 and csvkit without using proprietary software. On Android OS and also iOS, CSV files can also be imported into Google Sheets, MS Excel, and the application called Numbers, but they are better to be used to make quick browsing, not complex manipulation. The best way to import CSV files into these applications is not double click, because the files could be too big.
Double clicking and getting an incorrect running program for importing of CSV? Then the file associations need to be edited. Try to open file with Import and you will see the possibility to set up the delimiter, the encoding option, and column type. If all data ends in one column, select the delimiter. In a case if you get symbols with a messed up accent - reopen this file using encoding of UTF-8, or UTF-8 with BOM, or also Windows-1252, ISO-8859-1
Technical details
The CSV file format is plain text as opposed to binary file. CSV file does not have magic numbers, it does not carry a signature nor can it contain a built in data compression wrapper. The IETF CSV definition in the Internet standard document RFC 4180 defines a CSV file as an organized collection of records each record separated from each other by CRLF. The file can optionally feature a row which functions as a header. Records feature commas that divide fields, and any field with a comma, quote mark or a line break needs to be enclosed in double quotes. A literal double quote inside a quoted field is represented by repeating the double quote character, so the string He said "yes" gets expressed as "He said ""yes""".
There is a registered file MIME type as text/csv. The text/csv type has been extended by RFC 7111 with URI fragment identifiers for rows, columns and cells in a text/csv resource. The coding of CSV file is not descriptive, however UTF-8 is common. In Microsoft spreadsheet program, detection of UTF-8 coding is better when the first bytes in the file are EF BB BF. Other legacy implementations might be based on Windows-1252.
The format of the line endings varies as RFC 4180 specifies CRLF, but in most cases lines might have LF ending especially when used with unix like programs or might use CR ending when derived from legacy implementations. Embedding of a new line into a field is possible only when the field is quoted. The format does not have a location for storing the metadata such as the type of column, the locale setting, the markers for null fields, units and schemas. It also lacks location to store version numbers, primary keys and sort order. The size of the file can vary between a few bytes and many gigabytes.
How to convert .CSV files
When it's useful to have further Excel processing after importing a CSV file then converting the file from CSV format to Excel's native format will be helpful (formatting, using formulas, more sheets, applying filters etc). The procedure to convert CSV to XLSX can be done easily using the next instruction: libreoffice --headless --convert-to xlsx --outdir out input.csv. Options like csvkit, pandas and R readr can handle frequent exporting tasks.
In case if it is needed to convert a CSV into JSON (for use with web API requests for example), note that there must be rules how nested JSON can be constructed from a CSV, which represents flat data (no array/object nesting etc.). In that case csvkit tool can help with such kind of task: csvjson input.csv > output.json (the names in the header row represent the objects' fields).
For working with large data and need for indexation, validations, queries, joins between tables and other advanced procedures, it is best to use DBMSes instead of CSV files: SQLite and PostgreSQL support direct reading of CSV files with special keywords and constructions: sqlite3 supports direct loading with keywords .mode csv and .import --skip 1 input.csv mytable. COPY is a way for PostgreSQL.
In cases when it is necessary to process a file with analytics software, it should be converted either in Parquet file (columns typed and compressed with encoding scheme) or Feather file which has more readable type definitions for columns.
If a CSV file is full of commas, it's useful to convert it into a TSV file where embedded new-line characters and tabs in each field require special cleaning procedure: csvformat -T input.csv > output.tsv.
Before all conversions one has to define what delimiter to use, what encoding is, date format and what kind of decimal separator are. Conversions usually lose precision of values, leading zero digits, differences between empty strings and NULL values, time zones, encodings and newline characters inside of values etc.
| Convert to | Tool | Command / note |
|---|---|---|
| XLSX | LibreOffice Calc | libreoffice --headless --convert-to xlsx --outdir out input.csv Creates an Excel workbook; delimiter, encoding, dates, and numeric type inference may need manual import settings for reliable results. |
| JSON | csvkit csvjson | csvjson input.csv > output.json Converts rows to JSON objects using the header row as keys; duplicate or blank column names must be fixed first. |
| SQLite database | sqlite3 | sqlite3 output.db ".mode csv" ".import --skip 1 input.csv mytable" Imports to a table; create the table first for explicit column names and data types. |
| TSV | csvkit csvformat | csvformat -T input.csv > output.tsv Changes the delimiter to tabs; embedded tabs or inconsistent quoting may still require cleaning. |
| HTML table | Python pandas | python -c "import pandas as pd; pd.read_csv('input.csv').to_html('output.html', index=False)" Useful for publishing a small table; formatting and formulas are not preserved because CSV has none. |
| Parquet | Python pandas with pyarrow | python -c "import pandas as pd; pd.read_csv('input.csv').to_parquet('output.parquet', index=False)" Good for analytics; Parquet adds typed, compressed columns but inferred types should be checked. |
Common problems and fixes
The other common situation is that the CSV-file in Excel is usually opened by Excel with wrong separator and hence all columns are displayed in the very first column (in column A). The reasons are related to usage either semicolons, tabs, or other locale separators instead of system list-separator. To avoid this issue you need either to go to the menu and use «Data» -> «From Text/CSV», specifying delimiters during import, or else adjust CSV import setting before opening. Please remember that a presence of the file with the extension «.csv» doesn't mean that CSV contains commas, check it first line in plain-text editor.
Second, the problems can relate to encoding of text. The characters that could occur (é, £), so-called «mojibake» appear if a CSV is saved and imported in UTF-8 encoding but you open it by Windows-1252 or vice versa. In this case it will be enough either to reopen a file using correct encoding (UTF-8 or Windows-1252 respectively), or specify the exact format in which you need to save the document (UTF-8). Also a presence of a UTF-8 BOM is possible, which can be seen as «ï»¿ at the beginning of a header - either remove this BOM or use a parameter utf-8-sig
Thirdly, wrong inference about spreadsheet-like data leads to silent distortion of data. Namely the long identifiers appear in the scientific view of number, numbers with leading zeros can lose zeros. Hence use this function to import columns not in general (number) mode, but in text mode.
Moreover, dates can be distorted as well depending on program where they were written. For instance for the date 03/04/2024 there are two variants to be - March 4 or 3 April. It means that during import we have to set the right date locale and format.
Moreover, wrong quotes can ruin the parsers. Namely unpaired quotes or carriage return lead to the appearance «newline inside string» or «EOF inside string». It also can change the position of the columns making all fields going right.
To repair bad quoting of text use validators as csvlint/csvkit. Finally too large documents can cause the problems related to performance and even limitations (in some programs the limitation is about the number of rows). Thus we can divide the CSV into parts, load it in SQLite or deal with it using streaming tool.
Security considerations
CSV files do not have any embedded objects, nor do they feature macros, executables, binary files, or active scripts in their construction. Formula injections constitute the main threat where imported cells starting with an equal sign, plus, dash, at sign, TAB or CR will be treated as a spreadsheet function, thus activating such features as HYPERLINK, or WEBSERVICE or even the older DDE. In order to mitigate the problem when using user-controlled file exports, prefix suspicious cells with an extra apostrophe, protect yourself from special characters or just open the file using an import-as-text tool.
The main information stored in CSV files can range from names, addresses, personal numbers and coordinates of people, salaries or medical data, although this type of files does not use any native encryption. The hidden data will be less than in XLSX formats since there will hardly be any meta data stored besides the visible rows and header names. CSVs should always be checked for correct delimiter count per record prior to being used as data inputs for databases and scripts.
History and versions
Delimited text tables existed prior to the emergence of PC-era in mainframes, with comma-separated records existing without a CSV designation. One of the historical instances is IBM document describing Fortran compiler from 1972, that talks about List-directed read that uses comma-separated values. CSV later propagated via various application software, when in 1979 was issued VisiCalc, that helped popularize delimited text exchange. This format was later followed and used by other spreadsheet/database packages, like Microsoft Excel which first appeared for Macintosh in 1985 and for Windows in 1987 and uses .csv format for export/import.
Standardization process started much later: IETF RFC 4180 document by Yakov Shafranovich appeared in 2005, registering text/csv MIME type and specifying most popular CSV dialect format. Later RFC 7111 added URI fragment designations for row, column and cell specifications. The latest standard on use of the CSV format appeared in 2015 when W3C released CSV on the Web recommendations concerning meta-information and conversions. Still one must admit CSV format being very much relevant because of many reasons - good readability, easy generation, streamability, being independent of any natural language or computer system.
Related formats
CSV is not the only available choice here. Instead of comma, one may employ tabulations as in TSV files (.tsv, .tab), which will work in case some text fields happen to contain commas. Nevertheless, tabulation inside a field can cause trouble while parsing into import operations. Finally, pipe-delimited text files (.psv, .txt) are widely used in various types of data processing, as in data warehouses, logs etc., since these files avoid comma and decimal-separator conflicts, and are still less supported than CSV files by spreadsheet programs.
For spreadsheet formats one should pick Excel files (.xlsx and .xls), or OpenDocument Spreadsheet (.ods), since these file formats maintain all features like formulas, formatting, sheets, charts, filtering etc., which must be preserved.
For basic usage CSV will be most preferable file format when it comes to editing flat tables.
When one speaks about working with hierarchic or API related data JSON (.json) will come into use. XML handles nested structures (.xml). When one requires analytic data, with types and compression, Parquet (.parquet) or ORC (.orc) may be picked instead of CSV, SQLite (.sqlite, .db) in case of indices and other similar tasks.
No comments yet. Be the first to share your experience with this file type.