CSV year problem - excel changes "01 / 02" to word

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yossarianpickwick
Forum Newbie
Posts: 4
Joined: Sun Nov 05, 2006 7:29 pm

CSV year problem - excel changes "01 / 02" to word

Post 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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
yossarianpickwick
Forum Newbie
Posts: 4
Joined: Sun Nov 05, 2006 7:29 pm

Post 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
yossarianpickwick
Forum Newbie
Posts: 4
Joined: Sun Nov 05, 2006 7:29 pm

Post 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
yossarianpickwick
Forum Newbie
Posts: 4
Joined: Sun Nov 05, 2006 7:29 pm

Post 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.
Post Reply