Geek Answers Handbook

Opening CSV file in excel changes my formats

I have a CSV file that has a DateTime column that I used php to change to be in this format, 02/18/2013 03:53:48 PM

But when this file is opened in excel, all formats will be changed in this format, 02/18/2012 15:53

Now this is not a problem for me to watch or deal with, but I want the end user to not have to look at the time in 24-hour format in excel. How to make excel not change its format after opening csv and letting the end user sort this DateTime column without any problems. I know that I can change the date format by formatting the cells as soon as I open the file, but I do not want the end user to even have to take this step.

I would also like to note that when I select a cell, the formula bar will show my DateTime column in the format that I want, which puzzles me.

+4
source share
3 answers

CSV files are not formatted; they simply contain data.

MS Excel will review this data and try to set the appropriate format for it. For example, this means the correct numeric value. It can recognize a string containing a human-readable date / time value and convert it to its own timestammp value (represented as a floating point number), and then apply the corresponding corresponding date / time mask to this cell, so that it is erased as a date or time, but it’s not necessary to use the string representation that you had in your CSV file.

Solution # 1, prefix your formatted date string with the = character and wrap it in quotation marks so that it is treated as a string.

Solution No. 2, write the actual Excel file in which you can control the formatting of the cells.

+5
source

If you want to control the format of the final Excel file, you will need to output your data to this format and change it accordingly using the PHP Excel library. I can’t say exactly how to do this, since I don’t know which library you can use.

I used http://phpexcel.codeplex.com/ to some degree of success

If you simply output the CSV, Excel will open it with the default settings for users, which may differ from what you want.

+1
source

At that moment, when the time will be displayed, this is a parameter in Windows or (if users set its value) in Office. If you want something similar to a date / time that is NOT formatted by Excel when you open it, you should not specify the date / time in the first place.

Excel will see something that is specified in one direct quote as text so that you can store your date / time values ​​as "'02/18/2013 03:53:48 PM"

0
source

More articles:


All Articles