[Year 12 Its] Re: off topic (a little) sharing data betweendatabases and spreadsheets

[Robert Timmer-Arends]

I'm beginning to think that this problem is confined to Excel users whose
first name is Robert!

Regards
Robert T-A
Brighton SC

----- Original Message -----
From: "robertw" <robertw at lakessc.vic.edu.au>
To: "Year 12 Information Technology Systems Teachers' Mailing List"
<is at edulists.com.au>
Sent: Wednesday, May 18, 2005 2:40 PM
Subject: [Year 12 Its] Re: off topic (a little) sharing data
betweendatabases and spreadsheets


> I had similar problem with Cases or First Class imports for Markbook.
When
> I imported into Excel to massage the data to be transferred, it would take
> the old style student keys eg MAR01 and turn them into Mar-01, ie the
first
> of March.  Faced with such tenacious helpfulness, I gave up trying to
switch
> it off, and I resorted to U-Edit (a "no nonsense" powerful text editor,
use
> it and you will not return to Notepad in a long while) to do all my data
> massaging as well.
>
>
> Cheers, RobWard, Lakes Entrance Secondary College
>
>
> Michael Walker writes:
>
> >>>> Rob Attrill<attrill.robert.j at edumail.vic.gov.au> 05/16/05 02:34pm >>>
> >>Sort of off topic but this seems the most likely place to get an answer.
> >
> >>Why would an excel  spreadsheet which receives data in a .txt file from
a database correctly import data such as 11B, 11C, 12B or 12C, but
stubbornly receive 11A as .485333333 and 12A as 0? It is clearly importing
it in a numeric form despite all attempts to format the receiving cells as
text. Obviously, I can find plenty of "can do" fixes (using an apostraphe to
precede the data, etc), but the issue itself is bugging me.
> >
> > Hi Rob,
> >
> > Looks like the data is importing as numerical time. For example 11AM
calculates to 0.4583333333333 and 12AM is 0. Excel calculates date and time
as number of days from a reference date (1/1/04 I think) and time as a
fraction of a date (hence 11am being 11/24 or 0.458333333...). No idea how
this is affected by your importing but it seems that for some reason Excel
is ignoring your desire for the column to be text and converting the time to
a number.
> >
> > _______________________________________________
> > http://www.edulists.com.au
> > IT Systems Mailing List kindly supported by
> > http://www.vcaa.vic.edu.au - Victorian Curriculum and Assessment
Authority and
> > http://www.vitta.org.au  - VITTA Victorian Information Technology
Teachers Association Inc
>
> _______________________________________________
> http://www.edulists.com.au
> IT Systems Mailing List kindly supported by
> http://www.vcaa.vic.edu.au - Victorian Curriculum and Assessment Authority
and
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers
Association Inc