Chopping Off Data

  • Steve Jones - SSC Editor wrote:

    There is a note that some data can be cut off, but it can be easy to miss, and if you are busy, you could close it without realizing that it applies to your file. The verbiage isnt completely clear, especially to laymen.

    Interesting. Thanks. Just was curious, because the news article hadn't mentioned whether there was any warning message.

    [font="Comic Sans MS"]Jonathan Gennick
    http://Gennick.com
    Brighten the corner, where you are[/font]

  • donald.parish wrote:

    Answer since 2010 is Excel PowerPivot, which let's you have millions of rows in Excel, makes it like a reporting database. Then Power Query in 2013, which gives you good way to get the millions of rows of data. And now Power BI, which makes it pretty and powerful.

    I advise avoiding the use of any desktop tool as part of any production process.

  • Jonathan Gennick wrote:

    Interesting. Thanks. Just was curious, because the news article hadn't mentioned whether there was any warning message.

    When you open a file in an application that was not designed for it, the results of the operation, in IT parlance, is undefined. It is impossible to build a system of any reliability using components that exhibit undefined behavior. A warning message from such application has nothing to do with this discussion.

  • GeorgeCopeland wrote:

    When you open a file in an application that was not designed for it, the results of the operation, in IT parlance, is undefined. It is impossible to build a system of any reliability using components that exhibit undefined behavior.

    Excel is indeed designed to handle CSV files. They are a supported file type and are mentioned in Microsoft's own documentation. The behavior from loading a CSV file is defined in the documentation. There is no undefined behavior here.

    A warning message from such application has nothing to do with this discussion.

    I disagree. It is natural to wonder how the data loss might have occurred without anyone realizing it in the moment. Was there a warning? Did the user comprehend the warning? These are valid questions to ask.

    I'm able to recreate the error message that Steve mentions. I'm going to surmise that someone pushed through the error dialog without understanding the message, thus truncating the data. I don't know for certain whether that is precisely what happened, but it seems plausible.

    [font="Comic Sans MS"]Jonathan Gennick
    http://Gennick.com
    Brighten the corner, where you are[/font]

  • Jonathan Gennick wrote:

    Excel is indeed designed to handle CSV files. They are a supported file type and are mentioned in Microsoft's own documentation. The behavior from loading a CSV file is defined in the documentation. There is no undefined behavior here.

    Of course Excel can handle displaying a CSV...for a single user. Unmentioned yet are the data conversions that Excel performs to display Excel datatypes, which are extensive. For production processes, Excel is not a satisfactory solution, period.

    Jonathan Gennick wrote:

    I disagree. It is natural to wonder how the data loss might have occurred without anyone realizing it in the moment. Was there a warning? Did the user comprehend the warning? These are valid questions to ask.

    Please pose your questions to the server, which is where the solution should have been implemented in the first place.

  • I agree with you, Steve. I do have one observation and one question.

    Where I currently work there are a LOT of Excel and MS Access applications out there. I'm working on a team to address this issue, but it's a lengthy process due more to our project management rather than the work that would be involved.

    My question involves SSRS. It's been my experience, for many years (longer than I've been in my current work) that SSRS does allow exporting reports to Excel, but they're only in XLS format. Why doesn't SSRS allow exporting to XLSX?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • I can only assume that the controls they are using in SSRS are old and don't have this capability and MS is reluctant to update this. Since XLS is upgradeable to XLSX in Excel, maybe they think this isn't worth adding.

     

  • I never supported data in Excel or ANY application which used and stored data 'offline' that could be lost by local machine failure or lack of regular backups.  It was understood that it was a case of 'use at your own risk'. Never exported data for users to have offline at all.  All was done by accessing server DB systems even for reporting.

    I just didn't need those headaches

    Rick

    One of the best days of my IT career was they day I told my boss if the problem was so simple he should go fix it himself.

  • Hi Rod, on our local instance of SSRS 2016, .xlsx is the only Excel export option available.

  • Steve Jones - SSC Editor wrote:

    I can only assume that the controls they are using in SSRS are old and don't have this capability and MS is reluctant to update this. Since XLS is upgradeable to XLSX in Excel, maybe they think this isn't worth adding.

    That makes sense. Sort of the "lowest common denominator".

    Kindest Regards, Rod Connect with me on LinkedIn.

  • GeorgeCopeland wrote:

    Hi Rod, on our local instance of SSRS 2016, .xlsx is the only Excel export option available.

    Oh, interesting, George! We are working with older versions of SSRS, so that must be the issue, in my case. Hopefully we'll upgrade to SSRS 2016 at some point.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Jonathan Gennick wrote:

    The article says that "When a CSV file longer than that is opened, the bottom rows get cut off and are no longer displayed."

    Did Excel provide any warning that the data was ignored? Or did Excel just silently proceed as if nothing was wrong?

    Just am curious.

    I've always gotten an error when something was too big to load.  It would dutifully load the first million rows and the popup an error saying that not everything had been loaded.  Even if a VB-baked macro is being used for the load, that means that it IS a trappable error and there's really no excuse for this problem.

    This also smacks of "If you want it real bad, that's usually the way you'll get it".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • <rant>What's so unforgivable, as a UK tax payer, is that this has cost in the region of £12Billion.

    (I can forgive the users, and the [probably] very small team of actual techies - who are probably paid peanuts and ignored)

    Who the hell is allowed to cream off that amount of money in some tasty contract and thinks it's acceptable to have a bloody spreadsheet with no validation anywhere in the mix?

    A team of a dozen, half-competent data-professionals and developers could have done a better job in a few weeks, for a few hundred grand, than whatever that useless shower of consultants and "old school network" bottom feeders have cobbled together over the last few months! </rant>

    • This reply was modified 1 year, 1 month ago by  david.edwards 76768. Reason: Adding missed punctuation, through ranty typing :)

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • Rod at work wrote:

    I agree with you, Steve. I do have one observation and one question.

    Where I currently work there are a LOT of Excel and MS Access applications out there. I'm working on a team to address this issue, but it's a lengthy process due more to our project management rather than the work that would be involved.

    My question involves SSRS. It's been my experience, for many years (longer than I've been in my current work) that SSRS does allow exporting reports to Excel, but they're only in XLS format. Why doesn't SSRS allow exporting to XLSX?

    It depends on the version of SSRS - using SSRS 2016 or higher will export the data in XLSX format.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 14 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply