Powershell: Filling up an Existing Excel Sheet with data from SQL Server

Situation:

The client required to have data from their SQL Server 2012 Database filled in to an existing Excel Sheet. This sheet had a table with around 40 columns of which 12 where to be filled by us and rest from different departments.
I tried different solution for that since the requirements were not initially clear. Forexample OPENROWSET was tried. In this regards this link really helpful. Although this requires few configuration to be made on the server or to install e.g. ACE.OLEDB driver on server which was not possible in our case.
So we decided to write a Powershell script and access SQL

Now about the script:
  1. Take the file TestExcel.xlsx from C:\Temp folder
  2. Create its copy with the current date appended to its name. 
  3. Start reading the Excel file from Sheet "Specified Sheet Name"
  4. From Columns 1, it will take the name of the system. 
  5. Based on the name fill the excel sheet by executing the queries, here are the columns that are going to be filled: 

#      $ExcelSheetStartRowIndex = 1
#      $ExcelColumnOrder_SystemName = 1;
#      $ExcelColumnOrder_ComputerSepcification = 47;
#      $ExcelColumnOrder_ComputerLanguage = 48;
#      $ExcelColumnOrder_ComputerLocation = 49;
#      $ExcelColumnOrder_AdditionalSoftware = 52;

# #############################################################################
# 
# NAME: SCCM_CMDB_Export_Excel.ps1
# 
# AUTHOR:  Vaqar Hyder, Cellent AG
# DATE:  2016/04/21
# EMAIL: vaqar.hyder@cellent.de
# 
# COMMENT:  This script will do the following:
# 1. Take the file NGC_Local_HW_Requirements_IT.xlsx from   C:\Temp folder
# 2. Create its copy with the current date appended to its name. 
# 3. Start reading the Excel file from Sheet "Detailed Information"
# 4. From Columns 24, it will take the name of the system. 
# 5. Based on the name fill the excel sheet by executing the queries, here are the columns that are going to be filled:  
#      $ExcelSheetStartRowIndex = 3
#      $ExcelColumnOrder_Make = 21;
#      $ExcelColumnOrder_Model= 22;
#      $ExcelColumnOrder_ModelType = 23;
#      $ExcelColumnOrder_SystemName = 24;
#      $ExcelColumnOrder_ComputerSerial = 26;
#      $ExcelColumnOrder_ComputerSepcification = 47;
#      $ExcelColumnOrder_ComputerLanguage = 48;
#      $ExcelColumnOrder_ComputerLocation = 49;
#      $ExcelColumnOrder_AdditionalSCCMSoftware = 52;
#      $ExcelColumnOrder_ComputerMACAddress = 21;
#
#
#
# VERSION HISTORY
# 1.0 2016.04.19 Initial Version.
# 1.1 2016.04.21 Upgrade with...
#
#The Execution policy has been update on this system using the following command. Please revert to the restricted when no needed
#Set-ExecutionPolicy Unrestricted –force 
#
# TO ADD
# Implement MACAddress
# Implement OS Architecture
# Implement Logging
# Make  further function and break the code. 
# Test the code for more that 10 items
# 
##########################################################################


############################[Declarations]############################

    $SQLServer  = "dehnm-scc-pp101"
    $user = "ACAG\P666tes001"
    $pwd = ""
    #SQLDBName spiel keine Rolle. Wir habe Datenbank in SQL Abfragen erwähnt. 
    $SQLDBName = "SCCM_SystemProperties"
    
    #Related to Excel Sheet
    #Order of the Columns in Excel Sheet. Must be changed it here if Changed in Excel Sheet
    $ExcelSheetStartRowIndex = 3
    $ExcelColumnOrder_Make = 21;
    $ExcelColumnOrder_Model= 22;
    $ExcelColumnOrder_ModelType = 23;
    $ExcelColumnOrder_ComputerMACAddress = 25;
    $ExcelColumnOrder_SystemName = 24;
    $ExcelColumnOrder_ComputerSerial = 26;
    $ExcelColumnOrder_ComputerSepcification = 47;
    $ExcelColumnOrder_ComputerLanguage = 48;
    $ExcelColumnOrder_ComputerLocation = 49;
    $ExcelColumnOrder_ComputerOSArchitecture = 50; 
    $ExcelColumnOrder_AdditionalSCCMSoftware = 52;
    
    $ExcelColumnOrder_ComputerModel = 45;
    $ExcelColumnOrder_NewModel = 35;


    #The Location of the Excel sheet that contains the System Names
    $Path = "C:\Temp\" 
    
    #The Name of the Excel sheet that contains the System Names
    $fileName = "NGC_Local_HW_Requirements_IT.xlsx" 
   
    $Today = Get-Date -Format "dd-M-yy"
    
    #The new file with all the required columns fille will have this name and the same location as Source file
    $NewFileName = "NGC_Local_HW_Requirements_IT_$Today.xlsx"

    #Name of the sheet containing Information inside the Source Excel Sheet. 
    $sheetName = "Detailed Information" 

    #Logs related declarations
    $outFile = "C:\temp\ExportLogs$Today.txt"
    $now=Get-Date -format "dd-MMM-yyyy HH:mm"

