The Ins and Outs of CSV Files: Open, Read, Convert and Merge

JPG, PDF, XLS, GIF, DOC, PPT, EXE, WAV, PNG …

The canon of file formats grows larger every day. Staying familiar with the most popular file formats and what makes them useful can be intimidating, but mastering the ins and outs of CSV files is a useful skill for any businessperson.

Every file format has unique strengths and weaknesses ranging from compatibility, integrity, efficiency, or portability, but if you spend time around popular spreadsheet editors like Excel or Google Drive, you need to know what a CSV file is and how to use it.

This guide is your crash course on understanding and using CSV files—how to open, read, convert and merge this popular file format so that you can make the most of your data.

Key Takeaways:

A CSV is a generic format that stores spreadsheets as delimited text files.
You can read and edit CSVs in text editors or convert them to spreadsheets.
FileCenter has various tools to help you make the most of your CSVs and other documents.

Introducing the CSV

In the 2016 film Arrival, aliens arrive with a message for earth. The only issue is that nobody knows what it means. With no way to communicate, Louise Banks (a linguist played by Amy Adams) works with the aliens to create a shared vocabulary that they can both understand.

While competing software may have similar user interfaces, the code behind the scenes can be as different from each other as English is from an alien language. A CSV file is the “shared vocabulary” that allows different spreadsheet programs to communicate with each other.

CSV stands for “Comma-Separated Values,” which is partially misleading because CSV files can be separated by a variety of characters like semicolons, dashes, and vertical bars, as well as commas. Most spreadsheet editors can export a sheet as a CSV by converting the spreadsheet to a delimited text field where commas or other characters separate cells. Once someone saves the spreadsheet as a CSV, users can open it as a text file to view the delimited list or import it to most spreadsheet programs to restore the CSV to a spreadsheet.

CSV files (right) represent spreadsheets (left) as delimited text separated by commas or another character — Image Source: https://qph.cf2.quoracdn.net/main-qimg-fcf7924c63cc8d085cbe6428a601c3d8

Opening CSV Files

There are several ways that you can open a CSV file. Since CSV files are nearly universal, most spreadsheet programs can open them, but they convert them to their spreadsheet format. If you want to open a CSV file without converting it to a spreadsheet, right-click the file, navigate to Open With, and select a text editor like Notepad.

To open a CSV file without converting it to a spreadsheet, open it in a text editor like Notepad

Reading CSV Files

When you open a CSV in Notepad, it is a delimited text file, which means columns are separated by commas (or another delimiter) and rows are separated by returns. To locate a specific cell, simply count the number of lines and commas to locate the cell in question.

While less user-friendly than a spreadsheet, the fundamental principle is the same. You can edit text, add cells, or even find and replace to change the delimiting character.

Many spreadsheets contain large numbers that already use commas to separate decimal places. Since you want those commas to stay in the correct cell, it is a problem for a “comma-separated value” file, but there are two easy solutions:

Use a different delimiting character such as “|” or “;.” This will treat commas like any other alphanumeric character rather than as a delimiter.
Use quotation marks to bookend the cell values that contain a comma. For example, the number 1,000 would be written with the “1” in column A and the “000” in column B (separated by the comma), but the number “1,000” would be written as “1,000” in column A. The quotation marks preserve the comma as text rather than treating it as a delimiter.

Delimited text files are separated by a special character for columns and by return for rows

Converting CSV Files

While the delimited text file is handy for getting a quick preview or making small changes to a file, CSV files are most useful as a go-between format for spreadsheet editors. It’s useful to convert CSV files to other formats to leverage programs like Excel or Google Sheets to get the most value out of your spreadsheet.

When you open a CSV file in Excel, you can tell the program how to interpret the delimited text file. At the initial opening of a CSV file, the Text Import Wizard will pop up. The first step is to let Excel know it is opening a delimited text file by selecting the Delimited radio button.

Select Delimited to let Excel know your CSV is a delimited text file

Before clicking Next, you can also let Excel know if your data has headers or if you’d like to skip any rows at the beginning of the document.

Select the delimiting character you used in your CSV file on the next page. For this example, commas are the delimiters, but you can specify a custom character in the Other option if necessary.

Excel gives you a data preview so you can see how the program reads your CSV file if you’re unsure about its format. Note that in the example, “1,439.58” is in the second column even though it contains a comma. That’s because the delimited text file used quotation marks to inform Excel that the comma in that specific cell was not a delimiter.

Press Next for more customization options, or click Finish if you’re satisfied with your data preview once you’ve selected your delimiter.

Select your delimiter and click Next or Finish to convert your CSV file

Merging CSV Files

CSV files are useful because nearly every spreadsheet editor can import and export them, but they also have their drawbacks: CSV files don’t preserve the formatting or formulas of an Excel Workbook or Google Sheet, and they only store one sheet (or “page”) of data. The delimited spreadsheet uses returns and a special character to separate rows and columns but does not have a character to separate pages.

The only way to export and import CSV files with multiple sheets is to save each sheet as a separate CSV and merge them as you import them into your spreadsheet editor.

One easy way to merge CSV files is to import multiple CSVs into the same Excel workbook. Navigate to the Data toolbar, select Get Data, select From File, and then choose the From Text/CSV option to create a new sheet for each page. These actions will open the same Text Import Wizard you used to convert a single-sheet CSV. Repeat this process for each sheet you’d like to merge, and then you can save the Excel workbook as one document.

Merging multiple CSVs uses the same Import Text Wizard as converting documents

You have several simple options to merge multiple CSVs into a single sheet. First, you can copy and paste multiple CSVs into a single document in a text editor such as Notepad or copy and paste multiple sheets into a single Excel sheet before re-exporting as a CSV file.

If you want to merge CSVs without using a text or spreadsheet editor, you can merge files in the Windows Command prompt or use various free online tools.

Managing Documents with FileCenter

CSVs are just one color in the palette of document management. CSV files and other documents must be accessible, secure, sortable, and actionable to be useful to you.

FileCenter can help you make the most of your documents with cutting-edge document management tools like converting PDFs to Excel spreadsheets (and back again!), securely sharing CSVs and other files via encrypted portals, and leveraging metadata to keep your documents organized and indexed.

To learn how FileCenter can change the way you approach document management, download a free trial or request a demo today.