How can I safely open a CSV file in Excel

Excel is only trying to help

Did you know that just opening a CSV file in Excel can change the data it contains? It's true! Excel automatically tries to find any numbers and dates in your data and changes them to a format that it thinks you would prefer.

That might be a nice feature if you're just going to work with the data in Excel. But if you plan to import the data into another system (such as Watershed), chances are that these changes will make your data incompatible with the import template you have set up.

The changes Excel makes might cause the import to fail, or worse, the import will succeed but the reformatting will result in errors in your data. Switching the order of days and months in a date, for example, could have significant impact on how the date is interpreted. 

How to open your CSV 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. 

Open a new, blank worksheet in Excel.

In the Data tab, select From Text.

Select your CSV file and click Import.

Complete steps 1 and 2 of the wizard. Make sure you select Comma as the only delimiter.

In step 3, click on the first column in the Data preview, hold shift down and click on the last column (you might need to scroll). This will select every column. Now change the Column data format to "Text" and select Finish

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

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. 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.

If you can't find what you need or you want to ask a real person a question, please contact customer support.