############################[Functions]############################

function Get_MakeandModel_FromSCCMDatabase($ComputerName)
{

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $DataSet = New-Object System.Data.DataSet
  Try
       {
        $SqlQuery = "SELECT CM_P00.dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, CM_P00.dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
                    (SELECT CASE WHEN CM_P00.dbo.v_R_System.Name0 LIKE '%NTB%' THEN 'Laptop' ELSE 'Desktop'END) AS [ModelType] 
                    FROM CM_P00.dbo.v_R_System 
                    LEFT OUTER JOIN CM_P00.dbo.v_GS_COMPUTER_SYSTEM ON CM_P00.dbo.v_R_System.ResourceID = CM_P00.dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
                    WHERE (CM_P00.dbo.v_R_System.Name0 = N'" + $ComputerName +"')"
    
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd
        $SqlAdapter.Fill($DataSet)
        
       

        $SqlConnection.Close()
       if($DataSet.Tables[0])
       {
          
       } 
       else
       {
         Write-Host "Inside Exception";
       }
        return $DataSet.Tables[0]

    }
    Catch
    {
        
        
        Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'CM_P00' for the Make, Model and ModelType `n $_.Exception.ItemName `n $_.Exception.Message   " | Out-File $outFile -Append
        return $null;
    }

 
}

function Get_SerialNo_FromSCCMDatabase($ComputerName)
{

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    Try
    {
        $SqlQuery = "SELECT CM_P00.dbo.v_GS_PC_BIOS.SerialNumber0 AS SerialNumber
                    FROM CM_P00.dbo.v_R_System 
              LEFT OUTER JOIN CM_P00.dbo.v_GS_PC_BIOS ON CM_P00.dbo.v_R_System.ResourceID = CM_P00.dbo.v_GS_PC_BIOS.ResourceID 
                    WHERE (CM_P00.dbo.v_R_System.Name0 = N'" + $ComputerName +"')"

        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd    
        

        $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close()
        return $DataSet.Tables[0]
    }
    Catch
    {
     Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'CM_P00' for Serial Number `n $_.Exception.ItemName `n $_.Exception.Message" | Out-File $outFile -Append
     return $null;
    }
}


function Get_SystemSpecification_FromCMDb($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    Try
    {
        $SqlQuery = "SELECT SCCM_SystemProperties.dbo.v_SystemInformation.CLIENT_FUNCTION as Specification
                    FROM SCCM_SystemProperties.dbo.v_SystemInformation
                    WHERE SCCM_SystemProperties.dbo.v_SystemInformation.SystemName = N'" + $ComputerName +"'"

        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd    
        $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close()
       

        return $DataSet.Tables[0]
    }
    Catch
    {
        Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' System Specification `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
         return $null;
    }
}


function Get_SystemLanguage_FromCMDb($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    Try
        {
    
            $SqlQuery = "SELECT Language from SCCM_SystemProperties.dbo.v_SystemInformation
                        WHERE SCCM_SystemProperties.dbo.v_SystemInformation.SystemName = N'" + $ComputerName +"'"

            $SqlCmd.CommandText = $SqlQuery
            $SqlCmd.Connection = $SqlConnection
            $SqlAdapter.SelectCommand = $SqlCmd    
            $SqlAdapter.Fill($DataSet)
            $SqlConnection.Close()
            return $DataSet.Tables[0]
        }
    Catch
        {
            Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' Language `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
             return $null;
        }

}


function Get_SystemLocation_FromCMDb($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet
    
    Try
    {
        $SqlQuery = "SELECT SITE_SERVER as Location FROM SCCM_SystemProperties.dbo.v_SystemInformation
                    WHERE SCCM_SystemProperties.dbo.v_SystemInformation.SystemName = N'" + $ComputerName +"'"

        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd    
        $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close()
        return $DataSet.Tables[0]
    }
    Catch
    {
        Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' Location `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
         return $null;
    }
}


