Ask the Expert: How to safely open a CSV file in Excel

     

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 (such as 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.Open a worksheet in Excel.
  2. In the Data tab, select From Text.Select From Data in Excel.
  3. Select your CSV file and click Import.Select CSV file in Excel.
  4. Complete Steps 1 and 2 of Excel's Text Import Wizard. Make sure you select Comma as the only delimiter.Select comma delimiter in Excel.
  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.Change Excel column data format.
  6. Click OK in the next pop-up window. Your file is now open in Excel without any changes to the data format.Open CSV file in Excel.
  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.Excel data warning

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.


Andrew Downes

About The Author

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