What is Comma Separated Values File (CSV File)?

In Excel sheets, all fields which have a comma, are embedded inside double quotes which are commonly known as text qualifiers i.e one single cell with green, blue, and yellow, will be captured as “green, blue, and, yellow”.

What is a CSV File?

It's as simple as it sounds. After all, you don't need to be a tech-savvy to comprehend how CSV files work.

Typically, a file which has CSV file extension is used where there's a chunk of data to be sent to another application.

The file extension, on the other hand, helps an operating system identify which program, in particular, the file is linked to.

Here's an illustration:

If for instance, I transfer a file with the name ‘minutes.doc', the extension which ends with ‘.doc' means that the file should be opened with Microsoft Word. Almost always, a CSV file is opened by programs such as Microsoft Excel, Google docs, and OpenOffice Calc.

Suppose you already have Microsoft Excel installed in your computer, the CSV file should automatically open in Excel once clicked by the user.

Sometimes, the computer gives you options with an ‘Open with' widget to choose your most preferred application.

Aside from that, if you want to access the CSV file directly, you need to click File, then Open and choose the CSV file from that point.

To save your spreadsheet as a CSV,

1. Open your file using the spreadsheet application
2. Click File, then choose Save As
3. Choose the location where you want to save your file. Below, you'll see a Save as type option, choose CSV (Comma delimited), and finally, click Save button.
4. The application may show a message stating that some of the features in your workbook might be lost if you save it as CSV. That is to say, any form of formatting such a bold text will not be saved. Just click Yes to continue.

While creating a CSV file, it's standard practice to separate the data fields with a delimiter, which is always in the form of a comma.

Keep in mind, it should be one character or else, your data won't make any logical sense. What follows, is to make sure that your header list is delimited in the same manner as the entire file.

And why is this integral in the entire creation of a CSV file? It helps you get accurate results when you key in a query.

How to open a CSV file

Predominantly, spreadsheet programs are widely used to open CSV files. It's pretty much easier to read them here.

A spreadsheet program sorts the data in a CSV file systematically via columns. This helps to filter all the contents in the file.

Not forgetting that a user can make the most out of free spreadsheet tools such as Kingsoft Spreadsheets, or the very common OpenOffice Calc.

If you have OpenOffice Calc already installed in your computer, a CSV file should open automatically when you double click it.

As another option, your computer will show an Open with prompt, from where you can choose the program to open your CSV file with.

Alternatively, you can use online spreadsheet programs such as Google Sheets or Zoho Office which have groundbreaking features on board.

These tools allow more than one user to gain access to a single file in real-time. What's more fascinating about Google Sheets, to be precise, is that it lets you convert your Excel files into sheets and vice versa.

You may want to use Google Drive to open your CSV file. The process is pretty much simple. Just follow these easy steps;

1. Open Google Drive and login into your Google account.
2. Find the folder which has the file you want to add to Google Drive
3. Drag and Drop the file from its original location to ‘My Drive' section
4. Your browser will give you a confirmation prompt that your file has been properly added to Google Drive
5. You can now double-click the file to open it once the upload is complete.

A text editor is also used to open a CSV file. Generally, Mac operating systems come with a program called TextEdit while Windows uses Notepad as its pre-installed application.

Unfortunately, such programs don't have the capacity to handle a file with huge amounts of data. To resolve this impediment, there's the prevalence of notable third-party applications which come in handy whenever a user wants to deal with complex CSV files.

So why are text editors commonly used to handle a CSV file?

From this point, you can create a file from square one, by use of a programming language such a CSS or HTML. Also, the user gets to edit files which end with extensions like, TXT, XML, CSV, or PHP, just to name a few.

Converting the CSV file

This step is so imperative if you're going to interact with CSV files every now and then. Any CSV file can be converted into a Microsoft Excel format in just a click of a button.

A user needs to just click File, and go to Save as button. Here, there are formats available which include, XLS, TXT, XML, SQL, among many others. It's worth noting that Google docs also helps with the file conversion.

If your computer doesnt have a program for file conversion, you can jump right in your Browser and make good use of free file converter software.

You can do it online without having to go through the tedious process of downloading the program.

Other than the ordinary CSV(Comma delimited), there are other formats which come into play;

1. CSV(Macintosh)– It's used to save your Excel worksheet as a comma-separated file for computers which use the Mac operating system.
2. Unicode Text(*txt)– It's a universal format which is used by most operating systems including Linux, Windows, and Macintosh. The best part about this format is that it reads all recent languages.

