Paul, Let me also thank you for this information on the Excel date issue. I'm creating a CSV extract for a project. The CSV includes a header record to tell the user what each column contains. Dates come into Excel as text since the first row in the column is in fact text (using File/Open.) Using your information below, I was able to give the consumer of the data the choice to eliminate the header record or include an integer column that has to be typed as DATE in Excel after the file is opened.
Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul Buckley Sent: Thursday, November 19, 2009 1:08 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem Importing an Excel File with File Gateway Every day is a new day to learn something new. First let me say thank you to everyone that gave me suggestions and asked questions. Let me also thank John from RBTI for suggesting I import the '20 Jan 2009' date into a text field and convert it. What I discovered when I did that is that the date came is as an integer, 39833. In fact all the dates were 5 place integers. After some Google searching I found that Excel uses an "odd" 5 digit Julian day/date storing method. The number, in this case 39833, is the number of days since 12/30/1899. Once I knew this I was able to import the "date" column into an integer column in Rbase and add a computed column, (addday('12/30/1899', integer value)). In my example it converts (addday('12/30/1899', 39833)) to 1/20/2009. Thank you John and thank you all. I hope someone else can learn from my morning and not have to re-do this down the road. Paul Buckley -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul Buckley Sent: Wednesday, November 18, 2009 7:22 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem Importing an Excel File with File Gateway This is a weekly task and this is the only/best format they can download the files from the source. Paul Buckley -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul InterlockInfo Sent: Wednesday, November 18, 2009 6:58 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem Importing an Excel File with File Gateway Is this a onetime shot? or something that an end user has the ability to do when needed? Sincerely, Paul Dewey -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Paul Buckley Sent: Wednesday, November 18, 2009 5:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem Importing an Excel File with File Gateway Mike, In looking at the CSV file, the date is stored as 20 Jan 2009. Now what, can we handle this? Thanks, Paul -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MikeB Sent: Wednesday, November 18, 2009 10:29 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Problem Importing an Excel File with File Gateway Why don't you export a small sample from excel to a CSV and see how the format is actually arranged first. ----- Original Message ----- From: "Paul Buckley" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Wednesday, November 18, 2009 10:04 AM Subject: [RBASE-L] - Problem Importing an Excel File with File Gateway > Can I get someone else to confirm this before I submit it to RBTI? I'm > trying to import an Excel XLS file using File Gateway in eXtreme 9.0, ver. > 9.0.1.11111. The Excel file has a date column and the dates are entered > as > mm/dd/yyyy but formatted to display as 'day month year' (i.e. 20 Jan > 2009) > . I have changed the gateway setting to DMY but the date data does not > import (actually tried all options for format). If I change the format of > the Excel column to mm/dd/yyyy it imports correctly. > > > > Could someone please test this for me before I submit it? It's very > likely > I'm just missing something. I've also tried this in the latest V8 build > and > get the same results. > > > > Thanks in advance, > > Paul Buckley > > > > --- RBASE-L ================================================ TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO ================================================ TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE ================================================ TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. ================================================
