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