#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    HTML opening in Excel date format problems - help!


    Hi guys,

    I'm new here but have a very annoying problem. I work at HP and one of our daily tasks is to supply another company with sanitized data which we pull from the database with given SQL. To cut a long story short, I've made a script to completely automate the full process.

    To do this, I run the SQL in SQLPlus and output as HTML but save as XLS. However, Excel automatically reformats the dates... So something like:
    28-Mar-13 12:00:00 (in the XLS/HTML file)
    Appears as:
    28/03/13 12:00

    Is there any way to stop Excel from doing this but WITHOUT having to go into Excel and make changes? I'm trying to make this involve as little effort as possible so extra steps aren't really an option.

    So in other words, how can I manipulate the HTML in a way which Excel will treat all dates (or all <td's>/cells) as string?

    Thanks chaps and hope I've explained everything alright!
  2. #2
  3. No Profile Picture
    Permanently Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Location
    Ludhiana
    Posts
    33
    Rep Power
    0
    There isn't any other method because the date and time output method excel is showing has been set in excel. if you open the same excel file in another PC, for instance, the date and time will appear in the format set on that specific PC. The reformating issue isn't your programme's issue but rather the application software's issue.

    Open your excel file

    press ctrl + shift + F

    the format option should appear.

    Select Number..and then select the date format suitable for you
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by shellygill
    There isn't any other method because the date and time output method excel is showing has been set in excel. if you open the same excel file in another PC, for instance, the date and time will appear in the format set on that specific PC. The reformating issue isn't your programme's issue but rather the application software's issue.

    Open your excel file

    press ctrl + shift + F

    the format option should appear.

    Select Number..and then select the date format suitable for you
    Hi there. Thanks for your reply. I did actually find a solution, for those who many end up here with the same problem.

    Once you've spooled your file as HTML and renamed to XLS. Run a script with the following command in:

    Code:
    sed -e 's|<td>|<td class=\"text"\>|g' -e 's|<p>|<style>td.text{mso-number-format:"\@";}</style><p>|g' $line > $line2
    This replaces all <td> tags (cells) with a cell tag specified as the text class.

    It then replaces both the <p> found at the top and bottom of the file with <p> AND some embedded CSS (which Excel picks up). Basically this just tells Excel to read all the cells specified as 'text' AS text! So it doesn't do any reformatting.

    You may need to tinker for your own uses, and $line/2 are obviously the input/output files.

    Was very satisfied with this solution :3.

    Peace out.

IMN logo majestic logo threadwatch logo seochat tools logo