Seriously, when is Microsoft going to make a setting that says, "Do NOT auto-detect data types!" so I can check that box?
I deal with raw data a lot, and we end up using CSV simply because most everyone has a program that allows for easy viewing of the data inside (I still wish people had settled on a simple delimited format, but that's another rant). Excel (2007/2010) ends up being the most common data-viewing-tool that people use. It's unfortunate. Most people have no idea that Excel attempts to auto-detect the type of data in a field, and even less know that some of it's guesses are irreversible.
So if you send them a csv and they wish to change one little thing before sending it along, they have no idea that they just screwed up the file! Date information is the most annoying. If you send "yyyy-MM-dd HH:mm:ss", it gets transformed into "M/d/yyyy HH:mm"---not only for viewing, but it gets saved back into the CSV in that format!
So start with a CSV file with these contents:
I deal with raw data a lot, and we end up using CSV simply because most everyone has a program that allows for easy viewing of the data inside (I still wish people had settled on a simple delimited format, but that's another rant). Excel (2007/2010) ends up being the most common data-viewing-tool that people use. It's unfortunate. Most people have no idea that Excel attempts to auto-detect the type of data in a field, and even less know that some of it's guesses are irreversible.
So if you send them a csv and they wish to change one little thing before sending it along, they have no idea that they just screwed up the file! Date information is the most annoying. If you send "yyyy-MM-dd HH:mm:ss", it gets transformed into "M/d/yyyy HH:mm"---not only for viewing, but it gets saved back into the CSV in that format!
So start with a CSV file with these contents:
2007-03-24 13:23:00,25E2,just some textIf you open that in Excel, adjust the columns so you can see the data, then close the file, Excel asks you to save the "changes". So if you do, and keep it CSV, your file will get changed to this:
2011-05-01 02:11:05,11F7,more text
3/24/2007 13:23,2.50E+03,just some textHow "fun". Note that you could work around the date format by forcing a custom format on those fields. However, the "25E2" text is forever mangled; I know of no workaround.
5/1/2011 2:11,11F7,more text
Comments