Powershell function that accepts multidimensional arrays as parameters

Question

I'm trying to take a web script and make it a function that accepts an array of strings as arguments. Currently I work around this by making two functions from the same script. Then I use My-Function1 -arr $1 -arr2 $2 and after that use My-Function2 -arr $1 -arr2 $2 to get around the array problem.

There has to be a much cleaner method for passing this type of data to only one function but in my searching I have not see anything. I also saw a question on using the Tables refresh method to run a new query but with my limited scripting experience, I wasn't sure how to use that with my script.

The reason for two functions is: 1) First query runs and the results are inserted into a New Excel WorkBook and Worksheet. 2) Next the 2nd function is called to run the same T-SQL query however this time, Open workbook from #1 then insert a new named worksheet.

Original script from http://www.maxtblog.com/2014/06/powershell-extracting-sql-server-data-into-excel/

$docs = "D:\Scripts\Reboots2.xlsx"
If (Test-Path $docs){Remove-Item "D:\Scripts\Reboots2.xlsx"} Else {Continue}
Function First-Query {
param([string[]]$arr,$arr2)
### SQL query results sent to Excel
$SQLServer = 'SERVERNAME'
$Database = 'DATABASENAME'
## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True";
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $arr;
$SqlCmd.Connection = $SqlConnection;
## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$SqlConnection.Close()
$DataSetTable = $DataSet.Tables["Table"];
## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;
## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add(1);
$xlsSh = $xlsWb.Worksheets.item(1);
$xlsSh.Name = $($arr2)
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};
## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};
## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit() | Out-Null
## ---------- Saving file and Terminating Excel Application ---------- ##
$xlsFile = "D:\Scripts\Reboots.xlsx"
$xlsObj.ActiveWorkbook.SaveAs($xlsFile) | Out-Null
$xlsObj.Quit()
## - End of Script - ##
While([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)){Remove-Variable xlsObj}
start-sleep 1
}#End Function
Function Rest-Query {
param([string[]]$arr,$arr2)
### SQL query results sent to Excel
$SQLServer = 'SERVERNAME'
$Database = 'DATABASENAME'
## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True";
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $arr;
$SqlCmd.Connection = $SqlConnection;
## - Extract and build the SQL data object '$DataSetTable':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$SqlConnection.Close()
$DataSetTable = $DataSet.Tables["Table"];
## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;
## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsObj.Workbooks.Open("D:\Scripts\Reboots.xlsx")
$xlsSh = $xlsWb.Worksheets.Add([System.Reflection.Missing]::Value, $xlsWb.Worksheets.Item($xlsWb.Worksheets.Count))
$xlsSh.Name = $($arr2)
$xlsObj.DisplayAlerts = $false
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;
## - Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};
## - Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};
## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit() | Out-Null
## ---------- Saving file and Terminating Excel Application ---------- ##
$xlsFile = "D:\Scripts\Reboots.xlsx"
$xlsObj.ActiveWorkbook.SaveAs($xlsFile) | Out-Null
$xlsObj.Quit()
$xlsObj.DisplayAlerts = $true
While([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)){Remove-Variable xlsObj}
[gc]::collect()
[gc]::WaitForPendingFinalizers()
start-sleep 1
}#End Function2
$a = @'
SELECT DISTINCT
'@
$b = @'
SELECT DISTINCT 
'@
$c = @'
SELECT DISTINCT 
'@
$d = @'
SELECT DISTINCT 
'@
$e = @'
SELECT DISTINCT 
'@
$f = @'
SELECT DISTINCT 
'@
$g = @'
SELECT DISTINCT 
'@
First-Query -arr $a -arr2 Monday
Rest-Query -arr $b -arr2 Tuesday
Rest-Query -arr $c -arr2 Wednesday
Rest-Query -arr $d -arr2 Thursday
Rest-Query -arr $e -arr2 Friday
Rest-Query -arr $f -arr2 Saturday
Rest-Query -arr $g -arr2 Sunday

Another interesting result is Zombie Excel process which is left over. However, open the resulant spreadsheet then close it, the Excel process will exit. — user4317867
– user4317867, Commented Jan 21, 2015 at 6:45

user4317867 · Accepted Answer · 2015-01-22 00:57:01Z

1

You can indeed write a single function for what you wanted to do, however you need to rearrange your code and add some structure to it.

I have tried to give some structure to the example below which you will need to adapt to your script.

Basically, and from what I could understand from the code you posted, you are running a specific SQL query for each week day and want to save the results in Excel.

In the example below I'm using a for loop to ensure the index used in each array is the same. In the comments are the commands you need to add from your code.

Function First-Query {
    param([string[]]$arr,[string[]]$arr2)

    #initialize Excel
    # do your Excel commands like opening the file, adding the workbook

    #Now perform the query by using the appropriate element in each array (1 query / day)
    for ($i = 0; $i -lt $arr.Count; $i++)
    {
        $day = $arr2[$i]
        $query = $arr[$i]


        # then add the worksheet
        $xlsSh.Name = $day

        # run the query
        $SqlCmd.CommandText = $query;

        # Save the results in the Excel columns
    }

    # Save and Quit
}#End Function
$arr = @()
$arr += "SELECT DISTINCT"
$arr += "ANOTHER QUERY SELECT DISTINCT"
# additional queries added with $arr += 

$weekdays = @("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
First-Query -arr $arr -arr2 $weekdays

edited Jan 22, 2015 at 0:57

user4317867

2,4465 gold badges34 silver badges59 bronze badges

answered Jan 21, 2015 at 9:53

Micky Balladelli

10k2 gold badges36 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4317867 Over a year ago

Looks great, I will work on this and report back. Thank you!

user4317867 Over a year ago

Got it working, had to add in a few things. First, $xlsSh = $xlsWb.Worksheets.Add([System.Reflection.Missing]::Value, $xlsWb.Worksheets.Item($xlsWb.Worksheets.Count)) to add new worksheet AT THE END. Next $del = $xlsWb.Sheets | where {$_.Name -eq 'Sheet1'} to delete Sheet1, finally $activate = $xlsWb.Sheets | where {$_.Name -eq 'Monday' } to set Monday (1st sheet) as active.

user4317867 Over a year ago

Just an FYI, running this as a single function TotalMinutes : 1.74 and as the dual function TotalMinutes : 2.09 Also note I had the Test-Path clause incorrect. It needs to be If (Test-Path $docs){Remove-Item $docs} in case anyone finds that helpful.

Micky Balladelli Over a year ago

Yes, the else{continue} would just exit the script at that point.

Collectives™ on Stack Overflow

Powershell function that accepts multidimensional arrays as parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related