function Get_SCCMAdditional_FromSCCM($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    Try
    {
    
        $SqlQuery = "SELECT  CM_P00.dbo.v_Collection.Name AS OptionalSoftware
                             FROM CM_P00.dbo.v_Collection RIGHT OUTER JOIN
                             CM_P00.dbo.v_CollectionRuleDirect ON CM_P00.dbo.v_Collection.CollectionID = CM_P00.dbo.v_CollectionRuleDirect.CollectionID LEFT OUTER JOIN
                             CM_P00.dbo.v_R_System ON CM_P00.dbo.v_CollectionRuleDirect.ResourceID = CM_P00.dbo.v_R_System.ResourceID
                             WHERE CM_P00.dbo.v_R_System.Name0 = N'" + $ComputerName +"' 
                             AND (CM_P00.dbo.v_Collection.Name LIKE N'%- Auth') 
                             AND (NOT (CM_P00.dbo.v_Collection.Comment LIKE N'%(disabled)%')) ORDER BY OptionalSoftware"

        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd    
        $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close()
        return $DataSet.Tables[0]

    }
    Catch
    {
        Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'CM_P00' for getting SCCM Additional Software `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
         return $null;
    }
}


function Get_MACAddress_FromCMDb($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    Try
        {
            $SqlQuery = "SELECT MAC_ADRESS as MACAddress FROM SCCM_SystemProperties.dbo.v_SystemInformation
                        WHERE SCCM_SystemProperties.dbo.v_SystemInformation.SystemName = N'" + $ComputerName +"'"

            $SqlCmd.CommandText = $SqlQuery
            $SqlCmd.Connection = $SqlConnection
            $SqlAdapter.SelectCommand = $SqlCmd    
            $SqlAdapter.Fill($DataSet)
            $SqlConnection.Close()
            return $DataSet.Tables[0]
        }
    Catch
        {
            Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for MACAddress `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
            return $null;
        }
        
}




