Why Excel is not opening CSV files correctly?

You have come to the right place if you are looking for the answer or some unique tips to fix the issue that the CSV file is not opening correctly in Excel. There are several ways to open a CSV file in Excel correctly. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.


4 Cases of CSV File Not Opening Correctly in Excel and Their Solutions

In this section, I will show you some quick and easy methods to solve the issue that the CSV file is not opening correctly in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

Case 1: CSV File Is Opening in One Column

When we open a CSV file in Excel, that file may not be distributed to the columns. This happens when the delimiter is not properly set up. In this section, we will see probable solutions to the issue: Excel is opening CSV files in one column.

  • Now, we have opened the CSV file in Notepad, which shows the data are comma delimited.

Why Excel is not opening CSV files correctly?

  • However, if we open it in Excel, the data will be in column A

Why Excel is not opening CSV files correctly?

Now, let us demonstrate the possible solutions to this problem.


Solution 1: Change Regional Settings

In the first solution, we will change the regional settings from the Control Panel. Mainly, we will change the list separator from semicolon (;) to comma (,). We will be using Windows 10 to demonstrate this process. However, it should be similar to other versions of Windows.

📌 Steps:

  • Firstly, open the Control Panel.
  • Secondly, click on Clock and Region. Make sure, view by category is selected. Otherwise, you will need to select Region.
  • So, a new window will appear.
  • Thirdly, select Change data, time or number formats under the Region section

Why Excel is not opening CSV files correctly?

  • Then, the Region window will pop up.
  • After that, select “Additional settings…”.

Why Excel is not opening CSV files correctly?

  • So, the Customize Format window will appear.
  • Then, type comma (,) in the List separator Our CSV file is comma delimited, if yours is different, then use it accordingly.
  • After that, press OK.

Why Excel is not opening CSV files correctly?

  • Then, if we open that CSV file, it will be on multiple columns.

Solution 2: Specify Delimiter by Editing the CSV File

If Excel can recognize the data separators, it can show them in multiple columns. In order to identify the delimiter in Excel, we will insert a single line in this solution. The syntax is “sep=delimiter”. We have used a tab in our file, so we will need to add a tab in place of the delimiter.

📌 Steps:

  • To begin with, we can see the data file is semicolon-delimited.

Why Excel is not opening CSV files correctly?

  • After that, open that file in a text editor. We have opened it in Notepad.
  • Then, insert the following lines at the top. If the delimiter were a comma, then we would have written “sep=,”. So, change it according to your needs.

Why Excel is not opening CSV files correctly?

  • Afterward, if we open that file in Excel, the data will be in multiple columns. Thus, we have shown you the second solution to the problem: Excel opens CSV files in one column.

Why Excel is not opening CSV files correctly?


Solution 3: Specify Delimiter While Importing CSV File to Excel

In this method, we will import the data from the CSV file into Excel. This solution should work if the other two do not work for you. The importing feature of the CSV file is on the Data tab, and from there we will use the From Text/CSV option.

📌 Steps:

  • Firstly, from the Data tab, select From Text/CSV.

Why Excel is not opening CSV files correctly?

  • So, the Import Data window will appear.
  • Secondly, navigate to the file location.
  • Thirdly, select the CSV file and press Import.

Why Excel is not opening CSV files correctly?

  • Then, another window will pop up.
  • Afterward, from the Load dropdown menu, select “Load”.

Why Excel is not opening CSV files correctly?

  • So, this action will import the CSV data into Excel. Moreover, we can see our parser from the second solution is still there. You can simply delete row 5.

Why Excel is not opening CSV files correctly?

Read More: How to Open CSV with Delimiter in Excel (6 Simple Ways)


Case 2: Leading Zeros Are Kept While Importing from CSV File

When importing CSV files into Excel, the leading zeros in the CSV file are frequently omitted by default. This leads to data misinterpretation in various contexts, such as account numbers, ZIP codes, phone numbers, and so on, where leading zeros convey useful information.

Applying the Power Query is one of the most efficient ways to keep the leading zeros in Excel CSV. Let’s familiarize ourselves with the following steps to do this.

📌 Steps:

  • Firstly, go to the Data tab from Ribbon.
  • After that, select From Text/CSV option from Get & Transform Data
  • Now, navigate to your CSV file and select it.
  • Following that, click on Import.

After importing your CSV file, you will see a preview of your data like the following picture.

  • Firstly, click on Transform Data.

Why Excel is not opening CSV files correctly?

Afterward, a Power Query Editor window will be open on your worksheet as shown in the image given below.

  • Now, click on the marked area in the Account Number column.
  • After that, select the Text option from the drop-down.

Why Excel is not opening CSV files correctly?

  • Subsequently, a dialogue box will open, and choose Replace Current.

Why Excel is not opening CSV files correctly?

