Excel errors? Gotcha covered boss!



  • We hire folks to build a data warehouse for us. You all probably know the drill: Copy tables from systems such as our HR application, finance, production etc, convert into a fact/dimension model, load into cubes, show it in a report.

    Not all our inputs stem from relational databases. In any (semi-)professional environment, one sooner or later comes across an Excel file that needs to be added to the data model. And of course, Excel doesn't enforce correct user input. So when dealing with erroneous Excel entries, a developer is placed before a choice. Some may have the code throw an error, some may even *speak* with a client to say that 'the date entered on line 312 is malformed and could you please fix it'.

    Not Jim. Jim made sure the customer had a smooth ride. After copying the entire Excel file into a staging table, he used this view to copy the data to the data warehouse:

    CREATE VIEW [dbo].[V_SA_BL_D_CancelledSessions] AS
    select
        N'01' as    Organisation_Bkey
        ,convert(int, convert(nvarchar, convert(date,
           case
              when number = 43 then '15-4-2009'
              when number = 44 then '15-4-2009'
              else [date reported] 
           end, 105) -- format dd-mm-yyyyy
        , 112)) as DateReported_SKey
        ,convert(date,
           case
              when number = 43 then '15-4-2009'
              when number = 44 then '15-4-2009'
              else [date reported] 
           end, 105) -- format dd-mm-yyyyy
         as DateReported
        ,convert(int, convert(nvarchar, convert(date,
           case
              when number = 43 then '16-4-2009'
              when number = 44 then '17-4-2009'
              when number = 365 then '3-3-2011'
              when number = 699 then '19-7-2012'
              else [Date Session] 
           end, 105) -- format dd-mm-yyyyy
         , 112)) as Date_Session_SKey
    
     ... etc ...</pre><br>
    

    Yes. This developer thought the best way to deal with dates entered incorrectly was to do a row-based replace of the dates concerned. This of course worked like a charm, until the next error was typed into the sheet.

     



  • @steenbergh said:

    Excel doesn't enforce correct user
    input
    How would Excel do that?  Don't get me wrong, I'm not
    defending Excel, it's problems are well documented and I've suffered my fair share of grief using it. However, one of the most basic and fundamental features of Excel is that the user has to specify the format
    of each cell -- number/ currency / date /whatever.. Sure, if people are supposed to enter a date but all the cells are set to something other than a proper
    date format, then people can enter 923432.139080π√x   but that isn't really Excel's fault.



  •  @steenbergh said:

    Filed under: why is the CS editor broken in Chrome?
    Possible answers:

    (a) CS sniffs the user agent, doesn't recognize it because this version of CS is from 2007, and so it doesn't know what to do and just barfs

    (b) The editor seems to work fine in other browsers so maybe Chrome is doing something weird

    (c) CS is shit

    (d) Some combination of all of the above



  • @El_Heffe said:

     @steenbergh said:

    Filed under: why is the CS editor broken in Chrome?
    Possible answers:

    (a) CS sniffs the user agent, doesn't recognize it because this version of CS is from 2007, and so it doesn't know what to do and just barfs

    (b) The editor seems to work fine in other browsers so maybe Chrome is doing something weird

    (c) CS is shit

    (d) Some combination of all of the above

    CS uses TinyMCE for rich text editing and this version is so old it does browser sniffing to determine whether it can do RTF editing.



  • Does a trouble-free RTF editor web control actually exist? Every single one I've ever used manages to fuck something up, usually to do with spacing and cut-n-paste, in every browser except IE. And IE is the only browser that turns soft line breaks like those generated by Notepad's word wrap feature into hard line breaks in pasted text, so you're basically screwed either way.



  • @El_Heffe said:

    @steenbergh said:
    Excel doesn't enforce correct user
    input
    How would Excel do that?

    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine. This, well, not so much.



  • @steenbergh said:

    @El_Heffe said:
    @steenbergh said:
    Excel doesn't enforce correct user input
    How would Excel do that?
    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine.
    OK.  That's fine.  But your statement "Excel doesn't enforce correct user input" implies that Excel is doing something wrong and is partially at fault. (there are a lot of things wrong with Excel but this isn't one of them).



  • @El_Heffe said:

    @steenbergh said:

    @El_Heffe said:
    @steenbergh said:
    Excel doesn't enforce correct user input
    How would Excel do that?
    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine.
    OK.  That's fine.  But your statement "Excel doesn't enforce correct user input" implies that Excel is doing something wrong and is partially at fault. (there are a lot of things wrong with Excel but this isn't one of them).

    Er, there's no such implication, it was merely a factual statement.

    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe *should* eat horse manure?

    In fact, the quote cropped off part of the statement, in full it was:

    "And of course, Excel doesn't enforce correct user input"

    Which, if it implies anything, I would say it implies that this is a correct thing to do.

     



  • @Zadkiel said:

    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe should eat horse manure?

    Perhaps. Is "manute" different than "manure"?



  • @morbiuswilters said:

    @Zadkiel said:
    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe *should* eat horse manure?

    Perhaps. Is "manute" different than "manure"?

     

    Maybe it's lojban for "shit".



  • @Zadkiel said:

    @El_Heffe said:

    @steenbergh said:

    @El_Heffe said:
    @steenbergh said:
    Excel doesn't enforce correct user input
    How would Excel do that?
    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine.
    OK.  That's fine.  But your statement "Excel doesn't enforce correct user input" implies that Excel is doing something wrong and is partially at fault. (there are a lot of things wrong with Excel but this isn't one of them).

    Er, there's no such implication, it was merely a factual statement.

    In fact, the quote cropped off part of the statement, in full it was:

    "And of course, Excel doesn't enforce correct user input"

    Which, if it implies anything, I would say it implies that this is a correct thing to do.

    Nonsense.  If you were looking at an automobile and the salesman says "Of course, this car doesn't fly" it implies that there are cars that fly, or, it implies that maybe you were looking for a car that flies.

     



  • @dhromed said:

    @morbiuswilters said:

    @Zadkiel said:
    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe *should* eat horse manure?

    Perhaps. Is "manute" different than "manure"?

     

    Maybe it's lojban for "shit".

    Well, that's not very nice.

     


  • ♿ (Parody)

    @El_Heffe said:

    @Zadkiel said:
    @El_Heffe said:
    @steenbergh said:
    @El_Heffe said:
    @steenbergh said:
    Excel doesn't enforce correct user input

    How would Excel do that?

    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine.
    OK.  That's fine.  But your statement "Excel doesn't enforce correct user
    input" implies that Excel is doing something wrong and is partially at fault. (there are a lot of things wrong with Excel but this isn't one of them).

    Er, there's no such implication, it was merely a factual statement.

    In fact, the quote cropped off part of the statement, in full it was:

    "And of course, Excel doesn't enforce correct user input"

    Which, if it implies anything, I would say it implies that this is a correct thing to do.

    Nonsense.  If you were looking at an automobile and the salesman says "Of course, this car doesn't fly" it implies that there are cars that fly, or, it implies that maybe you were looking for a car that flies.

    I thought it was a slightly humorous way of pointing out TRWTFs of using excel for something like this to begin with. Of course, CS won't correct your misunderstanding.



  • @El_Heffe said:

    @dhromed said:

    @morbiuswilters said:

    @Zadkiel said:
    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe *should* eat horse manure?

    Perhaps. Is "manute" different than "manure"?

     

    Maybe it's lojban for "shit".

    Well, that's not very nice.
    Well, shit.

     

     

     


  • Considered Harmful

    @boomzilla said:

    @El_Heffe said:
    @Zadkiel said:
    @El_Heffe said:
    @steenbergh said:
    @El_Heffe said:
    @steenbergh said:
    Excel doesn't enforce correct user input

    How would Excel do that?

    It doesn't, and that's the point. When you need to enter in unstructured data (uch as a spreadsheet) into a structured setting (such as an RDBMS), stuff can go wrong. I don't mean to prevent that, but working with the flaws inherent in this process is NOT done in the way described above... If the interface prcess would just throw an error when encountering these malformed input, that would be fine.
    OK.  That's fine.  But your statement "Excel doesn't enforce correct user
    input" implies that Excel is doing something wrong and is partially at fault. (there are a lot of things wrong with Excel but this isn't one of them).

    Er, there's no such implication, it was merely a factual statement.

    In fact, the quote cropped off part of the statement, in full it was:

    "And of course, Excel doesn't enforce correct user input"

    Which, if it implies anything, I would say it implies that this is a correct thing to do.

    Nonsense.  If you were looking at an automobile and the salesman says "Of course, this car doesn't fly" it implies that there are cars that fly, or, it implies that maybe you were looking for a car that flies.

    I thought it was a slightly humorous way of pointing out TRWTFs of using excel for something like this to begin with. Of course, CS won't correct your misunderstanding.

    Nor should it. Correcting misunderstandings is well out of scope for forum software, and the algorithms involved are not reliable enough to deliver the level of quality we've come to expect in this fine software.



  • @steenbergh said:

    And of course, Excel doesn't enforce correct user input.

    Actually, you can come pretty close to enforcing correct user input, at least the way it sounds like you need it. You just need to take advantage of The Data Validation utility, which will allow you to enforce data validation rules on given cells and/or cell ranges. It can even enforce data type rules such as requiring a date to be entered in a given field. If the user tries to enter other data, they get a message saying that the data is invalid. You can even specify the error message that is displayed. Add to that the cell formatting capabilities, and you've come pretty close to what it sounds like you need. Then, just set the appropriate Protect Sheet and/or Protect Workbook settings, and your data entry folk will be able to punch data into Excel all day long without being able to touch the formatting or the Data Validation settings.



  • @morbiuswilters said:

    @Zadkiel said:
    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe should eat horse manure?

    Perhaps. Is "manute" different than "manure"?


    Some days I feel like King Manute, trying to turn back a tide of shit.



  • @dhromed said:

    @El_Heffe said:

    @dhromed said:

    @morbiuswilters said:

    @Zadkiel said:
    if I said 'El_Heffe doesn't eat horse manute' am I implying that El-Heffe should eat horse manure?

    Perhaps. Is "manute" different than "manure"?

     

    Maybe it's lojban for "shit".

    Well, that's not very nice.

     

     Well, shit.

     

    CALL shee



  • @abarker said:

    @steenbergh said:
    And of course, Excel doesn't enforce correct user input.

    Actually, you can come pretty close to enforcing correct user input, at least the way it sounds like you need it. You just need to take advantage of The Data Validation utility, which will allow you to enforce data validation rules on given cells and/or cell ranges. It can even enforce data type rules such as requiring a date to be entered in a given field. If the user tries to enter other data, they get a message saying that the data is invalid. You can even specify the error message that is displayed. Add to that the cell formatting capabilities, and you've come pretty close to what it sounds like you need. Then, just set the appropriate Protect Sheet and/or Protect Workbook settings, and your data entry folk will be able to punch data into Excel all day long without being able to touch the formatting or the Data Validation settings.

    I've actually done that so as to allow users to do bulk uploads/downloads/manipulation of data. Originally the system was using CSV files with no validation, i changed it to use proper xlsx files with data validation and it's been working pretty nicely as an efficient way to do bulk editing without having to rebuild the excel UI in our web application.


Log in to reply