Converting CSV to Excel: How to Import CSV Files into Excel Spreadsheets

Contents

How to Convert CSV to Excel
Converting CSV to Excel: Problems and Solutions
How to import multiple CSV files into Excel

In this article, you will find 2 easy ways to convert CSV file to Excel. In addition, you will learn how to import multiple CSV files into Excel and how to deal with situations where part of the data from a CSV file does not display correctly on an Excel sheet.

Recently, we started to study the features of the CSV (Comma-Separated Values) format and various ways to convert Excel file to CSV. Today we are going to do the reverse process – importing CSV into Excel.

This article will show you how to open CSV in Excel and how to import multiple CSV files at the same time. We will also identify possible pitfalls and offer the most effective solutions.

How to Convert CSV to Excel

If you need to pull some information into an Excel sheet from your company’s database, the first idea that comes to mind is to export the database to a CSV file and then import the CSV file into Excel.

There are 3 ways to convert CSV to Excel: You can open the file with extension . Csv directly in Excel, double-click the file in Windows Explorer, or import the CSV into Excel as an external data source. In the following, I will elaborate on these three methods and point out the advantages and disadvantages of each of them.

How to open a CSV file in Excel

Even if the CSV file was created in another program, you can always open it as an Excel workbook using the command Open (Open).

Note: Opening a CSV file in Excel does not change the file format. In other words, the CSV file will not be converted to an Excel file (.xls or .xlsx format), it will retain its original type (.csv or .txt).

Launch Microsoft Excel, tab Home (Home) click Open (Open).
A dialog box will appear Open (Opening a document), in the drop-down list in the lower right corner, select Text Files (Text files).
Find the CSV file in Windows Explorer and open it by double-clicking on it.

If you open a CSV file, Excel will open it immediately by inserting the data into a new Excel workbook. If you open a text file (.txt), Excel will launch the Text Import Wizard. Read more about this in Importing CSV into Excel.

Note: When Microsoft Excel opens a CSV file, it uses the default formatting settings to figure out exactly how to import each column of data.

If the data matches at least one of the following items, then use the Text Import Wizard:

The CSV file uses different delimiters;
The CSV file uses various date formats;
You are converting data that includes numbers with a leading zero, and you want to keep that zero;
You want to preview how the data from the CSV file will be imported into Excel;
You want more flexibility in your work.

To make Excel launch the Text Import Wizard, you can either change the file extension from . Csv on .txt (before opening the file), or import the CSV into Excel as described below.

How to open a CSV file using Windows Explorer

This is the fastest way to open CSV in Excel. In Windows Explorer, double click on the file . Csv, and it will open as a new Excel workbook.

However, this method will only work if Microsoft Excel is set as the default program for opening files. . Csv. If so, you will see the familiar icon in Windows Explorer next to the file name.

If Excel is not the default program, here’s how you can fix it:

Right click on any file . Csv in Windows Explorer and in the context menu that opens, click Open with (Open with) > Choose default program (Select program).
Выберите Excel in the list of recommended programs, make sure there is a checkmark for the option Always use the selected program to open this kind of file (Always use the selected application for this type of file) and click OK.

How to Import CSV to Excel

In this way you can import data from a file . Csv to an existing or new Excel sheet. Unlike the previous two methods, it does not just open CSV in Excel, but it converts the format . Csv в . Xlsx (if you are using Excel 2007, 2010 or 2013) or .xls (in versions of Excel 2003 and earlier).

Open the desired Excel sheet and click on the cell where you want to import data from the file . Csv or .txt.
On the Advanced tab Data (Data) in section Get External Data (Get external data) click From Text (From the text).
Find the file . Csvthat you want to import, select it and click Import (Import), or just double-click on the desired CSV file.
The Text Import Wizard will open, you just need to follow its steps.

Before we move on, please take a look at the screenshot below, it shows the original CSV file and the desired result in Excel. I hope this helps you better understand why we choose certain settings in the following example.

- Step 1. Select the data format and line number from which to start importing. Most often chosen Delimited (With separators) and from a string 1. The preview area at the bottom of the wizard’s dialog box shows the first few records of the imported CSV file.
- Step 2. In this step, you need to select the delimiters and line terminator. delimiter (Delimiter) is the character that separates the values in the CSV file. If your CSV file uses a symbol that is not in the proposed list, then check the box Other (Other) and enter the desired character. In our example, we have specified Tab (Tab character) and Comma (Comma) so that each product (they are tab-separated) starts on a new line, and product information, such as ID and sales data (they are comma-separated), are placed in different cells.Text qualifier (line terminator) is a character that encloses individual values. All text enclosed between such characters, such as “text1, text2”, will be imported as a single value, even if the text contains the character you specified as the delimiter. In this example, we have specified a comma as the delimiter and quotation marks as the line terminator. As a result, all numbers with a decimal separator (which is also a comma in our case!) will be imported into one cell, as can be seen in the preview area in the figure below. If we do not specify quotes as a string terminator, then all numbers will be imported into different cells.
- Step 3. Look into the area Data preview (Sample data parsing). If you are happy with how your data looks, then click the button Finish (Ready).

Tip: If your CSV file uses more than one consecutive comma or other delimiter character in sequence, check the box Treat consecutive delimiters as one (Count consecutive delimiters as one) to avoid empty cells.

Choose whether to paste the imported data into an existing sheet or a new sheet, and click OKto finish importing the CSV file into Excel.

Tip: You can press the button Properties (Properties) to configure advanced settings such as updating, layout, and formatting for imported data.

