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.
CSV File Not Opening Correctly in Excel: 4 Cases with 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.
- However, if we open it in Excel, the data will be in column A
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
- Then, the Region window will pop up.
- After that, select “Additional settings…”.
- 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.
- Then, if we open that CSV file, it will be on multiple columns.
Read More: How to View CSV File in Excel
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.
- 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.
sep = ;
- 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.
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.
- So, the Import Data window will appear.
- Secondly, navigate to the file location.
- Thirdly, select the CSV file and press Import.
- Then, another window will pop up.
- Afterward, from the Load dropdown menu, select “Load”.
- 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.
Read More: How to Open CSV with Delimiter in Excel
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.
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.
- Subsequently, a dialogue box will open, and choose Replace Current.
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.
- Now, click on Close & Load.
There you go! You have successfully imported your CSV file into Excel and kept the leading zeros intact.
Read More: Open CSV File in Excel Without Formatting
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.
- 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.
- Instantly, a Change Column Type dialog box will pop up. Select Replace current from there.
- Then, you will find results like the one below in the Power Query Editor.
- Now, go to the Home tab and select Close & Load.
- Finally, you will see results like the below in the Excel worksheet.
Read More: How to Open Large CSV Files in Excel
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.
📌 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.
- 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.
- 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.
- Thus, you have successfully imported long numbers in full form without losing digits from a CSV file.
Read More: How to Open CSV File in Excel with Columns Automatically
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. Please leave comments, suggestions, or queries if you have any in the comment section below.
