Excel Export - One Click

I'm using DbVisualizer 9.5.1

I used to use Aqua Data Studio.  That program had a feature called "View as Spreadsheet".  I so miss that feature.  Here's an example of what it did.

First, user writes a query and returns a recordset into the grid.

Second, user clicks an icon on the grid toolbar "View as Spreadsheet".

Third, user is brought to a new Excel workbook with the grid (columns and rows) provided.

That's it!  It's oh so simple but oh so nice.  I realize users can export to a file but this is so handy when you want to do a little bit of analytics on some ad-hoc analysis.  Often I don't need to save my results.  I just want to check something out and Excel is a very powerful tool for that type of work.  What Aqua did to marry their tool up with Excel was genius.  I might urge you all to consider this functionality.

If you want the DbVisualizer's implementation to be even better than Aqua's I would urge the process makes two worksheets in the workbook.  The first worksheet would be the grid results.  The second worksheet would be the code used to generate the results.  THAT WOULD BE AWESOME!!!!

http://www.aquafold.com/d10/docs/10.html

I have since moved to a company that just has PgAdmin and I am biding my time to when I can make a pitch for DbVisualizer! One of my favorite pieces of software I have ever used. I even used your command line tool to run SQL for migrating our bank customer data around, and parsed the dbvis.xml for connection info. In retrospect not the smartest way to do an ETL, but DbVis gave me a lot of power and Fifth Third was happy with it. Thanks for all the improvements that you do, and the great forum communication too!

I want to share an Excel macro that does just what I suggested -- moves the query to a new tab named "Query", moves the other columns up, and autofits them, all ready to email.

Sub DbVis_reformat()
'
' DbVis_reformat Macro
' Gets a spreadsheet exported from DbVisualizer ready to email.
' Moves the query to its own tab and autofits the columns.
'
' Keyboard Shortcut: Ctrl+d
'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Query"
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Data"
    Range("A2").Select
    Selection.Cut
    Sheets("Query").Select
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Range("A1").Select
    Range("A1").WrapText = True
    Selection.Columns.AutoFit
    Sheets("Data").Select
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    
End Sub

 Hope others find this useful.

Just wanted to say that I love this feature and have started using "Export Selection to Excel" in place of "Copy Selection As Formatted Text" when I want to paste into emails!

Roger,

Including the query in the spreadsheet is really a best practice, I understand why you did it. It enables people to recreate a spreadsheet any time after it was made. Can I suggest that the checkbox offer the options

• include query above grid [current behavior]
  
• include query on separate tab.
  

Well how about that?!  "Execute Buffer" does exactly what you said it does.  That works for me.  Now I just have to read up a bit on what the difference is between "Execute" and "Execute Buffer".  Something else to learn.  :)

Thank you!

Thanks, Roger. Just wanted to say that DbVisualizer has more features and timesavers than almost any other tool I use. Your programmers should be very proud. Your forum support is great too.

Thanks for the reply, Roger.

What I do is select the top three rows and hit Ctrl+(minus) to delete them so the headers start at the top like a normal sheet. I do this for readability -- it lets me hit Select All and use my keyboard shortcut for "autofit all column widths".

The issue is that I end up doing this every single time I click the button, which is a lot.

I can have workarounds: I can use Ctrl+Shift+Home to select only the query results before I autofit the column widths. I could also use the snippet below as a bookmark all the time and then map a hotkey to open C:\tmp\results.xlsx.

But I know you already have the ability to make it a setting because the ExcelIncludeSqlCommand option exists, so I thought I'd ask.

@set maxrows -1;

@export on;

@export set Filename="C:\tmp\results.xlsx" appendfile="clear" format="xls" ExcelIncludeSQLCommand="false" ExcelTitle="";

Hi there,

Jumping back on this thread I guess.  Since this might be coming back up in development I thought I'd offer another small observation.  :)

First, the user-option to choose whether or not to include the SQL in the resultant XLSX makes sense to me.  It's yet another option, yes.  It's also super easy to just delete the rows or clear the contents, yes.  But, well, whatever.  Makes sense to me as one of the reasons DbVisualizer is such a powerful tool is that it gives users such control over how they want to work.  Yes, this would be yet one more for users to decide on.

The observation I'd bring up is the fact that the SQL code provided to Excel is only the final statement of one's query and not the entire query that was last executed.  To allow better error debugging my team imposes statement delimiters wherever possible.  That's helped out debugging efforts using DbVisualizers message pane.  But the side effect is that the SQL pushed to excel isn't complete.

Try it yourself to see what I mean.

Example #1:  Run this code and the entire script will fully populate in Excel

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
SELECT GETDATE() as TodaysDate
INTO #Test

SELECT * FROM #Test

Example #2:  Run this code and you'll see only the final statement populates in Excel

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET NOCOUNT ON ;

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test ;
SELECT GETDATE() as TodaysDate
INTO #Test ;

SELECT * FROM #Test ;

Thanks for making this suggestion, Mike. It's one of my most used features already.

I would appreciate the option to turn ExcelIncludeSQLCommand off for the button, the same way you can for file export.