A brief overview of mechanisms to "export to excel" - which do you use, which do you prefer?

Question

A brief overview of mechanisms to "export to excel" - which do you use, which do you prefer?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

allmhuran (5/22/2012)
Jeff, In a perfect world where I wasn't already working lots of overtime and surviving almost entirely on a neurochemical dependency on stress, spending the time to put together some pretty management proposal to describe or prove something that seems obvious and trivial to me would be the ideal solution - except for my general lack of interest in writing pretty management proposals for obvious and trivial things. You're preaching to the converted on the subject. Of course, I'd also be the one who would have to plan and build it, and, in my role as DBA plus senior programmer + general problem solver for both the business and the development team, I'm currently well over 100% allocated on production support alone. But I'd do it if I was given the infrastructure.
As it is we have project managers, management panels, task allocation and reporting, and so on, designed to figure out things like ROI. I provide them with information from the front lines and put in suggestions as to thing they should be focusing on while doing all of that stuff. What they actually decide... <shrug>
In any case, we're off topic now. My ideal solution would be something like this:
Application presents users with a list of available exports (list predicated on authorization to run)
User selects export, provides any parameters, and hits the go button. User is prompted for an existing file locaton and data is exported to that file (not tab/comma separated, to avoid embedded special char/newlines etc)
Because of the requirement of export direct to excel without a CSV/TXT intermediary, the technical implementation options are limited. Any "export to text" functionality (sqlcmd, bcp, etc) are eliminated, leaving office automation with low level nested loop style write-per-cell, or export in xml (xslx) format, or export via programmatic SSIS Of the three, I like programmatic SSIS the best.

No... we're not off topic. You asked for my opinion and I gave it. Then you argued with it and I tried to give you some ammunition because I thought you might actually be interested. Now you're arguing even more about it. When you ask for opinions, you should treat it like a poll and stop arguing with people willing to give you an opinion on one of the selections that YOU listed. 😉

I suspect that a part of the reason why you work so much overtime is because you're so busy meeting everyone else's requests for reports that they could be building themselves. :w00t: Of course, THAT's just an opinion, as well.

--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)

Don Halloran SSCertifiable Points: 5937 More actions · Answer 2

Jeff Moden (5/23/2012)
No... we're not off topic. You asked for my opinion and I gave it. Then you argued with it and I tried to give you some ammunition because I thought you might actually be interested. Now you're arguing even more about it. When you ask for opinions, you should treat it like a poll and stop arguing with people willing to give you an opinion on one of the selections that YOU listed. 😉
I suspect that a part of the reason why you work so much overtime is because you're so busy meeting everyone else's requests for reports that they could be building themselves. :w00t: Of course, THAT's just an opinion, as well.

Whoa whoa whoa whoa WHOA there Jeff. Bad week?

I'm trying to say I agree with you. I definitely like the separate, user-queried-after-some-training, reasonably-up-to-date-copy-of-production-data solution. That's why I included it in my original post.

But the original post is more about the mechanisms used to export data to users. Not the source, or who is doing it, but the actual solution design used in getting the data out. In the architectural version, there is no solution design for IT... the burden of figuring out how to actually get the data into the users' tool of choice is up to each user, as is the actual creation of the queries. That's a huge load off IT for BAU style operations. I agree, OK? I like that solution. I've pushed for that solution. But it requires more buy in than I can get.

No, I don't spend my whole time extracting user data. That's what the code monkeys are for. But I can see a lot of time being spent by the code monkeys that could be better spent elsewhere, I have some knowledge of the various mechanisms out there (ie, the list in my OP), and I was curious how other people might be approaching this common-but-annoying day to day task.

GSquared has provided a description that I expect applies to a heck of a lot of businesses out there, ie, "lots of different options from the list". I do it too, I always have, I'm sure you have as well... but I've always disliked the distributed nature of the code base without any clear central repository of raw data exports from which everything can be managed.

GSquared SSC Guru Points: 260824 More actions · Answer 3

allmhuran (5/23/2012)
GSquared (5/23/2012)
I've got different solutions for different users.
Yes indeedy, your rundown mimics very closely the various options currently used where I work, and indeed at pretty much everywhere I've ever worked 🙂
For the "copy from SSMS paste to excel" - I avoid the out of memory exceptions by copying in batches of (n) columns at a time.

I don't like running a multi-million row query that will return results to my screen in SSMS. Too resource-intensive. Add in copying even a single column to the clipboard and you're looking for trouble, even with the kind of overpowered workstation I use. So I try to avoid that in those cases.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

allmhuran (5/10/2012)
...
COMMAND LINE BASE SOLUTIONS
OPTION: output to text file using bcp/sqlcmd
ISSUES: prone to special character problems, like tabs, commas, newlines etc embedded in large text coulumns such as memos, notes, etc. Excel produces an error when attempting to open text files given an .xlsx extension (xls gives a warning but does work)
OPTION: output to text file using bcp and a format file, give the text file an xls/xlsx extension
ISSUES: Excel produces an error when attempting to open text files given an .xlsx extension (xls gives a warning but does work)
...

What I've done in the past is:

#1 First, for security reasons, setup an account specifically for the purpose of allowing the user to submit ad-hoc queries. For example: db_datareader or select permission on specific tables/views.

#2 The user can then submit queries from an application to SQL Server using .NET System.Data.SqlClient.SqlBulkCopy class or the BCP shell command and output to TAB delimited files with the extension TXT or TSV.

#3 The file can then be opened in Excel manually by user, or let the application automate that step for them using Excel Automation or VB / PowerShell scripting.

The text file should NOT be given the extension XLS/XLSX, that confuses Excel.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

allmhuran (5/24/2012)
Whoa whoa whoa whoa WHOA there Jeff. Bad week?

Heh... yeah... actually, it was. Considering the cruddy state of mind I've been in because of a lot of reasons, there were two ways for me to read your email and I read it the wrong way. I even missed where you said...

You're preaching to the converted on the subject.

Please accept my apologies for raising hell and propping it up on a stick. :blush: