Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
I am attempting to parse Excel spreadsheets and insert the data into mysql. I am actually having quite a bit of success, but one issue I have not been able to address is ingesting dates. The unformatted date in Excel appears to be in the Julian format* (e.g. 10/28/2008 is 39749). There are three problems with that:
1) mysql expects dates formatted as 2008-10-28. I wrote a simple script to convert a standard US date format to mysql format:
#!/usr/bin/perl use strict; use warnings; my $sdate0='10/28/2008'; my @sdate = split /\//, $sdate0; my $sdate1 = join '-', $sdate[2],$sdate[0],$sdate[1]; print "$sdate1\n";
2) when I parse the date in the spreadsheet it actually is ingested as 39749 (sigh) so my split join script is of no value.
3) I did some research and it doesn't appear that mysql can convert Julian dates to it's date standard (2x sigh). Therefore, it looks like I need a perl script that can convert a Julian date to a mysql date. I looked on cpan and while there are some modules which address Julian dates, I didn't see anything that would do what I am trying to do. Does anyone have a Julian to mysql date conversion script?

*I typically receive the source spreadsheet via email in Office 97 xls format. I run Windows in a VM on top of a SUSE linux host os. When I save the xls file to my Unix (ext3) filesystem the date seems to distill down to the Julian format. I am running mysql and perl on the linux host and that appears to result in this Julian format issue.

Hagen Finley
Boulder, CO

In reply to Converting Julian Dates to Text by finhagen

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (6)
As of 2024-03-28 21:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found