|
I was updating the queries to generate a monster report (~33k rows, 1400 columns, 46m cells of data) and after seeing the output file wasn't the same size in the new version needed a way to compare them and see where they were different. Since Excel had no problems opening the file, or a file that had both versions of the report pasted into different tabs I figured I'd just do a 3rd tab to compare them. So I wrote my basic formula =tab1!A1=tab2!A1 selected all 46m cells on the 3rd tab and hit Ctrl-V figuring I'd have an searchable diff to look at where my results changed in a few seconds.
It took 50 minutes to complete, and peaked at 14GB of ram consumed and finished out at 12gb (only 2 after a close/reopen). The file with the formulas in it was also laggy as hell, although I managed to solve that by pasting the comparison as values into a 4th tab and deleting the 3rd. But I still need to figure out what actually went wrong with the report itself. 
Proving once again that age old bit of wisdom: I had a problem. I used regular expressions Excel, now I have two problems. 
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
Does a WinMerge or BeyondCompare not help?
I find them quite powerful, at least to find the differences in very similar files.
If a lot of differences, then they can suck as well.
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
Do either of those do a good job of showing where within a line things changed? >1400 columns means that just telling me that line 223 is different isn't that helpful; unless they're CSV aware and can tell me what column is different too I'm not sure that any programmers diff tool would be particularly suitable.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
Does it matter?
If they tell you "lines 234, 567, 1999, and 8812 are different" that knock you down to four lines: extract those to a file (CSV? Text?, XLSX?) as values and compare them - you get the actual differences and you can refer back to the original sheet to find out what the formulae were that generated them.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Compared to being able to see at a glance after finding the first 'FALSE' value that columns EG-EO (among other groups) is immediately useful in a way that getting thousands of changed lines is not (it tells me what went wrong, if not why). If I knew Excel was going to faceplant, and I knew there was going to be a huge number of rows different, I probably could've fumbled around line by line and found what the failure pattern was, likely spending more time at it than it took excel to chug through the comparisons and present the data in a way that made the problem obvious.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
To be honest... I have never had such long lines.
In what I have done until now, the Beyond Compare (paid product) does all what I need, pretty nicely.
WinMerge is free for home and does a lot, but sometimes is a bit weak.
The biggest problem I see is what I said... if the difference level is small... then they will help.
If the differences number is medium / high... then it will get messed up and probably won't help that much.
M.D.V.
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
yeah, this report's something else.
It's big enough to be the reason why I originally got a copy of MS office despite us mostly being a google docs company at the time. Google wouldn't open the report at all; LibreOffice would but it was too large for it to work well (things like filtering ranged from laggy to timing out and failing all together) with the standard size report; and that was before it grew above Calc's 1024 column limit and it joined Google in noping out.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
Well... then I would give a shot to the trial version of beyond compare. The trials are 30 days limited but as far as I know they are fully featured.
It does have a left overview panel, and the vertical divided screen where both compared files are. In the main panels the differences in the selected lines are remarked in red.
And there is the option of "only show differences" which would remove all noise (matching parts) from both panels.
What (after the first amount of time to process the differences and so on) I suppose it will reduce the load drastically.
But again... the more differences there are, the messier can get the comparison.
M.D.V.
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
|
It's been long enough since the last time I had to do anything with VBA that I can't get it to run, no.
There's nothing in that code snippet to actually declare a macro for excel to run; and when I slapped a `Sub Macro1() ... End Sub` around all the code before the functions it promptly barfs because something on the `WScript` line is null.
Edit: In any event, I'm 99% sure I fixed the copy/pasto that caused the missing data from the sql queries, and the bug/not finished in the final report generation code that still created sets of columns for all possible X instead of the inadvertently truncated list of Xes actually being sent to the database; and will let Excel whir my fans for an hour over lunch in a bit doublechecking that I actually did fix it all.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
modified 23-Nov-20 11:31am.
|
|
|
|
|
VBA is very powerful. I picked up some new VBA knowledge today
diligent hands rule....
|
|
|
|
|
We do a lot of importing from spreadsheets. Last week a colleague helped a customer setup an import for a much smaller file that you are dealing with. (~7k rows and 20 columns) He had created a nice little pivot grid with subtotals and totals so we'd have something to compare to.
The import worked like a charm but checking the total against the pivot grid showed differences. Highlighting the column for the values we were after also did not match what was imported. It was off by a small amount...still enough for the customer and the colleague to think that something must be wrong with the import/software so they generated a support ticket.
It took way too long to figure out that around a dozen or so cells had numbers formatted as text which were not calculating in the pivot grid or in the automatic total in the toolbar. The import was actually correct!
This file in question is an exported report from another boh system. (not ours) When the customer was made aware of the problem, the first question was how to easily find and fix those cells. They weren't real happy with the answer I gave...scroll through the sheet and look for the cells that are left-aligned, click on them one at a time and convert to number. It's what I did and took around 5 minutes...really, it's not my problem...you don't have to fix it at all, just trust the import which was right all along! Of course the next question was related to why didn't the import just fix the file for them.  I can't win! 
"Go forth into the source" - Neal Morse
"Hope is contagious"
|
|
|
|
|
I first read that title as "The Joys of..."  Oh, nevermind.
|
|
|
|
|
Slacker007 wrote: I first read that title as "The Joys of..." Oh, nevermind.
If you were thinking about the joys of ![Elephant | [mastadon]](https://www.codeproject.com/script/Forums/Images/elephant.gif) ing, it was ed anyway. Close enough.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
You should have to use a Java ETL tool like Pentaho/Kettle. 14GB just for reading the "distributed registry" aka Spring+ config files.
|
|
|
|
|
1400 columns!?
What human will even be able to read and interpret that much data? 
|
|
|
|
|
Sander Rossel wrote: 1400 columns!?
The changes I was debugging were to allow scaling down the report size at customer request; after years of insisting on adding more stuff to it. 
Sander Rossel wrote: What human will even be able to read and interpret that much data?
My understanding is that they fold, spindle, and mutilate out a subset of columns containing whatever they're actually interested in at the moment; and then feed it into a stats program for a second round of folding, spindling, and mutilating in order to get a result of "we did good last year, give us another grant to keep doing this for next year".
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
I was thinking about how my development life has changed a little in the last decade. No longer is it just Visual Studio. It's Visual Studio, and Azure DevOps, and SQL Server management studio, and pgAdmin, and Redis desktop manager, npm and webpack, Chrome and DevTools, CodeProject and SO, remote desktop, PowerShell, and all the ridiculousness around hosting, domain management and DNS.
That's a lotta stuff, which I guess I could group roughly as
IDEs
Build tools
Debug tools
Data storage management
Remote management tools
Source code control
Library management systems
Hosting services and tools (including backups)
Community support
[also: Office + online Office (MS + Google)]
[also: Security apps (password managers / authorisation apps)]
[also: Chat / video conference apps]
[also: graphic design apps]
Coffee
Is this normal? Anything else you guys are generally using day to day (or no longer using these days)?
cheers
Chris Maunder
modified 23-Nov-20 12:43pm.
|
|
|
|
|
Yeah, the toolchain has grown a lot in recent years.
For me these are the ones I use the most regularly:
IDEs:
Visual Studio 2019
Visual Studio Code
Programmer's Notepad (used as a scratchpad / quick editor)
Data:
Navicat for PostgreSQL
Redis
Source Control:
Sourcetree
Azure Devops for builds and deployments
Browsers:
Edge (Chrome), Chrome
Insomnia and Swagger for testing Web APIs
Others:
WSL2 to SSL into servers
WinSCP
Comms:
Outlook (stuck with 2010 due to Org)
Slack
Whatsapp
Zoom
Misc:
Paint.net for screenshots
modified 23-Nov-20 10:39am.
|
|
|
|
|
Based on the role/work, I think it pretty much covers all but one:
Collaboration tools - Outlook, Slack
|
|
|
|
|
These days?
SSMS
Notepad
CSC
Plus the utilities I develop with them.
|
|
|
|
|
I'd add Excel, PaintShop Pro, and Outlook plus a couple of in-house tools for monitoring odds and ends.
Oh, and Expresso.
And Fusion 360, but that's more personal than work related.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
As a simple C++/MFC desktop developer (no DB, no online/web), I'm relatively conservative.
Just Visual Studio and an old version of Araxis Merge and Visual Assist and ReSharperC++ and notepad++
And jira and git and azure devops.
This has has been my setup for many years (except azure devops which is new-ish)
I'd rather be phishing!
|
|
|
|
|
our shop just upgraded from writing in the dirt with sticks, to stone tablets. big win, if you ask me!! 
- Visual Studio 2019
- SQL Server
- DevOps using Git repos. Continuous integration with DevOps as well as sprint planning, etc.
- Visual Code
- LinqPad 5
- Learning AWS
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
