Mursalin Ibne Salehin
In this article, we will learn to stop excel from auto formatting dates in CSV. CSV stands for comma-separated values. Sometimes, we need to open or import a CSV file in our excel worksheet. Excel auto formats the date when we open or import any CSV file. Often, it can be undesirable because it changes the data. So, to stop excel from auto formatting dates in CSV, we need to follow some methods. Today, we will demonstrate these methods in easy steps.
Download Practice Book
Download the practice book.
What Is a CSV File?
The file which separates values by a comma is called a CSV file. It is a delimited text file. Each line of a CSV file consists of a data record where each field is separated by a comma. We can import or export a CSV file that contains data records in our excel worksheet very easily.
3 Ways to Stop Excel from Auto Formatting Dates in CSV
To explain the methods, we will use a CSV file that contains information about the ID Number and Date of Birth of some employees.
If we open or import this CSV file in excel, we will see a dataset like below.
And after auto-fitting the columns, we will see the result like below.
Here, if we look at the Date of Birth column, we can see excel has auto formatted the dates. It does not match with our original dataset in the CSV file. For example, if we look at Cell C2, it contains 10-Jul-93. But our CSV file contains 10-July-1993. We want to display the CSV file without auto formatting the dates. The methods below will discuss how to stop excel from auto formatting dates in CSV.
1. Save the CSV File as Text Document to Stop Excel from Auto Formatting Dates
We can stop excel from auto formatting dates by saving the CSV file as a Text Document file. It is one of the quickest processes. Let’s follow the steps below to learn this method.
STEPS:
- In the first place, open the CSV file.
- Secondly, click on the File tab. A drop-down menu will occur.
- Select Save As from the drop-down menu. The Save As window will appear.
- Now, change the Save as type to Text Documents(*.txt).
- After that, click Save to proceed.
- After saving the CSV file as a Text Document file, open Excel.
- Then, go to the File tab.
- Select Open and then, Browse like the picture below.
- After that, select the Text Document file and click Open. Make sure you have selected All Files in the file type field.
- After clicking Open, you will see a Text Import Wizard on the screen. It is the first step of the Text Import Wizard.
- Select Delimited in the Original data type field and check the My data has headers option. Then, select Next.
- After clicking Next, the second step of the Text Import Wizard will occur.
- Check Comma and uncheck Tab in the Delimiters field.
- Then, click Next.
- After that, the third step of the Text Import Wizard will appear.
- Now, select the desired column from the Data Preview field. We have selected the Date of Birth column.
- After selecting the column, select Text in the Column data format field.
- Finally, click Finish to see the results below.
Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
2. Stop Excel from Auto Formatting Dates by Importing the CSV File
There is another quick way to stop excel from auto formatting dates in CSV files. In this method, we will use the Data tab to import the file. Here, we will use the same CSV file. Let’s pay attention to the steps below to learn more.
STEPS:
- In the beginning, go to the Data tab and select From Text/CSV. It will open the Import Data window.
- 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 window.
- 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 below in the Power Query Editor.
- Now, go to the Home tab and select Close & Load.
- Finally, you will see results like below in the excel worksheet.
Read More: How to Use Formula to Change Date Format in Excel (5 Methods)
Similar Readings:
- How to Convert Text to Date with Excel VBA (5 Ways)
- Convert Date to Year in Excel (3 Quick Ways)
- How to Convert Date to Day of Week in Excel (8 Methods)
- Get the First Day of the Current Month in Excel (3 Methods)
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
3. Format the CSV File to Stop Excel from Auto Formatting Dates
In this method, we will format the CSV file to stop excel from auto formatting dates. We will mainly change the data format inside the CSV file. Again, we will use the same CSV file here. Let’s observe the steps below to learn more.
STEPS:
- Firstly, open the CSV file.
- Secondly, write:
"John","100","=""10-July-1993"""Instead of:
"John","100","10-July-1993"
Here, we have added “=” as prefix and “” as a suffix in the Date of Birth column.
- Follow this format for all data entries.
- Save it by pressing Ctrl + S and then, close the Notepad after saving it.
- Next, open Excel and select the File tab.
- Click Open and then, click Browse.
- After that, select the CSV file and click Open. Make sure you have selected All Files in the file type field.
- Finally, you will see results like below.
Related Content: How to Change Default Date Format from US to UK in Excel (3 Ways)
Things to Remember
There are certain things you need to remember to stop excel from auto formatting dates.
- While opening or importing the CSV file from excel, if you don’t find the file in the desired folder, select All Files instead of All Excel Files.
- You need to enable the Power Query if you are using Excel 2013 or earlier versions.
- If you have a large dataset, use Method-1 or Method-2. Method-3 is not appreciated for large datasets.
Conclusion
We have discussed 3 quick and easy methods to stop excel from auto formatting dates in CSV files. I hope these methods will help you to solve your problems related to auto formatting dates in CSV. Furthermore, we have also added the practice book at the beginning of the article. You can download the practice book to exercise. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.
I used method 2. Looked beautiful. I saved it and reopened it and it’s gone. Why and how do I fix it?
Hello DEBB WOLFE
We appreciate your comment. I understand your difficulty, and you can avoid the issue by importing the CVS file into the existing worksheet.
Change the column type in the Power Query window to text, as this article mentions. Next, select the Home tab. Select Close & Load and then Close & Load To at a later time.
As a result, the Import Data window will display. Check the Existing Worksheet and then press OK.
Thus, you will be able to solve the problem.
Regards
Lutfor Rahman Shimanto