Note: If your CSV file contains numbers or dates, Excel may not convert them correctly. To change the format of the imported data, select the column(s) with errors, right-click on them and select from the context menu Format cells (cell format).

Converting CSV to Excel: Problems and Solutions

The CSV format has been in use for over 30 years, but despite its long history, it has never been officially documented. The name CSV (Comma-Separated Values) comes from the use of commas to separate data fields. But that’s in theory. In fact, many so-called CSV files use other characters to separate data, for example:

Tabs – TSV files (tab-separated values)
Semicolon – SCSV files (semicolon separated values)

Some variations of CSV files separate data fields with single or double quotes, others require a Unicode Byte Sequence Marker (BOM), such as UTF-8, to interpret Unicode correctly.

This lack of standards creates a variety of problems that you may encounter when trying to convert excel file to csv, and especially when importing a CSV file into Excel. Let’s take a look at the known issues, starting with the most common.

CSV file does not display correctly in Excel

Symptoms: You are trying to open a CSV file in Excel and all the data ends up in the first column.

Reason: The root of the problem is that your Windows regional and language settings and your CSV file have different delimiters. In North America and some other countries, the default list separator is a comma. While in European countries comma is used as decimal place separator and list field separator is semicolon.

Decision: There are several possible solutions to this problem. You can quickly look through the recommendations below and choose the most suitable for your particular task.

Specify the correct delimiter directly in the CSV file. Open the CSV file in any text editor (even regular notepad will do) and paste the following text on the first line. Note that this must be a separate line before any other data:
- To set the comma separator: sep
- To set the separator to a semicolon: sep=;
As you may have guessed, in this way you can set any other character as a separator by simply specifying it immediately after the equal sign.
Select the desired delimiter in Excel. In Excel 2013 or 2010 on the tab Data (Data) in section Data Tools (Work with data) click Text To Columns (Text by columns).When the Column Text Wizard starts, in the first step, select the data format Delimited (with separators) and press Next (Further). In the second step, select the desired delimiter and click Finish (Ready).
Change the extension from . Csv on .txt. Opening a file .txt in Excel will launch the Text Import Wizard, and you will be able to select the desired delimiter, as described in the section How to import CSV into Excel.
Open a CSV file with a semicolon as a separator using VBA. Here is a sample VBA code that opens a CSV file in Excel that uses a semicolon as the separator. The code was written a few years ago for earlier versions of Excel (2000 and 2003), but if you’re fairly familiar with VBA, you should have no problem updating or changing it to work with comma delimited CSV files.

Note: All solutions shown only change the delimiter for the given CSV file. If you want to change the default separator once and for all, then the following solution will suit you.

We change separators in the settings of regional standards. Нажмите кнопку Home (start) and run control Panel (Control Panel), click item Region and Language (Regional standards) > Additional Settings (Extra options). A dialog box will open Customize Format (Format setting) in which you need to select a dot (.) for the parameter Decimal symbol (Integer/Decimal Separator), and set a comma (,) for the parameter List separator (List element separator).

Translator’s Note: These settings are given for the English localization of Excel (and a number of other countries). For localization, it will be more common to use a comma as a decimal separator and a semicolon to separate list items.

Double tap OKto close the dialog boxes – you’re done! From now on, Microsoft Excel will open and display all CSV (comma delimited) files correctly.

Note: Setting the Windows Control Panel to decimal separators and list items will change the default character settings for all programs on your computer, not just Microsoft Excel.

Leading zeros are lost when opening CSV file in Excel

Symptoms: Your CSV file contains values with leading zeros and those zeros are lost when opening the CSV file in Excel.

Reason: By default, Microsoft Excel displays the CSV file in the format General (Common), in which leading zeros are truncated.

Decision: Instead of opening the .csv file in Excel, run the Text Import Wizard as we did earlier to convert the CSV file to Excel.

In step 3 of the wizard, select the columns containing values with leading zeros and change the format of these columns to text. This is how you convert your CSV file to Excel, keeping the zeros in place.

Excel converts some values to dates when opening CSV file

Symptoms: Some of the values in your CSV file look like dates, and Excel will automatically convert such values from text format to date format.

Reason: As mentioned above, Excel opens the CSV file in the format General (General), which converts date-like values from text format to date format. For example, if you open a CSV file containing user logins, the entry “Apr23” will be converted to a date.

Decision: Convert the CSV file to Excel using the Text Import Wizard. In step 3 of the wizard, select columns with records that look like dates and change the column format to text.

If you need to achieve the opposite result, that is, in a certain column, convert values to dates, then set the format Date (Date) and select the appropriate date format from the drop-down list.

How to import multiple CSV files into Excel

I think you know that Microsoft Excel allows you to open multiple CSV files using the command Open (Open).

On the Advanced tab Fillet (File) click Open (Open) and from the drop-down list at the bottom right of the dialog box, select Text Files (Text files).
To select multiple files in a row, click on the first file, then press and hold Shift, click on the last file. Both of these files, as well as everything in between, will be selected. To select files that are not in a row, hold down the key Ctrl and click on each file . Csvyou want to open.
When all the desired CSV files are selected, click the button Open (Open).

This method is simple and fast, and we could call it excellent, if not for one circumstance – each CSV file opens in this way as a separate Excel workbook. In practice, switching back and forth between multiple open Excel files can be extremely inconvenient and burdensome.

I hope now you can easily convert any CSV file to Excel. If you have any questions or difficulties, feel free to write to me in the comments. And thank you for the patience of everyone who mastered reading this long article to the very end! 🙂