function Get_OSArchitecture_FromCMDb($ComputerName)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet

    
    Try
    {
        $SqlQuery = "SELECT SysInfo.SystemName,
                    (SELECT CASE WHEN SysInfo.OS_ARCHITECTURE = '32' OR SysInfo.OS_ARCHITECTURE = '64'  
                     THEN 'W10-64'  
                        WHEN SysInfo.OS_ARCHITECTURE = '32 Native' OR SysInfo.OS_ARCHITECTURE = '64 Native' 
                     THEN 'W10-64 Native'
                        WHEN SysInfo.OS_ARCHITECTURE = 'W10-64' 
                     THEN 'W10-64'
                        WHEN SysInfo.OS_ARCHITECTURE = 'W10-64 Native' 
                     THEN 'W10-64 Native'
                 ELSE 'Not Available' END) AS [OSArchitecture] 
                                FROM SCCM_SystemProperties.dbo.v_SystemInformation SysInfo
                                WHERE SysInfo.SystemName = N'" + $ComputerName +"'"

        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter.SelectCommand = $SqlCmd    
        $SqlAdapter.Fill($DataSet)
        $SqlConnection.Close()
        return $DataSet.Tables[0]

    }
    Catch
    {
        Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for OSArchitecture `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
         return $null;
    }
}

 function Check_IfNumericValue ($x) {
    try {
        0 + $x | Out-Null
        return $true
    } catch {
        return $false
    }
}

Write-Host "*******************************The Altana Rollout List Generator***************************************" -ForegroundColor Yellow
Write-Host "`n"
Write-Host "Please enter the index/position of the first rows in the Excel sheet to be processed, `npress 3 which is default. `nPress Enter key to quit. `nEntering anything other than numeric value between 0 - 9, will result in exit."
Write-Host "`n"
Write-Host "*******************************************************************************************************" -ForegroundColor Yellow
$firstRowPostion = Read-Host;
  
  if ((Check_IfNumericValue $firstRowPostion))
  {

    if ($firstRowPostion -ge 10)
        {
            Write-Host "Starting the Processing from Row No $firstRowPostion."
            $ExcelSheetStartRowIndex = [int]$firstRowPostion
        }
        else
        {
            Write-Host "You entered an invalid number!"
            Break;
        }
  }
  else
  {
        Write-Host "You entered an invalid number!"
        
        Break;
        #$ExcelSheetStartRowIndex = 3;
  }


Copy-Item $Path$fileName -destination $Path$NewFileName 
$PathWithFileName = $Path + $NewFileName;
Write-Host $PathWithFileName -ForegroundColor Green 

$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($PathWithFileName) 
$sheet = $workbook.Worksheets.Item($sheetName)
$objExcel.Visible=$false 
$rowMax = ($sheet.UsedRange.Rows).count

$ComputerName = "";
$flagSuccess = 0;
Write-Host "$rowMax Rows found in Usage range of given Excel Sheet" -ForegroundColor Green 
Write-Output "Excel Processing started at $now" | Out-File $outFile

for($intRow = $ExcelSheetStartRowIndex ; $intRow -le $rowMax ; $intRow++)
   {

    Try
        {
            #Excel sheet ends if there is not Company code available
            if($sheet.cells.item($intRow,2).text -eq "")
            {
                Write-Host "`n`n`n`nSeems like end of Excel Sheet has been reached which is at the row $intRow where as the Usage area has $rowMax rows.`nPlease check the Excel Sheet at $intRow if the Original Excel sheet ends here" -ForegroundColor White
                Write-Output "Excel Processing Ended at $now" | Out-File $outFile -Append
                Break;
            }

            Write-Host "Row No.: $intRow in process!" -ForegroundColor Yellow
            $ComputerName = $sheet.cells.item($intRow,$ExcelColumnOrder_SystemName).text;

            ############################# Model, Make, ModelType (SCCM) ############################

            $dtComputers = Get_MakeandModel_FromSCCMDatabase $ComputerName;
            if($dtComputers)
            {
                foreach ($Computer in $dtComputers) 
                {
                  $sheet.cells.item($intRow,$ExcelColumnOrder_Make) =$Computer.Make         
                  $sheet.cells.item($intRow,$ExcelColumnOrder_Model) =$Computer.Model
                  $sheet.cells.item($intRow,$ExcelColumnOrder_ModelType) = $Computer.ModelType
                }
            }
            else
            {
                Write-Host "Model, Make, ModelType could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Make, Model and ModelType" | Out-File $outFile -Append
            }

            #############################Serial Number (SCCM) ######################################

            $dtComputersSerialNumbers = Get_SerialNo_FromSCCMDatabase $ComputerName;
            
            if($dtComputersSerialNumbers)
             {
                foreach ($ComputerSerial in $dtComputersSerialNumbers) 
                {
                    $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerSerial) =$ComputerSerial.SerialNumber   
                }
             }
            else
            {
                Write-Host "Serial Nr.could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Serial Nr." | Out-File $outFile -Append
            }

            #############################System Specification (SCCM)################################

            $dtComputersSpecification = Get_SystemSpecification_FromCMDb $ComputerName;
        
            if($dtComputersSpecification)
             {
                foreach ($ComputerSpecification in $dtComputersSpecification) 
                {
                    $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerSepcification) =$ComputerSpecification.Specification   
                }    
             }
            else
            {
                Write-Host "Specifications could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Specifications" | Out-File $outFile -Append
            }

            #############################Language (CMDB (Admintool))###############################

            $dtComputersLanguage = Get_SystemLanguage_FromCMDb $ComputerName;
            if($dtComputersLanguage)
             {
                foreach ($ComputerLanguage in $dtComputersLanguage) 
                {
                    $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerLanguage) =$ComputerLanguage.Language   
                }
             }
            else
            {
                Write-Host "Language could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Language" | Out-File $outFile -Append
            }

            ############################Location (CMDB (Admintool))################################

            $dtComputersLocation = Get_SystemLocation_FromCMDb $ComputerName;
        
            if($dtComputersLocation)
             {
                foreach ($ComputerLocation in $dtComputersLocation) 
                {
                 $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerLocation) =$ComputerLocation.Location
                }
             }
            else
            {
                Write-Host "Location could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Location" | Out-File $outFile -Append
            }

            
            #############################Additional SCCM Software (CMDB (Admintool))############################
            $dtComputersAdditional = Get_SCCMAdditional_FromSCCM $ComputerName;
            $AdditionalSystemSoftware = "";
            if($dtComputersAdditional)
             {
                foreach ($rowComputerAdditonalSoftware in $dtComputersAdditional) 
                {
                    $AdditionalSystemSoftware +=$rowComputerAdditonalSoftware.OptionalSoftware + ";"
                }
             }
            else
            {
                Write-Host "Additional Software could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for Additional Software" | Out-File $outFile -Append
            }
            $sheet.cells.item($intRow,$ExcelColumnOrder_AdditionalSCCMSoftware) = $AdditionalSystemSoftware.trimstart(";")

            ############################ MACAddress (CMDB (Admintool))############################
            $dtComputersMACAddresses = Get_MACAddress_FromCMDb $ComputerName;
            if($dtComputersMACAddresses)
             {
                foreach ($ComputerMACAddress in $dtComputersMACAddresses) 
                {
                    $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerMACAddress) =$ComputerMACAddress.MACAddress
                }    
             }
            else
            {
                Write-Host "MAC Address could not be added for Row No.: $intRow Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for MAC" | Out-File $outFile -Append
            }


            ############################ OSArchitecture (CMDB (Admintool))############################

            $dtComputersOSArchitecture = Get_OSArchitecture_FromCMDb $ComputerName;
            if($dtComputersOSArchitecture)
             {
                foreach ($ComputerOSArchitecture in $dtComputersOSArchitecture) 
                {
                    $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerOSArchitecture) =$ComputerOSArchitecture.OSArchitecture
                }    
             }
            else
            {
                Write-Host "OSArchitecture could not be added  for Row No.: $intRow. Check Logs for details" -ForegroundColor Red
                Write-Output "Computer: $ComputerName - exception encountered while accessing  server 'DEHNM-SCC-PP101' database 'SCCM_SystemProperties' for OS Architecture" | Out-File $outFile -Append
            }


             $ComputerName = $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerModel).text;

            
            
            ############################# Computer Model using New Model ############################

            $NewModel= "";
            $NewModel = $sheet.cells.item($intRow,$ExcelColumnOrder_NewModel).text;
             
            if($NewModel -ne "")
            {
             
                switch ($NewModel)
                {
                  {$_ -like   'ALTANA ID3*' -or $_ -like 'ALTANA ID2*' -or $_ -like 'ALTANA ID1:*'  }
                    {
                        $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerModel) ='Desktop';
                    }
                  {$_ -like 'ALTANA ID4*' -or $_ -like 'ALTANA ID5*' -or $_ -like 'ALTANA ID7*' -or $_ -like 'ALTANA ID8*' -or $_ -like 'ALTANA ID9*'}
                    {
                        $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerModel) ='Notebook';
                    }
                  {$_ -like 'ALTANA ID6*' -or $_ -like 'ALTANA ID10*'}
                    {
                        $sheet.cells.item($intRow,$ExcelColumnOrder_ComputerModel) ='Tablet';
                    }
                }
            }




            Write-Host "SystemName: $ComputerName has been processed. Please check logs and the on screen messages for errors." -ForegroundColor Yellow
            $ComputerName = "";
        }
        Catch
        {
            Write-Host "Updating Row No.: $intRow SystemName: $ComputerName wasn't Successful! Please check $intRow in the Excel Sheet" -ForegroundColor Yellow
            Write-Output "Updating Row No.: $intRow SystemName: $ComputerName wasn't Successful! Please check $intRow in the Excel Sheet. `nsee below `n $_.Exception.ItemName `n $_.Exception.Message " | Out-File $outFile -Append
            $flagSuccess = 1;

        }
    }  

   

