How to Fix CSV File in Excel (5 Common Problems)

Nowadays, the CSV file format is very popular due to its simplicity and compactness. It is also very easy to manipulate CSV files. But while working with a CSV file in Excel, we find many problems. In this article, we will discuss how to fix CSV file in Excel and solve some of the common problems.

How to Fix CSV File in Excel: 5 Common Problems

In this section, we will discuss 5 very common problems that we may face while working with a CSV file in Excel. For each problem, we will devise more than one solution so that you can try each one of them and check which one solves your problem. So let’s begin with the first problem in the list.

1. All Data from CSV File Appearing in a Single Column in Excel

This is a common issue that you may need to fix while working with CSV files in Excel. Due to this problem, when we open a CSV file in Excel, we observe that all of the data is shown in a single column. This issue arises because Excel divides data into columns using the list separator set up in your Windows Regional settings. This might either be a comma or a semicolon in North America and a few other places (in European countries). The file opens in one column when a particular CSV file employs a delimiter other than the default separator.

To solve the issue we can take 3 different approaches.

1.1 Changing Delimiter in CSV File

We can manually set the delimiter in the CSV file itself. To do that, follow the steps below.

Steps:

• Firstly, open the CSV file in any text editor app. Here, I am using Notepad.

• Now, look at the data in the text editor app. See whether they are separated by a comma (,) or semicolon (;). In my case, the data are separated by a comma.

• Now to explicitly tell excel which separator the file has, we need to write on the first line like below. (see the figure below)

sep=,

• If your file is separated by a semicolon, you need to write sep=;
• By appropriately defining the separator, excel will show them properly.

1.2 Delimiter Specifications for Importing CSV Files into Excel

We can import the CSV file using either the Text Import Wizard or Power Query rather than opening it directly in Excel. In both cases, we can specify the delimiter. Let’s see steps on how we can specify the delimiter while using Power Query.

Steps:

• First, open an excel sheet. Then go to the Data From there on the Get & Transform Data group, click on From Text/CSV.

• As a result, an Import Data Dialogue Box should pop up. Now select your CSV file and click on Import.

• Consequently, a Preview Dialogue Box should appear like the figure below. You will notice that on that dialogue box, there is an option to choose Delimeter and you can simultaneously see how it would look in the preview. Hence by looking at the preview, you can pick the correct delimiter.

• And finally, click on Load, and you will see that data from the CSV file are being displayed in a separate sheet as a table.

• This imported table is linked to the original document. Hence it will always show the updated data.

The Text Import Wizard is a legacy feature of excel. Hence, from the Excel 2016 version, it has moved from ribbon to Excel Options.  After enabling it, follow the steps below to import data from the CSV file.

Steps:

• In the Data tab, click on Get Data > Legacy Wizards >From Text.

• As a result, an Import Text File window will open. Now select the CSV file and click on Import.

• After that, we will have to complete 3 steps to import the data. In the first step, check the Delimited option and if you have a header in the data, check the My data has headers. Then click on Next.

• In the 2nd step, we have to choose the delimiter and click Next. Here I have chosen Comma.

• In the final step, choose the General column data format and click on Finish.

• Now, in the Import Data Dialogue box, choose where you want to put the data and click OK.

• Finally, you should get the final result.

1.3 Utilise Text to Columns Feature to Split Cells

This method is applicable when you have already imported the CSV file into Excel but it is still displaying in a single column only. We can use the Text to Column Feature to separate the text and distribute them into multiple columns. To know in detail, follow the referred link. Here I will only show how we can split a column into several by specifying a delimiter. Follow the steps below.

Steps:

• First, select the data and then go to the Data From there, choose Text to Columns from the Data tools group.

• Now, in the Convert Text to Column Wizard chose Delimited and click Next.

• Now in step 2, choose comma as the delimiter and click Next.

• Finally, set the destination and click Finish.

• Consequently, you will have your data organized in columns as expected.

Read More: How to Keep Leading Zeros in Excel CSV Programmatically

2. Keeping Leading Zeros in Excel CSV

Another problem that people face is the leading zeros of a CSV file get truncated when opened in Excel. The reason behind it is that when a file is opened in excel, it is converted into the General format that stips off leading zeros. We can solve the problem in two ways.

2.1 Utilising Text Import Wizard

As we have seen in Method 1.2  in the case of Text Import Wizard, while importing from a CSV file, in step 3, we choose General as Column Date Format. But to keep the leading zero intact, we have to select another option. To do this, follow the steps below.

Steps:

• Similar to 2 import the CSV file in excel using Text Import Wizard and do the same things up to step 3.
• In step 3 of the Text Import Wizard, first, select the column which contains leading zeros. Then, select Text in the Column data format.

• Then click on You will get the desired leading zeros in excel.

2.2 Applying Power Query

There are two ways to show leading zeros if you want to import a CSV file into Excel using a connection.

2.2.1 Importing Data in Text Format

Like in method 1.2, when loading the data by Power Query, select Do no detect data types from the Data Type Detection box.

As a result, the leading zeros will not be truncated when imported into Excel.

2.2.2 Setting Format for Individual Column

If your data is in more than one format, then the previous method will not work. Rather, we will need to set the format for each column. To do that, follow the steps below.

Steps:

• Instead of clicking on Load below the data preview window, click on Transform Data. a window should pop up like this.

• Now, select the column which contains numbers, and from the Data Type drop-down menu, choose Text.

• Now if you load the imported data, the leading zeros will be intact.

Hope this will solve your problem.

Read More: Paste Comma Separated Values into Excel

3. How to Fix Excel Issues with CSV Date Format