At this stage, you will be able to see that zeros in the Account Number column are back as shown in the following image.

  • By following the same steps for the Telephone Number column, we will get the following output.

Why Excel is not opening CSV files correctly?

  • Now, click on Close & Load.

Why Excel is not opening CSV files correctly?

There you go! You have successfully imported your CSV file into Excel and kept the leading zeros intact.

Why Excel is not opening CSV files correctly?

Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)


Similar Readings

  • [Solved:] Excel Is Opening CSV Files in One Column (3 Solutions)
  • Excel VBA to Read CSV File Line by Line (3 Ideal Examples)
  • Excel VBA: Import Comma Delimited Text File (2 Cases)
  • Merge CSV Files into Multiple Sheets in Excel (with Easy Steps)
  • Difference Between CSV and Excel Files (11 Suitable Examples)

Case 3: Dates Are Formatted Incorrectly While Importing from CSV File

While importing files containing date values there occurs problems like days and months are mixed up or some values are converted to dates that are actually not date values.  There is a quick way to stop Excel from auto-formatting dates in CSV files. For this, follow the steps below:

📌 Steps:

  • In the beginning, go to the Data tab and select From Text/CSV. It will open the Import Data
  • Select the CSV file and click Import. Make sure you have selected All Files in the file type field.
  • After that, a window containing the dataset will occur. Select Transform Data from there.

Why Excel is not opening CSV files correctly?

  • After clicking Transform Data, the Power Query Editor will open.
  • Next, select the Date of Birth column in the Power Query Editor.
  • Then, go to the Transform tab and select Data Type. A drop-down menu will occur.
  • Select Text from the drop-down menu.

Why Excel is not opening CSV files correctly?

  • Instantly, a Change Column Type dialog box will pop up. Select Replace current from there.

Why Excel is not opening CSV files correctly?

  • Then, you will find results like the one below in the Power Query Editor.
  • Now, go to the Home tab and select Close & Load.

Why Excel is not opening CSV files correctly?

  • Finally, you will see results like the below in the Excel worksheet.

Why Excel is not opening CSV files correctly?

Read More:How to Import CSV into Existing Sheet in Excel (5 Methods)


Case 4: Numbers Converted to Scientific Notation

Sometimes when we try to import a CSV file containing numbers that are of digits of higher numbers, the numbers become converted to scientific notation. Here I will show you how you can avoid this.

Why Excel is not opening CSV files correctly?

📌 Steps:

  • First, go to the Data tab and select From Text/CSV and import the CSV file.
  • After that, a window containing the dataset will occur. Select Transform Data from there.

Why Excel is not opening CSV files correctly?

  • Now, the Power Query Editor will appear.
  • Next, select the 123 icon on the Account Number column header.
  • Then, a drop-down menu will occur.
  • Here, select Text from the drop-down menu.

Why Excel is not opening CSV files correctly?

  • As a result, you will see that the numbers are in the full form not in the scientific format.
  • Now, go to the Home tab and select Close & Load.

Why Excel is not opening CSV files correctly?

  • Thus, you have successfully imported long numbers in full form without losing digits from a CSV file.

Why Excel is not opening CSV files correctly?

Read More: How to Convert CSV to XLSX (4 Quick Methods)


Conclusion

In this article, you have found how to fix the issue that the CSV file is not opening correctly in Excel. I hope you found this article helpful. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.


  • How to Open CSV File with Columns in Excel (3 Easy Ways)
  • How to Open CSV File in Excel with Columns Automatically (3 Methods)
  • Excel VBA: Read Text File into String (4 Effective Cases)
  • How to Convert CSV to XLSX without Opening (5 Easy Methods)
  • How to Read CSV File in Excel (4 Fastest Ways)
  • Excel VBA to Convert CSV File to XLSX (2 Easy Examples)

How do I fix CSV format in Excel?

Using the "From Text" feature in Excel.
Open the Excel file that you're trying to change in terms of data formatting. ... .
Click the Data tab, then From Text..
Select the CSV file that has the data clustered into one column..
Select Delimited, then make sure the File Origin is Unicode UTF-8..

How do I convert CSV to Excel without changing format?

Steps to convert content from a TXT or CSV file into Excel.
Open the Excel spreadsheet where you want to save the data and click the Data tab..
In the Get External Data group, click From Text..
Select the TXT or CSV file you want to convert and click Import..
Select "Delimited". ... .
Click Next..

How do I open a CSV file in Excel with formatting?

Click File > New Workbook in Excel's top toolbar. Click From Text in the Data tab. Select the desired . CSV file in the pop-up window that appears.

Why does my CSV file look weird?

If you have opened a csv file and found weird characters in it, chances are you are using Excel. All of WebCollects exports are in the "UTF8" character set, which covers all international characters correctly. Some versions of Excel have trouble opening csv files that use UTF8 format.