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
>
>
>
>