$sheet.Columns.item("A:AY").EntireColumn.AutoFit() | out-null
$workbook.Close($true)
$objExcel.quit()

Comments

Post a Comment

Popular posts from this blog

SPFx: Develop using SharePoint Framework without Installing all the dependecies.

Image
SharePoint Framework (SPFx) has been around for more than 5 years now with being Generally available since Feb 2017.  In the recent times, its been possible to develop webparts for SharePoint on-prem as well using SPFx and with that it is getting harder to maintain multiple versions of required toolchains. e.g. Node. For M365 you can use the latest version of SPFx but probably you know, if you aim to develop webparts for the SharePoint Server 2016 platform, for instance, you need to use the SharePoint Framework v1.1.0 due to the server-side version dependencies. So yet another challenge is using the correct version of the SPFx and Nodejs for the particular project . Although I personally used to like Node Version Manager which lets you manage multiple installations of node.js on a Windows computer but still one needs to isolate SharePoint Framework dependencies if you are targeting multiple platform from on PC. 2nd Problematic scenario begins when you start developing SPFx extensi...
Read more

SharePoint 2010 Migration Woes: Importing and exporting lists template between different SP2010 Servers

Image
Migrations of SharePoint contents from one server to another can be a big PITA. Once I came across a  scenario  where I had to export a couple of lists from SharePoint online i.e. Office 365 to the SharePoint 2010 on my local server.  What I did is to copy the required lists along with content by creating a list template i.e. via .stp file. Then I upload it to my local SharePoint 2010 server's list template gallery. Later I  successfully  created a new list with the uploaded template. For that I took the following steps: Open your SharePoint site in the SharePoint designer 2010. Select Lists and libraries. Select you desire list and the click on the "Save as Template" from the ribbon. You'll be redirected to the SharePoint site and there after filling in the required fields and pressing OK , .stp will be created. This template can be viewed in Site Action -> Site Setting -> Galleries ->List Template. Select the list template and then from ...
Read more