October 19, 2020 at 4:00 pm
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]
October 19, 2020 at 4:01 pm
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.
October 19, 2020 at 4:21 pm
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.
October 19, 2020 at 5:36 pm
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]
October 19, 2020 at 5:46 pm
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.
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.
October 19, 2020 at 5:49 pm
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.
October 19, 2020 at 6:02 pm
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.
October 19, 2020 at 6:03 pm
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
Disaster Recovery = Backup ( Backup ( Your Backup ) )
October 19, 2020 at 6:07 pm
Hi Rod, on our local instance of SSRS 2016, .xlsx is the only Excel export option available.
October 19, 2020 at 7:13 pm
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.
October 19, 2020 at 7:14 pm
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.
October 19, 2020 at 7:15 pm
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
Change is inevitable... Change for the better is not.
October 19, 2020 at 8:00 pm
<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>
"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?
October 19, 2020 at 8:13 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply