This is a quick and dirty command line program to run 1 or more queries against a SQL Server database and then save each result to its own sheet within Excel.
Example command
DatabaseToExcel.exe /s:MySqlBox /d:MyLocalDb /u:foo /p:bar /q:query.sql /o:out.xlsx
This is the query that we run:
SELECT TOP 10 ProposalID
INTO #pidsToGo
FROM dbo.Proposal
WHERE CurrentWorkflowState IN (10, 11, 20, 60, 70)
-- Query 1
SELECT p.ProposalID,
p.ProposalTitle,
p.BRAsOfDate
FROM dbo.Proposal p
WHERE p.ProposalID IN (SELECT ProposalID
FROM #pidsToGo)
-- Query 2
SELECT pl.ProposalLineId,
pl.UIIndex,
pl.ProposalID,
pl.SpotLength
FROM dbo.ProposalLine pl
WHERE pl.ProposalID IN (SELECT ProposalID
FROM #pidsToGo)
The application then produces an Excel file with 2 worksheets.
But we can make it prettier with named sheets. To that end we can provide a named sheets file. Example:
Proposals
Proposal Lines
Then run the command again with the sheetFile parameter:
DatabaseToExcel.exe /s:MySqlBox /d:MyLocalDb /u:foo /p:bar /q:query.sql /o:out.xlsx /sheetFile:sheetNames.txt
And then, the app generates an Excel file with named sheets:
Other command line switches:
/i - Login via Windows Authentication
/e - Launch file in Excel after creation