CSV file - How to open?, Why it is used?, Drawbacks, Vs Delimited Flat file

CSV File Format

What is a CSV file?

CSV file is a plain text file which uses commas as a column or field delimiter. In 1972, the IBM Fortran first supported CSV files.

What is a CSV file used for?

CSV files are mainly used to transfer (import/export) large quantities of tabular data between different applications easily.

To transfer data between databases, quite often CSV to SQL.

As a CSV file is a plain text file, they are compatible with all the platforms avoiding incompatibilities such as word size or byte-order.

As CSV files are plain text files, they are easy for the website developers to create.

The CSV files are largely human-readable.

How do I open a CSV file?

A CSV file can be opened by the below programs,

1. Microsoft Excel

• Just double click the CSV file, it will be opened in Microsoft Excel, if installed in the computer.
• If it asks to select the program in which you want to open, by “Open with” prompt, select Microsoft Excel.
• The opened file’s data should be in the required separate columns. If the data in the file is misaligned, follow the below steps,

1. First, open Microsoft Excel (I’m using MS Office 2013) and then click “DATA” tab as shown in the figure
   
2. Then, select “From Text” as shown in the below figure.  An “Import text file” dialog box will open.
   
3. Browse and double-click the required text file and click “import”. Make sure “Delimited” is selected in the opened text import wizard and Tick mark “My data has headers” if your data has headers, otherwise don’t select it. Click Next,
   
   
    
   
   
   
4. Tick mark, “Comma” as delimiter and click next
5. Select the Colum data format. By default, the format is “General”. If the dates are not showing properly in a column, select the “Date” format. After that, click “Finish”.

• Open Office Calc: 

         If Microsoft Excel is not installed, Use open office calc, an open source spreadsheet program.

• Numbers (in Apple)
• Google Docs (Online)
• Programming Languages: 

        Any programming language that has input/output and string processing functionality can read and write CSV files.

• All text editors like notepad (but shown without delimiter columns).

• Almost all other spreadsheet programs.

What is meant by a delimiter in a CSV file?

A delimiter is a sequence of one or more characters used to mark the boundary between individual regions or fields in a plain text file or other data streamed files.

Example: Comma is a delimiter in CSV files but other delimiters like space, tab etc. are also used as delimiters.

What is meant by Text Qualifier in a CSV file?

A Text Qualifier in CSV files which indicates the individual field values in the file. Text qualifier of a particular CSV file can be known by opening it in notepad or any other text editor programs.  In the below figure, you can see “Internal double Quotes” is used as Text Qualifier.

How to create a CSV file?

A CSV file can be created using Microsoft Excel, Open office calc, Google Docs and any text editor like Notepad.

Delimited Flat file vs CSV file

• A CSV file is also a type of flat file. Actually, there are two types of flat file, one is CSV and the other is delimited flat files.
• The only difference between a CSV file and Delimited flat file is a CSV file contains delimiter and an optional text qualifier.But, the Delimited flat file has only delimiter and text qualifier or enclosing character is not used.

Limitations or drawbacks of CSV

• Multiple sheets are not supported by CSV file format
• CSV cannot naturally represent hierarchical or object-oriented database data.
• CSV format lacks to specify information data type. For example, there is no way to distinguish the string "1" from the number "1".
• CSV works best only for tabular data, not nested data.