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 :wink:, 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:

Code: Select all

,01/02, 02/03, 03/04, 04/05
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.