Alternate way to export query results?

Joseph Zwick

Hello,

Does anyone know if it is possible to copy the results from a query and paste them into excel, as opposed to doing an export? It seems like exporting them takes wayyyy too long and if I am spending a day running queries waiting for them to export takes up about 20% of my time.

I previously used SQL at my old job for querying and copying and pasting the results worked really well.

Thanks in advance!

Joe Z

JoAnn Strommen

@Joseph Zwick, are you exporting using that function in the query? Or are you taking query to Export module? Exporting within query (disk with arrow icon) at the top of query window should not take that long. (yes, I can hear the murmurs, query is a grouping tool, but at times it does provide all the data one needs.)

Karen Stuhlfeier

An Export will remove duplicates. For some queries where I know that there aren't duplicates I sometimes export directly from the query screen. I have queries with the types of Fund and Campaign that this works really well for, but for any type of constituent query I use Export.

Faith Murray

The export in Query results only gives duplicates if one of the fields in Criteria or Output can have multiple entries. If all Criteria and Outputs are only able to hold one value, then there will be no duplicates. For example, if outputting “All Addresses”, a constituent may have multiple addresses, and so you will get one line in the Results for each address on a constituent's file. However, if using “Address Processing”, each record only has one Primary+Send Mail address, and so you will only get one line in your Results.

What this means is that, as @JoAnn Strommen says, sometimes you can get away with exporting right out of query, provided you stick to specific safe fields: such as (but not limited to) ID, Name, Processing Address, Primary Addressee, Primary Constituent Code, Last/First/Largest Gift, and any of the Gift Summaries. Stay away from fields such as Phone, non-primary Constituencies, Solicit codes, plain old “Gifts”, Actions, etc. If these latter types of fields are needed, then you must use the main “Export” tab (not under Query) to output your file.

Heather MacKenzie

I've tried it, years ago now, and had that not work. But perhaps it's been updated and does it now? Remember that right click is your friend, and can reveal a menu that's not available anywhere else of other options. Try it and let us know!

Shane Littleton

Joseph, you may be familiar with this already, but if you are BB hosted, the Blackbaud File Hosting is supposed to be quicker to save to or import from, versus saving or importing from a local file. For most exports, that won't make a huge difference, but it may make an impact if you are exporting a large amount of data at once.

Tangentially, my Blackbaud File Hosting has been acting up this past week, so I hope it's not on the fritz for other people too!

Jeannie Goings

@Faith Murray well said. You should consider writing for or becoming an explainer of all things Blackbaud.

Melanie Cardullo

You are able to export directly from the query in excel format.

Madeleine Holdsworth

I agree. I use the export to Excel directly from query only for very simple queries. For larger or more complex queries, I always take it to Export. Once you have your exports set up, you can also revise those for fields you want to add or remove and just save a new copy. Sometimes I dump in a query and if there are fields I don't need from the export I just delete those in the Excel file. Way faster than fooling with a new Export set up. We just moved to NXT hosted this year and I find the exporting much faster with large data sets. In a non-hosted environment, depending on your organization's network, it can be painfully slow.

Faith Murray

@Madeleine Holdsworth, we had the opposite experience. Migrating to NXT slowed all our processes, exports, and printer integrations down. Probably because we have an excellent IT staff on campus who previously dedicated an entire server to our database. And because we have to deal with rural broadband. Sigh.

Still, it's definitely been worth being cloud-hosted during covid shutdowns!

Madeleine Holdsworth

Interesting comment about your server - we also had an entire server devoted to our RE application when we were in the nonhosted environment. However, speed was always an issue. It's so much better now. It may depend upon whether or not you're in the Azure environment with Blackbaud (we are not).

Faith Murray

That could be. We are in Azure. Interesting thought… ?

Darrel Giesbrecht

BTW, you should know that, if you are in the Azure environment, anything exported from RE to the hosted files Tenant File Share (F: folder) will be available to anyone with an NXT logon.

Martin Drenth 2

@Joseph Zwick it might be a good idea to create a queue and place all the queries you need there for export. Then let it run in the night. The next morning they are waiting for you…

NB: export it to the Tenant File Share

NB2: better to have a (newly created) TREQueueUser perform the tasks than run it with your own login