What is CSV(Comma Separated Value) file? When to use CSV File? How to open CSV file Format?

👤 Diwas Poudel    🕒 21 May 2022    📁 TECH

In computers, there are various file extensions and file types. Txt, pdf, xlsx, and other popular file extensions Have you ever come across an a.csv file? It is not widely used or popular among the general public. If you want to learn about CSV files, how to use them, when to use them, and why to use them, you've come to the right place. Let's get started.

What is CSV File?

CSV file stands for Comma Separated Value file.CSV file is a type of plain text file that is a common format used for data exchange between different applications. It is used in programming and the data field.CSV file handles only tabular data. Data inside the CSV file will be number, string only, and does not contain formulas, etc.

Also Read: Websites for free books for kids 

How does the CSV file look?

Ans: CSV file looks like this:

In detail overview of content inside CSV file.

1. CSV file extension should be .csv.

2. The standard format of CSV data is defined by rows and columns.

3. Each row ends by newline then the next row begins.

4. Each column is separated by a comma. But other delimiters like colon, semicolon, space, equal sign, a tab is also used. But the last value is not followed by a comma.

Eg. "Diwas","Poudel",45,"Jhapa, Nepal"

5. Each line in the CSV file is called a record and this acts as a row of data in a table.

6. Leading and trailing space of character between comma field separators are ignored and trimmed out.

Eg. Diwas , Poudel,45, "Jhapa, Nepal" resolves to "Diwas" and "Poudel"

7. The fundamental concept of separating fields from commas is simple, but when field data contain commas as well then the situation becomes complicated. Because there is no standard method for doing this. But general solutions look like this:

The solution for this is: Enclose the field in double-quotes.

Example from Point 4, ie. "Jhapa, Nepal" has a comma within a single field so quoted whole data in double quotes

8. If any field has double-quote characters then those characters must be surrounded by double-quotes.

