Scott S Nelson

The upside to modern PCs is that so many file associations are created for us automatically. This is offset to a degree because the default setting in Windows is to hide file extensions, so that what you see is just the name and an icon. This is especially problematic for CSV files as most people who use Windows have Excel and since the friendly green icon is fronting the file the habit to just click it prevails.

Sometimes, this association is fine. Simple text data delimited with commas or tabs can be converted to Excel format with no manual intervention and everything looks fine. But, if any of the fields have commas, or are dates or numbers, than Excel makes lots of assumptions that it doesn’t tell you about and changes the data to match the assumptions. One does not need to be a data scientist to know this is bad. Indeed, one only needs a pulse to be annoyed by it, and if you don’t know why the data is being messed up, frustration is a common reaction.

The first thing I recommend is to change your Window settings to show file extensions. There are instructions provided by Microsoft for this here.

Next, develop the habit of opening CSV files using one of the more time-consuming (and reliable) methods. Method 1 is to open the CSV file in a text editor (my personal preference is EditPad, and there is a good list of others here). Then create a new Excel workbook or sheet, copy the contents of the CSV file from the text editor and use the Paste > Use Text Import Wizard option.

The simplest approach is to accept the defaults on the first two steps and on the third step select all columns by holding the Shift key, scrolling to the right and click the last column, and choose the Text column format.

This will create a clean separation by column with no auto-formatting applied. Then Finish and you will have the data as you expected.

For larger files, I suggest reading this thread on SuperUser.com.

When creating data in Excel for CSV upload, format all the columns as Text before saving as CSV.  If you have to do the same data transforms regularly, I recommend creating a template with formulas.

Probably the hardest habit of all for most users to adopt is when opening the file in Excel just to view its contents is to select No when prompted to save the changes.