Powershell transfert UPS Worldship address book to csv file for UPS web import

PowerShell transfer UPS Worldship address book to csv file, for UPS Web shipping address book import. Code Release : ``` cls Set-ExecutionPolicy -ExecutionPolicy Bypass # database Intraction Write-Host "##################################################" -ForegroundColor Red Write-Host " Start Transfer Worldship Address Bokk data to UIS" -ForegroundColor Green Write-Host " " -ForegroundColor Red Write-Host "##################################################" -ForegroundColor Red Start-Sleep -Seconds 3 $Win64Arch = [Environment]::Is64BitProcess if($PSVersionTable.PSVersion -le 5.1){ write-host "Update Powershell first" exit } try{ if($Win64Arch -eq $true){ $temp = Get-ItemProperty -Path HKLM:SOFTWARE\WOW6432Node\UPS\Installation -ErrorAction Stop }else{ $temp = Get-ItemProperty -Path HKLM:SOFTWARE\UPS\Installation -ErrorAction Stop } }catch{ $ErrorMessage = $_.Exception.Message $FailedItem = $_.Exception.ItemName write-host $ErrorMessage -ForegroundColor Red Write-Host "Could not found WS install direct" -ForegroundColor Red Exit } $file = $temp.DataDirectory $pcname = $env:COMPUTERNAME foreach($sqlVer in $temp.SQLVersion){ if($sqlVer -eq "SX2012"){ Write-Host "SQL Server is SX2012" -ForegroundColor Green $SQLServer = "$pcname\UPSWS2012SERVER" #use Server\Instance for named SQL instances! } elseif($sqlVer -eq "SX2014"){ Write-Host "SQL Server is SX2014" -ForegroundColor Green $SQLServer = "$pcname\UPSWS2014SERVER" #use Server\Instance for named SQL instances! } elseif($sqlVer -eq "SX2008"){ Write-Host "SQL Server is SX2008" -ForegroundColor Green $SQLServer = "$pcname\UPSWSDBINSTANCE" #use Server\Instance for named SQL instances! } else{ Write-Host "SQL Server is SX2005" -ForegroundColor Green $SQLServer = "$pcname\UPSWSDBSERVER" #use Server\Instance for named SQL instances! } } #$pcname = $env:COMPUTERNAME #$SQLServer = "$pcname\UPSWS2012SERVER" #use Server\Instance for named SQL instances! $SQLDBName = "UPSWSDB" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security=true;" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = 'Select * From calPkgAgent' $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $nRect = $SqlAdapter.Fill($DataSet) $nRect | Out-Null $objTable = $DataSet.Tables[0] if(!(Test-Path -Path $file\ImpExp)){ Write-Host "Could not check ImpExp create it" -ForegroundColor Red New-Item $file\ImpExp -ItemType director } $filecheck = Test-Path $file\ImpExp\TEMP.csv if($filecheck){ Write-Host "Check Temp.csv remove it" -ForegroundColor Red Remove-Item $file\ImpExp\TEMP.csv -Force } else{ Write-Host "Continue" -ForegroundColor Green } Write-Host "Output Address data to temp.csv" -ForegroundColor Green $objTable | Export-Csv "$file\ImpExp\TEMP.csv" -NoTypeInformation $SqlConnection.Close() #End :database Intraction #clear $addressTMP = Import-Csv -Path $file\ImpExp\TEMP.csv -Delimiter "," | Sort-Object m_primaryKey -Unique #$addressTMP | export-csv "$file\ImpExp\TEMP-re.csv" -NoTypeInformation $filecheck = test-path $file\ImpExp\UISCSVEXPORT.csv if($filecheck){ Write-host "Remove $file\ImpExp\UISCSVEXPORT.csv" -ForegroundColor Red Remove-Item $file\ImpExp\UISCSVEXPORT.csv } else{ Write-Host "Continue" -ForegroundColor Green } Write-Host "Export to $file\ImpExp\UISCSVEXPORT.csv" -ForegroundColor Green foreach($add in $addressTMP){ #$UPSAccount = $add.Sm_shipperNo if($add.Sm_shipperNo){ $POstCode1 = $add.m_PostalCode } else{ $POstCode1 = "" } $data = [PSCustomObject]@{ Company = $add.Sm_businessName CustomerID = $add.Sm_customerId Attension = $add.m_Attention Address1 = $add.m_StreetAddress Address2 = $add.m_RoomFloor Address3 = $add.m_Department City = $add.m_City Stae = $add.m_StateProv PostCode = $add.m_PostalCode Country = $add.m_Country Phone = $add.Sm_phoneNo Ext = "" Mail = $add.Sm_emailAddress FAX = $add.Sm_faxNo IsResidential = $add.m_residential LocationID = $add.Sm_RLAText IsConsigneeBilled = $add.m_isConsBill UPSAcct = $add.Sm_shipperNo PostCode1 = $POstCode1 UPSPoBox = $add.m_isPOBox} $data | Export-Csv $file\ImpExp\UISCSVEXPORT.csv -Append -NoTypeInformation } $filecheck = test-path $file\ImpExp\TEMP.csv if($filecheck){ Write-Host "remove TEMP.csv" -ForegroundColor Red Remove-Item $file\ImpExp\TEMP.csv } Write-Host "Transfer File completed" -ForegroundColor Green Write-Host "##################################################" -ForegroundColor Red Write-Host "File : $file\ImpExp\UISCSVEXPORT.csv" -ForegroundColor Green Write-Host " " -ForegroundColor Red Write-Host "##################################################" -ForegroundColor Red Start-Sleep -Seconds 3 ```