There are some occasions when after converting CSV into Excel, dates are shown improperly. Here we will discuss 3 major problems concerning date format in excel when importing data from CSV files.

3.1 Mixing Up of Days and Months

If the format of the imported dates is not the same as in the Windows Regional settings, then days and months may mix up in excel. For example, September 11, 2001(09/11/2001) may be displayed as November 9, 2001. To solve the problem, follow the steps below.

Steps:

• During the 3rd step of Text Import Wizard, select the column which contains dates.

• Now, in the Column date format, select Date then choose the appropriate date format. After that, click on Finish. As a result, the dates will be correctly formatted.

3.2 Converting Some Values to Dates

Sometimes, Excel thinks of a number or text as a date when it closely resembles any date format. For example, if you have a text like jun2 in your data, excel will think of it as June 2. This may result in unnecessary transformations from general format to date format. To fix this CSV file problem in excel, follow the steps below.

Steps:

• During the 3rd step of Text Import Wizard, select the column which contains date like texts.

• Now, select Text as Column Data Forma You will see that this time the selected column is displaying as text rather than date.

3.3 Incorrectly Formatting of Dates

You can also format dates by opening the CSV file in excel if your date is not displaying properly. To do that, first, open the CSV file in excel and then follow the steps below,

Steps:

• First, select the column which contains dates.
• Then, to open the Format Cells dialog box, press Ctrl + 1
• Now, go to the Number tab, and select Date from Category.
• From Type, choose your desired formatting.
• Finally, click OK.

Read More: How to Edit CSV File in Excel

4. Preventing Excel from Displaying Numbers in Scientific Notation

Whenever the numbers in CSV files are larger, after importing them into excel, they are normally displayed in scientific notation. For example, if we have the number 12345678 in a CSV file, it will be shown as 1.2×10^7.

If you don’t want your numbers to be displayed that way, you can follow either of the 3 solutions discussed below.

4.1 Importing Long Numbers As Text

While importing them via Text Import Wizard, you can make the column format as Text( shown in the figure below)

As a result, you will get the numbers in full form.

4.2 Changing Formatting of Numbers in Excel

If you have already imported your data in excel, still you can display the scientifically expressed numbers in their full form. You just need to change the number format to Number from General.

4.3 Making the Column Wider

If you still face the issue, simply try to widen the column size. Hopefully, it will display the numbers in their full form.

Read More: Formatting CSV File in Excel

5. How to Fix CSV Files Not Saving in Excel

Many times we face problems in saving CSV files in Excel.  To fix this problem in CSV file in excel, here we will discuss five distinct solutions which you can apply while saving a CSV file in Excel.

5.1 Choosing CSV UTF-8 Format to Save CSV File in Excel

An effective way to ensure that CSV files in Microsoft Excel have all the essential updates without erasing or losing any data is to save them in the CSV UTF-8 file format. Follow the steps below to see how we can save files in that format.

Steps:

• First, open the CSV file in Excel and execute desired changes.
• Then, go to File Tab, and select Save As.
• Then, choose the location where the file will be stored.
• Finally, from the drop-down menu, choose CSV UTF-8.

• Then name your file and click on the Save option.

5.2 Saving Changes by Saving As Unicode File

Microsoft Excel also supports saving files in CSV UTF-16(Unicode Text) format. Similar to UTF-8, this file format effectively makes the required adjustments to your CSV file while maintaining characters and symbols. To save the file this way, follow the steps below.

Steps:

• Like the previous example, do similar to the first three steps.
• Then, from the dropdown menu, select Unicode Text and then click on Save.

5.3 Changing the Settings of Excel

You may have some parts of your Excel settings not configured properly to fix CSV files in excel, which would explain why you have trouble storing CSV files. Follow the instructions below to fix your file correctly and prevent issues.

Steps:

• In Microsoft Excel, select File > Options.

• After that, select the Advanced option.
• Unmark the ‘Use System Separators’ option.
• Finally, fix the Decimal Separator to Dot (.) and Thousands Separator to Comma (,). Then click OK.

• Then save your file in CSV format and see if the problem is solved or not.

5.4 Updating Microsoft Excel

To fix CSV file-saving problem in excel, try to update Microsoft Excel to the most recent version if you’re still having trouble saving your CSV file. This should fix the app’s faults and glitches and guarantee that you got a functional copy of Excel. To Update Microsoft Excel, follow the steps below.

Steps:

• Go to File Tab > Account > Update Options.
• Now from the drop-down menu, click on Update Now/Enable Updates.

• Then relaunch Excel and see if the problem is solved or not.

5.5 Reinstalling Microsoft Excel

The only thing left to do at this point is to uninstall Excel from the pc and reinstall a new version. By doing so, you can make sure that the software is correctly installed and that no system resources are missing. Follow the steps below to uninstall Excel.

Steps:

• First, go to the Control Panel and click on Uninstall a Program under Programs.

• Then search for Microsoft 365/Microsoft Excel, then right-click on it.

• Lastly, click on Uninstall and then follow the instruction on the prompt.

Hopefully, this will solve all the issues that you are facing while saving CSV files in Excel.

Read More: How to Sort CSV File in Excel

Things to Remember

• You should have a text editor(like Notepad) to solve 1st problem discussed above.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

That is the end of this article. I hope, this article will help to fix all the CSV file-related problems in Excel.  If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

Related Articles

• Merge CSV Files into Multiple Sheets in Excel
• How to Merge CSV Files in Excel
• How to Merge Multiple CSV Files into One Workbook in Excel
• How to Stop Excel from Auto Formatting Dates in CSV

<< Go Back to Import CSV to Excel | Importing Data in Excel | Learn Excel