SHARE
TWEET

Untitled

a guest Feb 21st, 2020 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. $main = {
  2.     Get-ExcelData
  3. }
  4. function Get-ExcelData {
  5.     $date = Get-Date -format "yyyyMMdd_HHmm"
  6.     $Infile = "C:\Users\John\Desktop\sample.xlsx"
  7.     $Outfile = "C:\Users\John\Desktop\" + date + "results.txt"
  8.     $sheets = Get-ExcelSheetInfo $Infile
  9.     $ExcelData = @()
  10.     Write-Host "--Reading" $Infile "into datatable"
  11.     foreach ($worksheet in $sheets) {      
  12.         $ExcelData += Import-Excel -Path $Infile -WorksheetName $worksheet.name
  13.     }
  14.     $ExcelData | Select-Object -Property FacilityName, "Region-2`nCounty-3`nDistrict-4" | Out-Null
  15.     $rowMax = $ExcelData.Length
  16.     Write-Host "--Executing SQL query"
  17.     $UBData = Get-UBData
  18.     $UBData = $UBData.Tables[0].EXTERNAL_CODE
  19.     for($i=0; $i -le $rowMax; $i++) {
  20.         if ($ExcelData[$i]."Region-2`nCounty-3`nDistrict-4") {
  21.             $RCD = $ExcelData[$i]."Region-2`nCounty-3`nDistrict-4"
  22.             $RCDTS = $RCD.substring(0,2) + "-" + $RCD.substring(2,3) + "-" + $RCD.Substring(5,4) + '-' + $ExcelData[$i].Type + '-' + $ExcelData[$i].School
  23.         }
  24.         $ExcelData[$i] | Where-Object {$_ -ne $null} | Add-Member -MemberType NoteProperty "RCDTS" -Value $RCDTS    
  25.     }
  26.     $resultCount = 0
  27.     Write-Host "--Comparing data sets"
  28.     for($i=0; $i -le $rowMax; $i++)  {
  29.         $excludeTypes = @("1","2","6","9","D")
  30.         if (($UBData -notcontains $ExcelData[$i].RCDTS) -and ($excludeTypes -notcontains $ExcelData[$i].Cat) -and ($ExcelData[$i].RCDTS -notlike '77-777*') -and ($ExcelData[$i].RCDTS -ne $null)) {
  31.             Add-Content $Outfile $($ExcelData[$i].FacilityName + ", " + $ExcelData[$i].RCDTS)
  32.             $resultCount++
  33.         }
  34.     }
  35.     Write-Host "`r`nWrote" $resultCount "results to" $Outfile
  36. }
  37. function Get-UBData {
  38.     $DataSet = $null
  39.     $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  40.     $SqlConnection.ConnectionString = "Data Source=server;Initial Catalog=database;Integrated Security=True;ApplicationIntent=ReadOnly"
  41.     $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  42.     $SqlCmd.CommandText = "SELECT ORG_CODE, ACCT_CODE, NAME, EXTERNAL_CODE, ADDRESS_L1, CITY, STATE, POSTAL_CODE, MAIN_PHONE, KEYWORD_1, KEYWORD_2 FROM  ACCT_MASTER WHERE (ORG_CODE = 10) AND (TYPE = '9S') AND (CLASS = 'O')"
  43.     $SqlCmd.Connection = $SqlConnection
  44.     $SqlCmd.CommandTimeout = 0
  45.     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  46.     $SqlAdapter.SelectCommand = $SqlCmd
  47.     $DataSet = New-Object System.Data.DataSet
  48.     $DataSet.DataSetName = 'ROOT'
  49.     $SqlAdapter.Fill($DataSet) | Out-Null
  50.     $SqlConnection.Close()
  51.     return $DataSet
  52. }
  53. & $main
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top