I have a very large .csv. If I print the first line of the file it looks as follows.
2020/01/01 00:00:47.069, ZoneStatistics, 1955786687, 02 Adv, 1, 0, 19,
However when I open it in excel...
43831.00054, ZoneStatistics, 1955786687, 02 Adv, 1, 0, 19,
If I change the format of the first column to date, no matter what I choose from the list it does not display correctly.
Are there any solutions to this?
Use this custom cell format:
yyyy/mm/dd hh:mm:ss.000
Yes, there is a solution to the issue of how Excel formats your data when it reads the CSV file. The default format works reasonably well with some common data, but for those cases where the default doesn't cut it, the solution is to tell Excel to import the file, rather than simply opening it.
The difference this makes is that you then get a chance to tell Excel how to format your dates, numbers and so on in the way you need.
In newer versions of Excel, this is done using Power Query, which can be launched from the Data ribbon tab, where there is aGet Data menu:
Here you may import data from your CSV or other text file in a controlled way, as well as from various other data sources:
Older versions of Excel use the Text Import Wizard (which you may continue to use in newer versions if you prefer it or are not yet used to Power Query). The linked pages include instructions for both ways, and for re-enabling the Wizard if you want to use it in newer versions.
Whichever version of Excel you have, importing rather than opening is the solution just the same, by giving you fine-grained control over how dates, numbers and other data types are interpreted by Excel when it reads your data.