For example, " Diwas "Raj"  can be written as "Diwas""Raj"""

CSV is data and database file extensions.Some other file of this types are : .dat, .db, .sql, .tar, .mdb etc.

The IETF document RFC 4180 first published in 2005 contains the standards for the data format of CSV files.RFC 4180 is a technical standard that formalizes the CSV file format and defines the MIME text type in CSV as well as the handling of text fields. 

Why use CSV?

1 It is used to import and export the data from one application to another.

2 It is a platform-independent file. You can create a CSV file in a Windows environment and can open the same file in Linux and Mac environments.
3 Large number of applications can open this file easily. Some applications that can open this file are excel, office, google sheets, LibreOffice, GenScriber, OpenOffice Calc, Fresh Books, Mobi systems OfficeSuite, etc.
4 It can be easily imported into the database.

5 Importing data into Excel takes up more memory, whereas importing CSV files takes significantly less memory, and reading those CSV files in excel will be much faster.

A database program that keeps data which cannot be read by other applications then, in this case, that proprietary format can export into CSV format and save it as a CSV file, and later it can be easily imported into another spreadsheet program and database application.

Also Read: What is .dat file? When to Use dat file? How to open dat file?

When to Use CSV?

Here is the list of when to use the CSV file.

  • When your data can take tabular structure.
  • When you want to transfer small to medium scale data.
  • Transfer data from one application to another as long as both applications support CSV and can open CSV files.
  • CSV files can also be used for backup data.

Advantages of CSV File 

Here are some advantages of CSV File as compared to other files.

  • Text in CSV Files is human-readable.
  • CSV Files can be easily edited manually.
  • Parsing CSV Files is simple to implement.
  • CSV Files can be processed by almost all the applications.
  • CSV Files are easy to generate.
  • CSV Files are faster to handle.
  • CSV Files are smaller in size
  • CSV Files can be easily imported into most databases.
  • If the data volume is very large basic data and the scheme is more or less flat then CSV could be one of the best for transferring data as compared to XML and JSON.
  • CSV supports streaming processing, you do not have to keep a whole file in memory

Disadvantages of CSV File | Limitation of CSV File

Here are some disadvantages of CSV Files.

  • CSV files are unable to handle complex data. Only good for basic data only.
  • No separation of numeric data from text data.
  • Some databases do not support CSV files for data import.
  • CSV files are not fully standardized. There is a lack of universal standard rules for handling CSV data. Ways of handling data are different. The problem for solving points 4. 6. 7. and 8. of "How does CSV files look" are also different from person to person
  • No standard way to represent controlled characters.
  • CSV file will not be properly imported if the required field(mandatory field) is blank and if data has been quoted.
  • CSV files will not be properly imported if data in the CSV file contains an unsupported character.
  • CSV file does not have standard ways of representing binary data.
  • Everything in CSV is a string. It has no type.
  • The CSV file format has very limited support for special characters.
  • There is no standard predetermined way to represent control characters in CSV files.
  • The most popular CSV libraries are badly optimized, which means that CSV parsing is slower.

How to Create CSV File using Excel?

The steps are as shown below:

1 Open file you want to save in CSV format in excel.

2 Goto File >Save As > Select the desired location you want to save.

3. Give the name to the CSV file and under "save as type" select "CSV MS-DOS" and then click on OK

save as csv

There are various formats to save CSV files but ms excel provides 4 formats. They are :

  • CSV Macintosh
  • CSV Comma Delimited
  • CSV UTF-8 (Comma Delimited)

How to Open CSV File in Excel?

Opening the CSV file is very simple. Let's look at opening CSV files in various application

Opening CSV File in MS EXCEL

  1. First, make sure you have installed the MS Excel application
  2. Then right-click on the CSV file that you like to open. Then click on "OpenWith".Then click on Excel.

How to Open CSV File in Google Sheet?

Because Google Sheet is a spreadsheet app that deals with data, it is sometimes necessary to open a CSV file in Google Sheet.

Here are the steps:

1 First of all Open Google Sheets 
2 Click on 'File'
3 Next click on 'Import' 

4 Then Next Goto Upload Tab 
5 Then Click on 'Select a file from your device'

upload file
fig. Select Upload Tab and click on 'select a file from your device'

6 Now select your desired CSV File to open in Google Sheet 
7 In the next screen click on the 'Import data' button.

fig. click on 'Import data' button

8 Then click on the 'Open Now' link to open the CSV file.

open now to open csv file
fig. Click on the 'Open now' link to open the currently selected CSV File

CSV File vs Text File

  • Data in .txt files have no formatting for distinguishing each data.
  • Data in .csv files can be distinguished by a specific delimiter generally commas. But any other delimiter like a pipe, tab, or any single value character. Most of the applications can distinguish each data and treat them are tabular data.

CSV File vs xlsx File

CSV Files and XLSX Files are different. Differences between CSV File and XLSX File are as follows.

CSV File XLSX File
CSV stands for Comma Separated Values. It is a plain text file that contains a series of data separated by delimiters generally commas.
XLSX stands for Excel Microsoft Office Open XML Format Spreadsheet file. It is Microsoft Excel Spreadsheet used for storing, organizing, and analyzing the data.
CSV files only contain delimited data and we cannot use formulas on them. In XLSX files, we can store data as well as can perform formatting, use formula, and macro, and generates Pivot Table, and VBA code on it.
CSV files can be opened and edited by all text editors. XLSX cannot be edited or opened by text editors.
CSV generally consumes less memory than xlsx files. XLSX consumes more memory while importing data.
Data of CSV will only be text or number. Data of XLSX may contain text, numbers, charts, and graphs
Less Complicated than XLSX files. More Complicated than CSV files
In CSV no operations on data can be performed In XLSX operations on data can be performed.
Also Read: What is a TIFF file? How to Open TIFF File?


Why is CSV bigger than Excel?

Ans: Because XLSX files are compressed files. If there are many repeated data in excel then XLSX files are generally smaller than CSV files.

Is the CSV file editable?
Ans: You can open and edit CSV files in notepad, word, excel, libre, and any other text editor.
Do CSV files have row limits?
Ans: Actually CSV files do not have any row limits. If you open CSV files in excel then it won't hold more than 1 million data.
Are CSV files better than XLSX?
Ans: CSV files are less complicated and faster than Excel files. Also, CSV files consume less importing than Excel while importing data.
Does CSV remove all formatting?
Ans: CSV files only save text and value so they do not have any formatting like bold, italic, underline, fonts, etc.If you have formatted data in excel and while converting it into CSV files then all formatting gets removed.
 Can we use a formula in CSV data?
Ans: No. We cannot perform any formula and operations on data like we used to do in Excel.
Does CSV support multiple tabs or worksheets?
Ans: CSV doesn't have multiple tabs or worksheets as Excel does. One CSV file contains only one tab.
How do you make a CSV file readable?
Ans: CSV files are already readable and can be read by a variety of applications such as notepad, excel, MS Word, Google Sheets, and so on. Let's take a look at the reading difficulty by opening a csv file in these applications.
  • Notepad: Hard to read the data in a proper format
  • Google Sheet: Easy to read if you use text to column function
  • MSWord: Hard to read the data as data will not be transferred in column format.
  • MS Excel: Easy to read if use the text to column functions