What Is A CSV File And How To Use It Efficiently?

If you work with Microsoft Excel or Google Sheets, you will eventually come across a CSV file. These are plain text files which can be opened in nearly any spreadsheet application and are therefore commonly used to resolve compatibility issues between programs.

Think about when you post your resume to a job board. You typically save it as a PDF, right? Why is that? Because a PDF can be read on any computer, while say a Word document will likely not open if your prospective employer doesn't have Microsoft installed.

CSV files are sort of similar. If a spreadsheet is saved as .csv, it can be opened in a wide variety of applications. While you'll lose some formatting, the basic information remains intact. Below, we'll offer a brief introductory guide to CSV files and how they work.

What Are CSV Files?

CSV file stands for “comma-separated values,” which is a somewhat self-explanatory term. In CSV files, information is typically separated using commas, creating a simple text file that many external applications – like Excel or Google Sheets – can open, interpret, and then display.

Why do we need CSV files? They help facilitate communication between programs that cannot necessarily communicate with each other directly. You can save an Excel spreadsheet in CSV format, for example, and then open it in Google Sheets or Outlook without any problems.

How Does A CSV File Work?

Most common spreadsheet applications allow you to open spreadsheets as CSV files (Business Insider provides an in-depth tutorial for doing so in Excel, Google Sheets, and text editor here). Once you open your spreadsheet as a CSV file, you'll see a plain text version, which can help you take a close look at how CSV formatting works.

Information is almost always separated using commas (you may use different separators, which we'll touch on more below). When you open a CSV file, you might see something like:

Author, Book

George Orwell, 1984

Louisa May Alcott, Little Women

When you open this file in, say, Excel, you'd get something like this:

Saving CSV Files

Most applications allow you to save spreadsheets as CSV files, which is often necessary if you will be importing spreadsheets into different applications.

For example, say you're sending a colleague a spreadsheet you made in Excel and you know they work primarily in Google Sheets. A spreadsheet saved as “.xlsx” may not open properly. To prevent issues, you could save your spreadsheet as .csv. This will convert your spreadsheet into a plain text file that can be read and then interpreted by Google Sheets. In most cases, saving a spreadsheet as a CSV is as simple as hitting “File → Save As and then selecting “Save As CSV.”

Keep in mind that while a CSV file will maintain all the basic information you inputted, it will not maintain your formatting. In other words, if you tinkered with something like column length or font color in Google Sheets, that change will not be maintained when you save your spreadsheet as a CSV file.

Dealing With More Complex Formatting

Commas and quotations within an entry can sometimes complicate CSV files. If you're ever editing or altering a spreadsheet in plain text, there are some special considerations to keep in mind to avoid errors.

If any entries in your spreadsheet include commas, this can get tricky. For example, say you're making a spreadsheet of opening lines of books. In a CSV file, you may write:

Book, Opening Line

1984, It was a bright cold day in April, and the clocks were striking thirteen.

However, remember commas are separators in CSV files. Your spreadsheet would end up looking like this:

You have inadvertently created an unnecessary third column. The solution is adding quotation marks to signify a given sentence – even if it contains a comma – belongs in the same column:

Book, Opening Line

1984, “It was a bright cold day in April, and the clocks were striking thirteen.”

With this formatting, you'd end up with:

But what happens if there are both quotation marks and commas in an entry? Going to the above example, say you're trying to input the opening line to Little Women – “Christmas won't be Christmas without any presents,” grumbled Jo, lying on the rug.

While formatting this may seem daunting, it's actually not that complicated. You have to place the entire sentence in quotation marks, which instructs your application not to split the data between columns. Then, within those quotation marks, add the actual quotations included to keep the sentence grammatically correct:

Book, Opening Line

Little Women, ““Christmas won't be Christmas without any presents,” grumbled Jo, lying on the rug.”

With this formatting, you'd end up with:

Using Different Separators

While CSV files most commonly use commas to separate values, this is not always the case. With some applications, you can choose from a set of common separators or specify the separator on your own. Once you choose a separator, it must stay the same for the entire file.

There are a variety of reasons you might use different separators. In the above example, you're likely going to be using commas within entries a lot if you're inputting lines from literature. When you're also relying on commas as separators, this leads to a lot of extra formatting that could be avoided by simply choosing something like a semi-colon or carat instead.

If you do decide to use different characters to separate values, you need to make sure you specify this at the beginning of the file. Otherwise, it will not show up correctly when you open your CSV in another application. You did this by starting an entry with “sep=” and then putting in your choice of separator. For example, say you want to use a semicolon as your separator. You would write:

sep=;

Author; Book

George Orwell; 1984

Louisa May Alcott; Little Women

This would get you the exact same table we made in our initial example:

CSV Files: The Bottom Line

CSV files can save you a big headache when sharing spreadsheets between different applications and are fairly simple to understand with a bit of practice. While the above tutorial is only a beginner's guide, it should give you the basic tools you need to start understanding and using CSV files.

Looking for solutions for your company? Xano is the fastest No Code Backend development platform on the market. We give you a scalable server, a flexible database, and a No code API builder that can transform, filter, and integrate with data from anywhere. Sign up here to get started.

The post What Is A CSV File And How To Use It Efficiently? appeared first on Xano.