Page 1 of 1
CSV year problem - excel changes "01 / 02" to word
Posted: Sun Nov 05, 2006 7:37 pm
by yossarianpickwick
Hi All,
I have written a script that converts a report table to a CSV download but the years, which *have* to be formatted in '02 / 03' format (ie 2002 / 2003 in shorthand) are getting autoconverted in Excel to "date / Month" .
This is rather frustrating.
Can anyone suggest how I might overcome this? Is there a way to specify to NOT autoparse these fields in excel?
Thanks
David
Posted: Mon Nov 06, 2006 12:04 am
by aaronhall
Not really the appropriate forum for this question

, but if you highlight the cells in question, and click on Format > Cells... from the top menu, it will bring up a dialog box. In that box, the selected tab should be "Number". In the "Category" selection box, either "General" or "Text" should fix the problem.
Posted: Mon Nov 06, 2006 1:02 pm
by yossarianpickwick
Hi there,
Thanks for your response.
I think you mistake my problem. I am aware of how to open Excel and tell it to change the 'type' of cell which does stop it from autoformatting the dates.
However, I am not in control of the dozens of people's Excel program who will be using the reporting tool I am working on.
IE.. I need to fix the problem at the CSV file level rather than the 'Using Excel' level.
I think because of that, it is appropriate to ask for help in this forum - I don't need info on how to use Excel so much as I need info on how to (using PHP) create a CSV file that works around this Excel bug.
Thanks
David
Posted: Mon Nov 06, 2006 1:04 pm
by yossarianpickwick
I have found a pretty ugly work around but I post it here because I cannot find any solution to this on the internet and hope this could help someone else.
I hope someone can provide a better solution but if you change the .csv file so that the line:
Is changed to:
," "01/02, " "02/03, " "03/04, " "04/05
It will force a space before the numbers and stop Excel formatting the cell. Now, of course, this is a rather invalid way of fixing this but it is better than having my row reformatted entirely out of context.
HTH (and please post better workarounds)
David
Posted: Mon Nov 06, 2006 1:24 pm
by yossarianpickwick
A better solution was emailed to me:
Change the dates row to look like this:
="01/02",="02/03",="03/04",="04/05"
The ="" forces Excel into 'text' mode without carrying the " through.
Thanks for your help.