How do you open a CSV file in Excel without formatting?

Table of Contents Show

Microsoft 365 or Excel 2019
Excel 2016 – Excel 2010
Excel is only trying to help.
How to open your CSV file safely:
How do I open a CSV file directly in Excel?
How do I open a CSV file in Excel 365 without losing formatting?
How do you force Excel to open CSV files with data arranged in columns?

open the excel option you can find on the start menu of excel.

excel option -> advanced -> Use system separator (disable this then excel will not convert anything from text).

IMO, such misleading option though, eg if I have "5.123.444,24" and we want get number format from it, by enabling "use system separator" with the option to convert "." as thousand separator and "," as decimal separator, seems that we are doing right.In fact, if it is enabled, we'll have 5.123.444,24 and with the option disabled , we'll have 512344,24 which means the text "5.123.444,24" has been converted to number.

Data formatting can get mixed up when you open a data file with CSV format. In this article, we are going to show you how to avoid formatting change in CSV files in Excel.

Download Workbook

If you need to prevent numbers from changing into dates when entering data, please see How to stop excel from changing numbers to dates.

Excel applies formatting updates when opening CSV files, and you won't get a prompt to prevent this. You need to import the data in the CSV file. By importing the data, you will gain the ability to select the data type before parsing it. You do not need to select anything if you have a Microsoft 365 subscription or Excel 2019. The newer versions detect the data automatically.

Microsoft 365 or Excel 2019

Avoiding formatting change on CSV on Microsoft 365 or Excel 2019 is fairly easy.

Activate the Insert tab in the Ribbon
Click From Text/CSV in the Get & Transform Data section
Select your file
You will see the preview of your data
1. Either click Load to import the data
2. Or, use Transform button to open Power Query Editor and modify your data before parsing

You will see your data without any changes to formatting.

Excel 2016 – Excel 2010

In previous Excel versions, you can use the Text Import Wizard with text strings. To avoid formatting change on CSV, you first need to set data format manually.

Activate the Insert tab in the Ribbon
Click From Text/CSV in the Get & Transform Data section
Select your file
Move to the Step 3 by Next. Make sure that comma as delimiter is selected
In the 3rd step, select the columns where you'd like to keep the formatting
Mark the Text option as a Column data format
Click Finish to complete parsing

True or False: Opening a CSV file in Excel can change the data it contains. If you answered true, then you know how Excel automatically tries to change numbers and dates in your data into a format that it thinks you would prefer.

Excel is only trying to help.

This might be a nice feature if you're simply going to work with the data in Excel. But, if you plan to import the data into another system (e.g. xAPI data into Watershed), chances are these automatic formatting changes will make your data incompatible with the import template that's meant for another system.

The changes Excel makes to your data might cause a failed import—or worse, the import will succeed, but the reformatted data will be erroneous. Switching the order of days and months in a date, for example, could significantly impact how the date is interpreted by other systems.

How to open your CSV file safely:

The solution is to tell Excel that your CSV file is made up of text fields before you import it. This will stop it trying to change numbers and dates, or at least it will ask your permission before changing them.

1) Open a new, blank worksheet in Excel.

2) In the Data tab, select From Text.

3) Select your CSV file and click Import.

4) Complete Steps 1 and 2 of Excel's Text Import Wizard. Make sure you select Comma as the only delimiter.

5) In Step 3 of the Text Import Wizard, click on the first column in the Data preview, press the shift key, and click on the last column (you might need to scroll to the right to see it). This will select every column. Now, change the Column data format to “Text” and select Finish.

6) Click OK in the next pop-up window. Your file is now open in Excel without any changes to the data format.

7) Excel may still try to warn you that the data is not in the format it thinks you should be using. Just ignore those warnings; don't change the data format.

Recommended Reading

5 Pitfalls of Using Excel to Report on Learning
Do you manually pull data from various systems and import it into master spreadsheets to create statistics, graphs, and charts? In addition to the countless hours it takes to gather all this data, the process itself is prone to endless frustrations. Read this blog post about the top challenges when it comes to using spreadsheets to report on learning.

About the author

As one of the authors of xAPI, Andrew Downes has years of expertise in data-driven learning design. With a background in instructional design and development, he’s well versed in creating learning experiences and platforms in corporate and academic environments.