So how do I export my Excel file into a CSV?

Practically speaking, data is exported from an Excel file to a CSV either with UTF-8, or UTF-16 encodings. So let me explain the differences.

With the UTF-8, the encoding is more compressed since it uses anything between 1 to 4 bytes for each symbol. This type of encoding is compatible with all character formats based on the ASCII (American Standard Code for Information Interchange).

UTF-16 stores one symbol in 2 to 4 bytes. It's used when your data has Asian characters like Japanese and Chinese.

A CSV file is remarkably used in so many fields. If we put the e-commerce industry into the equation, there's actually so much we can relate to.

You can import and export files to update your inventory and product information. Adding new or existing products is quite feasible.

An online store owner can use this strategy to transfer data of new customers and do seamless stock management. You can use the files to import tracking numbers for all products which are on transit to the buyers.

CSV Structuring

All structures used in a CSV file are easy to create and require less effort since they all follow a similar format.

If you wish to do it manually, then you can use a text editor such as Notepad++ which supports a couple of dominant programming languages.

Alternatively, using a spreadsheet program can serve a user's needs better since it has cells where data is sorted in rows and columns.

How to Create a CSV File

So, how would you create a CSV file? Well, first you need to make sure that you have the right program. A CSV is a simple file format intended to store table-based data, like the stuff you get from a database or spreadsheet. You can import and export files in CSV to a variety of environments.

Interestingly, although you’re importing table data with a CSV file, this is a text file type. In other words, you can create your CSV file using any editor capable of changing text.

Most commonly, a CSV file is created by exporting a file and clicking on the File > Export buttons. If you’ve created a spreadsheet in a program, then you can simply export the content of that file into CSV format when using the program.

The easiest way to create an CSV file is to start with your spreadsheet in something like Microsoft Excel. When you’re done editing your content, instead of saving it as a plain text file, you simply save the file as a CSV, which means you can separate each newline with a comma.

Make sure that there are no additional commas included in your spreadsheet content before you save to CSV, as this can lead to mistakes in the data.

You may find that it’s easier to export and import CSV files if you avoid using large amounts of punctuation like quotation marks, semicolon, and other content in your tabular data too.

Within your Excel program, click on the file menu, and choose the option for Save as. You should see the option to “Save as type”, where you can choose the CSV file format as one of the options.

Click on the character separated values option and enter the file name you want to save your content as. Click “yes” when you’re prompted to confirm that you want to use this file type, and finish by clicking “Save”.

It’s also possible to download various files from the internet in CSV format. For instance, you can download lists of email contacts, or invoice data in CSV text format so that it’s easier to upload your spreadsheet file to another service.

CSV vs Excel (.xls) – What's the Difference?

The most common method for saving CSV files involves choosing the specific file type in Microsoft Excel, or a similar spreadsheet service.

However, if you right-click on the file name of a standard Excel file, then you’ll notice it’s typically saved in another format: .XLS.

The difference between CSV and XLS is quite simple. The CSV format is a plain text file type, where you separate values by commas.

However, XLS is a quite different kind of solution, known as “binary file format”. This holds information about all the worksheets in your file, including the formatting and content options.

CSV files are available to open in any spreadsheet program, including Google Sheets, Open Office, and Microsoft Excel. You can open the CSV file in a simple text editor, and it’s compatible with most spreadsheet platforms.

There are some disadvantages to the simplicity of the CSV, however. For instance, you can only store a single sheet in a file without any formulas or formatting.

XLS files are more common in the Excel workbook files if you have the version between 97-2003, the later versions are more likely to be in XLSX format instead.

The XLS extension is specifically designed for Microsoft Excel, but that doesn’t mean you can’t use it in other environments too.

You can open XLS files with the Excel viewer, which makes it easier to print the information from your values file. Microsoft Excel can also read a CSV file template, but there may be some issues with the separator depending on your computer.

One quick solution, rather than finding complex tutorial guidance online is to change the filename extension from CSV to TXT when you need to access the data in Excel.

When you open the .txt file in Excel, you can manually specify the encoding, delimiters, formatting of columns, and so on.

Excel files tend to be the better choice if you’re storing more complicated data, while CSV files are supported by virtually every data upload interface.

If you’re planning on moving data between platforms, then you may be better off using the CSV format.