CODEWITHSUNDEEP
sqlexcelpowershell
Glowie
Glowie

Reputation: 2309

Powershell - output three select statements to three EXCEL worksheets

I have code that can read output of an SQL select statement into a worksheet and save it.

Now I am trying to create three select statements and export it into three worksheets in EXCEL.

Unfortunately, the code only extracts output from the first SELECT statement and stores it into the first EXCEL worsheet

# SQL string with multiple SELECT statements

$SQL = "SELECT Statement 1 
        SELECT Statement 2
        SELECT Statement 3" 

# Create Excel file to save the data

if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing 
  { 
  New-Item "$DirectoryToSave" -type directory | out-null 
  } 

$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1

if ($currentWorksheet -lt 4){
    $ws = $wb.Worksheets.Item($currentWorksheet)
}
else
{
    $ws = $wb.Worksheets.Add()
}
$currentWorksheet += 1


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
    $Excel.Columns.Item(1).Font.Bold = $true 
}

$filename = "D:\Script\Database_stuff.xlsx"
if (test-path $filename ) { rm $filename } 
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$wb.Saved = $True #flag it as being saved 
$wb.Close() #close the document 
$Excel.Quit() #and the instance of Excel 
$wb = $Null #set all variables that point to Excel objects to null 
$ws = $Null #makes sure Excel deflates 
$Excel=$Null #let the air out

How do I even google a solution to this? Thank you!

UPDATE

Solution for short-term

SQL1 = "SELECT Statement 1"

SQL2 = "SELECT Statement 2"

SQL3 = "Select Statement 3"

if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing 
  { 
  New-Item "$DirectoryToSave" -type directory | out-null 
  } 

$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1

$ws = $wb.Worksheets.Item(1)
$ws.name = "GUP Download Activity"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL1)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
}

$ws = $wb.Worksheets.Item(2)
$ws.name = "Distinct"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL2)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }


$ws = $wb.Worksheets.Item(3)
$ws.name = "Computers in GUP Downloads"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL3)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }

$filename = "D:\Script\Daily_GUP_Report.xlsx"
if (test-path $filename ) { rm $filename } 
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$wb.Saved = $True #flag it as being saved 
$wb.Close() #close the document 
$Excel.Quit() #and the instance of Excel 
$wb = $Null #set all variables that point to Excel objects to null 
$ws = $Null #makes sure Excel deflates 
$Excel=$Null #let the air out

Upvotes: 0

Views: 522

Answers (2)

Glowie
Glowie

Reputation: 2309

Solution for short-term

SQL1 = "SELECT Statement 1"

SQL2 = "SELECT Statement 2"

SQL3 = "Select Statement 3"

if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing 
  { 
  New-Item "$DirectoryToSave" -type directory | out-null 
  } 

$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$currentWorksheet=1

$ws = $wb.Worksheets.Item(1)
$ws.name = "GUP Download Activity"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL1)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
}

$ws = $wb.Worksheets.Item(2)
$ws.name = "Distinct"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL2)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }


$ws = $wb.Worksheets.Item(3)
$ws.name = "Computers in GUP Downloads"


$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL3)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }

$filename = "D:\Script\Daily_GUP_Report.xlsx"
if (test-path $filename ) { rm $filename } 
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$wb.Saved = $True #flag it as being saved 
$wb.Close() #close the document 
$Excel.Quit() #and the instance of Excel 
$wb = $Null #set all variables that point to Excel objects to null 
$ws = $Null #makes sure Excel deflates 
$Excel=$Null #let the air out

Upvotes: 0

AnalystCave.com
AnalystCave.com

Reputation: 4984

Shouldn't you replace this:

$SQL = "SELECT Statement 1 
        SELECT Statement 2
        SELECT Statement 3"

With this?:

$SQL = "SELECT Statement 1
        UNION ALL
        SELECT Statement 2
        UNION ALL
        SELECT Statement 3"

I do not understand the issue with the three worksheets though? Why not a simple loop or a Sub which will take the statement and the worksheet as parameters?

Upvotes: 1

Related Questions