+ Post New Thread
Results 1 to 7 of 7
MIS Systems Thread, Report Design - Export To Excel converts numbers as Text in Technical; I'm in report designer and I've built a behaviour report that exports the data to excel. The only issue is ...
  1. #1

    Join Date
    Apr 2013
    Location
    Nottingham
    Posts
    24
    Thank Post
    11
    Thanked 6 Times in 2 Posts
    Rep Power
    4

    Report Design - Export To Excel converts numbers as Text

    I'm in report designer and I've built a behaviour report that exports the data to excel.
    The only issue is that the numeric data (attendance percentages, count of number of incidents for the student) exports as text.

    Excel gives me a warning exclamation mark / shreik saying that the number in the cell is formatted as text or is preceded by an apostrophe.

    I can correct the columns when in excel, but if someboody else is running the report from the SLT, then I don't expect them to have to start formatting cells, especially if the want to do analysis on the counts e.g. pivot tables

    Any ideas how I can force the report design to output the fields as numbers rather than text?

    Many thanks

    Regards



    Phil

  2. #2

    Join Date
    Mar 2011
    Location
    Bristol
    Posts
    119
    Thank Post
    24
    Thanked 24 Times in 20 Posts
    Rep Power
    17
    Hi Phil,

    This is actually built into the macro that runs when reports are exported to Excel.

    Have you done much Excel macro programming? The only way to do it (I think) is to amend the report template to either convert the text back to numbers [with something like 'For Each Cell in Range, Cell.Value = Val(Cell.Value)'], or amend the section of code in the standard template which converts the numbers to text.

    There is a great document that has been doing the rounds for years which explains how to create your own custom excel report templates, but it requires some VBA knowledge. Let me know if you want this, and I can dig around for it (if someone else doesn't beat me to it.)

  3. Thanks to Ecclesbury from:

    bwfc_nottingham (12th April 2013)

  4. #3

    Join Date
    Mar 2011
    Location
    Bristol
    Posts
    119
    Thank Post
    24
    Thanked 24 Times in 20 Posts
    Rep Power
    17
    p.s. in case you're wondering why Capita convert all numbers to text in the output, the following comment is included in the standard template code:

    ' GERAINT 11/8/06: Additional code to enable correct interpretation of dates.
    ' We define every column as being a TEXT type column - so we avoid any mis-interpretation

    Incidentally, if you wanted to amend the code directly you could do it under here, but I tend not to, as I don't fully understand why they have done it, so wouldn't want to break anything. I also never amend the actual standard template, just take a copy of it to create my new templates.

  5. Thanks to Ecclesbury from:

    bwfc_nottingham (12th April 2013)

  6. #4

    Join Date
    Apr 2013
    Location
    Nottingham
    Posts
    24
    Thank Post
    11
    Thanked 6 Times in 2 Posts
    Rep Power
    4
    Ecclesbury

    Thanks for the reply, I've only been in my post as data manager for 3 weeks, my background is as a Business / Sytems Analyst for big PLC's

    I must say it's very unusual for numeric data to be exported as text.

    In my previous roles I've exported data from databases (Oracle, SQL Server), either via native SQL or via utilities such as MSQUERY, MS ACCESS that utilise ODBC drivers and I've never had any 'dodgy cell' conversions.

    I've not done much work with templates, so I need to do a bit of research on that.

    I've recorded macros in excel, but never had the need to write them in VB. Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.

    In the short term I will just convert them manually, but the manual you refer to would be useful.

    While I'm finding my feet in the new job I tend to post the same question on here and on Capita's own forum.

    Thanks for the reply

    Forums are wonderful places for exchange of ideas and tips !

    Phil


    Phil
    Last edited by bwfc_nottingham; 12th April 2013 at 11:02 AM. Reason: another typo

  7. #5

    Join Date
    Sep 2006
    Location
    Reading
    Posts
    177
    Thank Post
    14
    Thanked 41 Times in 39 Posts
    Rep Power
    22
    Quote Originally Posted by bwfc_nottingham View Post

    Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.



    Phil
    Could not agree more, I know I have made errors transferring and analysing in Excel and try to keep it to the absolute minimum.Captita still have some work to do in this area.
    As to Excel the latest version does a good job treating text as numbers but if you do not like macros or VBA then you might find this ASAP Utilities for Excel - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools useful.

  8. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by bwfc_nottingham View Post
    Ecclesbury

    Thanks for the reply, I've only been in my post as data manager for 3 weeks, my background is as a Business / Sytems Analyst for big PLC's

    I must say it's very unusual for numeric data to be exported as text.

    In my previous roles I've exported data from databases (Oracle, SQL Server), either via native SQL or via utilities such as MSQUERY, MS ACCESS that utilise ODBC drivers and I've never had any 'dodgy cell' conversions.

    I've not done much work with templates, so I need to do a bit of research on that.

    I've recorded macros in excel, but never had the need to write them in VB. Reading these forums and the one's on Capita, I do worry that there's a lot of data manipulation and massaging outside of SIMS or the core MIS systems i.e. 'desktop computing' is occurring. If you are not careful you can end up with errors in your 'desktop computing' and making wrong decisions based on the contents.

    In the short term I will just convert them manually, but the manual you refer to would be useful.

    While I'm finding my feet in the new job I tend to post the same question on here and on Capita's own forum.

    Thanks for the reply

    Forums are wonderful places for exchange of ideas and tips !

    Phil


    Phil
    You're right, there is much done externally, and reliance on manual intervention is a pain. If you want others to use the report i'd recommend recording a macro into the template, the guide @Ecclesbury referred to is here Edit Excel Output

    I have a few reports that use this method and it's useful. The VBA is just the macro code, and you will need to tweak it no doubt to get it to work. It's very useful when creating pivot tables of your exported data.

    All excel based reports, first go out to a csv / text file and then are read back in to Excel using the default macro.

  9. #7

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    244
    Thank Post
    23
    Thanked 36 Times in 27 Posts
    Rep Power
    20
    My experience back in more commercial environments was that businesses, sites, and sometimes individual users, often land up using a mixture of date locale settings, so that converting dates (text) to dates (date!) could cause confusion, especially if a date got mangled through UK-text-US misconversion or similar.

    Rather than deal with people complaining that the dates were coming out wrong, I suspect the chosen solution was to format them as text, so that no-one could complain the wrong data was being output!

    The "text" format is a pain, though, as it also fails on some versions of Excel when the text is more than 1023/1024 chars long. ironically, formatting this text as "general" avoids the problem. It also leaves Excel to determine that the text date literals are, in fact, dates, and to format them as such. As long as it won't cause any issues at your site, creating a new default template with the format-as-text line removed can make life a *lot* easier, and I've yet to be able to find a test case where it would be a problem.

SHARE:
+ Post New Thread

Similar Threads

  1. Exporting Distribution Lists from AD to excel...
    By mikehollingsworth in forum Scripts
    Replies: 0
    Last Post: 7th December 2011, 11:16 AM
  2. Exporting to excel sheets
    By umass in forum Scripts
    Replies: 1
    Last Post: 14th October 2011, 03:15 PM
  3. [MS Office - 2010] Excel - Convert text to lowercase
    By FunkMaster_J in forum Office Software
    Replies: 7
    Last Post: 2nd June 2011, 10:45 PM
  4. [MS Office - 2010] Excel: Link to send current spreadsheet as attachment
    By RabbieBurns in forum Office Software
    Replies: 5
    Last Post: 13th August 2010, 08:00 AM
  5. Exporting timetables from SIMs to Excel
    By CaterinaAnna in forum MIS Systems
    Replies: 18
    Last Post: 12th January 2010, 03:28 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •