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
CSV year problem - excel changes "01 / 02" to word
Moderator: General Moderators
-
yossarianpickwick
- Forum Newbie
- Posts: 4
- Joined: Sun Nov 05, 2006 7:29 pm
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
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.
-
yossarianpickwick
- Forum Newbie
- Posts: 4
- Joined: Sun Nov 05, 2006 7:29 pm
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
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
-
yossarianpickwick
- Forum Newbie
- Posts: 4
- Joined: Sun Nov 05, 2006 7:29 pm
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:
HTH (and please post better workarounds)
David
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/05It 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.," "01/02, " "02/03, " "03/04, " "04/05
HTH (and please post better workarounds)
David
-
yossarianpickwick
- Forum Newbie
- Posts: 4
- Joined: Sun Nov 05, 2006 7:29 pm