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.
Recommended Reading
About the author
As a co-author of xAPI, Andrew has been instrumental in revolutionizing the way we approach data-driven learning design. With his extensive background in instructional design and development, he’s an expert in crafting engaging learning experiences and a master at building robust learning platforms in both corporate and academic environments. Andrew’s journey began with a simple belief: learning should be meaningful, measurable, and, most importantly, enjoyable. This belief has led him to work with some of the industry’s most innovative organizations and thought leaders, helping them unlock the true potential of their learning strategies. Andrew has also shared his insights at conferences and workshops across the globe, empowering others to harness the power of data in their own learning initiatives.
Subscribe to our blog