CSV Files in Excel

CSV Files in Excel

CSV stands for Comma Separated Values, which means that in this type of file in excel, each text field is separated by commas. They are simple to create and read, and they help move data between applications. You can recognize a CSV file through the .csv extension or when you open a file, and commas separate the data.

For instance, we have saved a file containing the details of 20 companies separated by commas called companies.csv. To open it in Excel, go to File – Open – Text Files ( in the file type dropdown list in the Open dialog box). Then, choose the file “companies.csv.” Excel automatically opens it and displays the data in a new sheet where each data separated by commas is displayed neatly in columns.

Explanation – CSV Files

The list of data in CSV files is separated by commas and are plain text files. They are often used to exchange data between different applications. They can be opened in text editors and programs like Excel. A simple CSV file may look like this:

There are many built-in options to open a CSV file into Excel. These include:

• Directly opening it in Excel.
• Right-click the CSV file and choose “Open with” > Excel
• Double-click and open with Windows Explorer
• Importing CSV files into Excel

How to Open, Import, and Convert CSV Files in Excel?

Let us take an example of a simple file called student.csv. It contains details of some students, as shown below. Commas are the delimiters used in this file.

Step 1: Let us open this file in Excel. Go to Windows Explorer and locate the file.

Now, right-click on the file, and choose “Open With” > Excel.

Step 2: The file now opens in Excel format with the data listed in different columns.

Step 3:

• To save this file in the Excel format, click on File – Save As.
• Enter the file name and choose the option “Excel workbook (*.xlsx).
• Click on the Save button.

Step 4: You can import the file student.csv details into Excel in the following path:

Data tab – Get Data – From File – From Text/CSV.

Step 5: Choose your file and click

Step 6: Click on “Load” in this window.

Step 7: You get the data imported into your Excel worksheet.

Examples

Let us look at examples to learn more about opening CSV files into Excel, importing them, and saving them in Excel format.

Example #1 – Open CSV File in WorkSheet

CSV files in Excel comprise many rows of data with one or more fields. It is a simple form of storing data and can be imported across applications. You can read multiple CSV files easily. Let us look at how to open a CSV file in Excel. For example, a CSV file (residents.csv) contains the list of people who live in an apartment on a PC.

Step 1: To open it in Excel, go to file and click “Open.”

Step 2: Double-click “My PC” from the options available.

Step 3: Then, we need to go to the location of your CSV file. Select the “Text Files” option on the right of the “File name” field in the dialog box.

The data in the file is directly displayed in a new workbook. Each field of data in a row is displayed in a separate column.

Step 4: To make it user-friendly in a proper Excel format, format the headers and adjust column lengths.

You can save it and use it in Excel for your requirements. You can load multiple CSV files into Excel this way. It’s that simple!

Excel uses the current default data format settings when it opens a .csv file and interprets how to import each data column. You can use the Import Text Wizard in Excel to format the different columns in a CSV file into Excel. For example, the date should be in another format than the one in the CSV file.

Example #2 – Using Windows Explorer

Let us look at another example of how to open the same CSV file in Excel via Windows Explorer.

Step 1: Go to the directory where you have the CSV file using the File/Windows Explorer.

Step 2: Right-click on the CSV file and choose “Open with” > Excel.

Step 3: Now, the CSV file into Excel is opened, and data is displayed, as shown below.

Step 4: If you want to save this as an Excel file, click “File” in Excel’s top-left corner. In the sidebar, click “Save As.”

Step 5: Double-click on this PC.

Step 6: In the dialog box that pops up, choose the file destination, enter a file name, and in “Save as Type,” choose “Excel Workbook” from the available options.

Step 7: Press Save. Your file is saved as an Excel file in the specified destination.

Example #3 – Import CSV File Through External Data

Besides opening an entire file in Excel, you can also choose to import the data in a CSV file into the current worksheet. Let us look at how this is achieved with CSV Files in Excel. Importing also helps you consolidate CSV files.

Step 1: First, open a blank Excel worksheet where you want to import the data.

Step 2: Go to the Data tab and click on the option “From Text/CSV” in the “Get and Transform Data” group.

Step 3: In the dialog box that opens, browse to the destination of the CSV or txt file. Now, select the .csv file and press Import.

Step 4: You get the “Example CSV File wizard” containing the details of the file residents.csv.

Step 5: Notice that the delimiter has already been set to “Comma.” You can also change it based on the type of delimiter used in your file.

Step 6: Click “Load To” at the bottom of the dialog box.

Step 7: We get the “Import data” dialog box. Here, we choose the existing worksheet and enter the cell address for the data.

The data will be imported into the current worksheet, as shown below. You can also choose how to view your data or select “New Worksheet” in the dialog box.

Notes:

Data with commas

What if the data you want to import contains commas? For instance, consider a file with the data as shown below:

The description contains commas as well. After importing the data to Excel, you can observe how the commas have affected the fields.

We need only two columns but because of the commas in the data, you get four columns. To resolve this, wrap the data in quotation marks whenever you have a comma.

You get the data as desired.

How to Use CSV Files in Excel?

• CSV files are used extensively in business. They help companies export large amounts of data with ease.
• CSV files are easy to create and can be easily read by opening them in Excel.
• CSV files are easy to read and write. Hence, these files can be manipulated in different ways.
• CSV files into Excel are compact and easy to import.
• You can easily store CSV files in Excel in the .xls or .xlsx format.
• CSV files can be easily transferred to and from Excel using the Export/Import feature.

Important Things to Remember

• You can efficiently work with CSV files. In Excel, you can open them like any other .xls file and transfer the data to different cells.
• CSV files in Excel can have different types of separators, not just commas. However, multiple separators are generally not allowed.
• They can be used to communicate data between applications.
• You can easily save the CSV file as an XLS file. But then, there may be problems with data formatting between the two.

Frequently Asked Questions

Recommended Articles

Guide to CSV Files in Excel. Here we explain how to create, open, import, and convert CSV files into Excel format with step-by-step examples. You may learn more from the following articles –