1

Surely someone before me has needed to produce a year, month, day in a single field for a CSV that "just works" in popular versions of Microsoft Excel? I want only a date, no timestamp, though I suppose I could include 00:00 or something like that if I absolutely had to.

Panagiotis Kanavos points out that "Excel can only import it and try to guess whether the text values correspond to a certain type, using the user's locale settings." My question is about what format will cause Excel to guess correctly in the US and Europe, and ideally everywhere else.

If it's impossible or unreliable to do this in CSV, I will accept a link to using some zipped XML format or something that Excel and other spreadsheets accept universally instead of CSV.

This is NOT a duplicate of of the following:

GlenPeterson
  • 4,866
  • 5
  • 41
  • 49
  • The key word in your question is "magically"... Maybe the number value of the date? – cybernetic.nomad Jan 10 '19 at 15:13
  • 5
    Then just use `YYYY-MM-DD` – Scott Craner Jan 10 '19 at 15:13
  • 4
    A little humour is obligatory... https://www.xkcd.com/1179/ – Mark Setchell Jan 10 '19 at 15:14
  • @cybernetic.nomad I removed the word "magically." I just mean that Excel recognizes it as a date field without confusing month and day. – GlenPeterson Jan 10 '19 at 15:22
  • 1
    Why create a *CSV* file when you want an *xlsx* file? `xlsx` *is* a zipped XML format. You can create the XML files directly or use one of the many libraries available in most languages that can do this for you – Panagiotis Kanavos Jan 10 '19 at 15:24
  • @PanagiotisKanavos It's just really easy to write a plain-text CSV file. For .xlsx I think you have to make multiple temp files, and zip them on the server before starting the download, then clean up afterward. Similarly, the client should have an easier time importing CSV into some system other than Excel if they want to. IDK. Maybe .xlsx is the answer? – GlenPeterson Jan 10 '19 at 15:27
  • 1
    In any case, CSV files have no types. Excel can only *import* it and try to guess whether the text values correspond to a certain type, using the user's locale settings. When you use the `Insert ` menu, you can modify its guesses. When you double-click on it, it can only use the locale settings. – Panagiotis Kanavos Jan 10 '19 at 15:28
  • @PanagiotisKanavos - yes, I'm looking for the date format that makes Excel guess correctly. – GlenPeterson Jan 10 '19 at 15:29
  • @GlenPeterson not at all. Depends on the language and library. For example, with EPPlus one can load an array of objects with `sheet.Cells.LoadFromCollection()` and then write it out to the web application's response stream. – Panagiotis Kanavos Jan 10 '19 at 15:29
  • 1
    @GlenPeterson that's *your locale's* format. Otherwise, ISO8601. *With* the timestamp and probably even the `T`. There are no types in a CSV. There's no `date` type. – Panagiotis Kanavos Jan 10 '19 at 15:31
  • @GlenPeterson Excel isn't a database either. It doesn't have separate date and datetime types, it has only a limited set of types and the rest is styling. Dates are datetime values whose time component is zero and their cell's style has no time component. That's easy to specify in `xlsx`, impossible in CSV. – Panagiotis Kanavos Jan 10 '19 at 15:32
  • @GlenPeterson finally, Excel will only read so much data to determine the data types. If the *last* line in a 100K row sheet contains an unexpected value, Excel will fail. You can configure the number of rows to read but you can't specify *all of 1M of them*. – Panagiotis Kanavos Jan 10 '19 at 15:34
  • `It's OK to be a little Euro/USA-centric` that's impossible. The US uses the *opposite* month/day order from all European countries. Germany used dot as a date separator. What language are you using? Why not use a library to create a file that will end up being 5 times smaller than a CSV? – Panagiotis Kanavos Jan 10 '19 at 15:37
  • You mentioned PHP Excel. Are you using PHP? That library was replaced by [PHPSpreadsheet](https://github.com/PHPOffice/PhpSpreadsheet). There's no PHP Excel vs Microsoft Excel. Just make sure you produce *XLSX* files, not the abandoned and not the largely undocumented pre-2006 `xls` format. – Panagiotis Kanavos Jan 10 '19 at 15:43
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186494/discussion-between-glenpeterson-and-panagiotis-kanavos). – GlenPeterson Jan 10 '19 at 15:43
  • @ScottCraner Are you suggesting YYYY-MM-DD because that's the official international standard, or because Excel recognizes it automatically? If Excel recognizes it, then that's my answer. – GlenPeterson Jan 10 '19 at 15:52
  • 1
    Yes, that is your answer. Excel will recognize that as a date no mater the local settings. – Scott Craner Jan 10 '19 at 15:53
  • @ScottCraner - great - thanks! If you want points, write it up and I'll accept it. The sooner we can close this, the fewer down-votes I'm going to get on my question. – GlenPeterson Jan 10 '19 at 15:54
  • 1
    The only way to stop the down votes is to delete. But I will answer if you want – Scott Craner Jan 10 '19 at 15:55

2 Answers2

2

Excel will recognize YYYY-MM-DD as a global standard.

enter image description here

Cartoon from: https://www.xkcd.com/1179/

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 2
    The mouse-over on this comic is priceless: "ISO 8601 was published on 06/05/88 and most recently amended on 12/01/04." – GlenPeterson Jan 10 '19 at 16:14
1

The difficulty with answering your question is that to test the proposed answer, the format must be tested in "all popular versions" of Excel

I have several versions of Excel and in my testing this:

enter image description here

worked in all my versions (English-US Locale)

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 2
    The month names add clarity for English speakers, but most languages have their own names for the months. If some system ever parses this CSV (instead of Excel) I don't want someone to have to write a parser for English month names, or import some huge date conversion library. So I'm going with ISO 8601 for now as the accepted answer. I also upvoted your answer because it adds richness and depth - thanks! – GlenPeterson Jan 10 '19 at 19:00
  • @GlenPeterson I agree with your assessments. – Gary's Student Jan 10 '19 at 20:41