Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- $main = {
- Get-ExcelData
- }
- function Get-ExcelData {
- $date = Get-Date -format "yyyyMMdd_HHmm"
- $Infile = "C:\Users\John\Desktop\sample.xlsx"
- $Outfile = "C:\Users\John\Desktop\" + date + "results.txt"
- $sheets = Get-ExcelSheetInfo $Infile
- $ExcelData = @()
- Write-Host "--Reading" $Infile "into datatable"
- foreach ($worksheet in $sheets) {
- $ExcelData += Import-Excel -Path $Infile -WorksheetName $worksheet.name
- }
- $ExcelData | Select-Object -Property FacilityName, "Region-2`nCounty-3`nDistrict-4" | Out-Null
- $rowMax = $ExcelData.Length
- Write-Host "--Executing SQL query"
- $UBData = Get-UBData
- $UBData = $UBData.Tables[0].EXTERNAL_CODE
- for($i=0; $i -le $rowMax; $i++) {
- if ($ExcelData[$i]."Region-2`nCounty-3`nDistrict-4") {
- $RCD = $ExcelData[$i]."Region-2`nCounty-3`nDistrict-4"
- $RCDTS = $RCD.substring(0,2) + "-" + $RCD.substring(2,3) + "-" + $RCD.Substring(5,4) + '-' + $ExcelData[$i].Type + '-' + $ExcelData[$i].School
- }
- $ExcelData[$i] | Where-Object {$_ -ne $null} | Add-Member -MemberType NoteProperty "RCDTS" -Value $RCDTS
- }
- $resultCount = 0
- Write-Host "--Comparing data sets"
- for($i=0; $i -le $rowMax; $i++) {
- $excludeTypes = @("1","2","6","9","D")
- 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)) {
- Add-Content $Outfile $($ExcelData[$i].FacilityName + ", " + $ExcelData[$i].RCDTS)
- $resultCount++
- }
- }
- Write-Host "`r`nWrote" $resultCount "results to" $Outfile
- }
- function Get-UBData {
- $DataSet = $null
- $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
- $SqlConnection.ConnectionString = "Data Source=server;Initial Catalog=database;Integrated Security=True;ApplicationIntent=ReadOnly"
- $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
- $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')"
- $SqlCmd.Connection = $SqlConnection
- $SqlCmd.CommandTimeout = 0
- $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
- $SqlAdapter.SelectCommand = $SqlCmd
- $DataSet = New-Object System.Data.DataSet
- $DataSet.DataSetName = 'ROOT'
- $SqlAdapter.Fill($DataSet) | Out-Null
- $SqlConnection.Close()
- return $DataSet
- }
- & $main
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement