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

[robertw]

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