Python

Python – CSV to Pipe delimiter

February 20, 2020 – 11:40 am

So why would a perfectly sane person want to have a pipe delimited CSV verses a properly escaped comma separated value file? Yeah beats me too, glutton for punishment, not working with the right parser, drug addiction? All could be reasons. But fear not, if this is the road you’d like to go down there’s a python script for it and here it is so I don’t have to recreate the wheel next time I need it.

import glob
import csv
import os

for entry in glob.glob('applicationResumeAttachmentsManifest/*.csv'):
    #outputFile = (entry.strip(".csv")+"-Pipe.csv")
    outputFile = ("C:\\python\\pipe\\"+entry.strip(".csv")+"-Pipe.csv")
    os.makedirs(os.path.dirname(outputFile), exist_ok=True)
    #print(entry)
    #print(outputFile)
    with open(entry, encoding='utf-8') as inputFile:
        with open(outputFile, 'w', encoding='utf-8', newline='') as writeFile:
            reader = csv.DictReader(inputFile, delimiter=',')
            writer = csv.DictWriter(writeFile, reader.fieldnames, delimiter='|')
            writer.writeheader()
            writer.writerows(reader)
print("Conversion complete.")
Permalink | Comments Off on Python – CSV to Pipe delimiter
Python

Python

February 20, 2020 – 11:31 am

I know that the current buzzword for IT is python and I’m going to have to jump on that bandwagon. In my role as an integration engineer, there are a lot of times that I have to manipulate file data and I’m super impressed by the ease and power of this language.

So to this end I’ll be creating a category for python on this blog and storing code snippets that I find handy and useful. Hope you find it as helpful and enjoyable as I do.

I know a lot of people hear python and think, yeah, a big snake that will constrict you until you’re dead and then swallow you whole. A perfect simile for programming. But here’s the truth, Guido van Rossum developed the language starting in 1989 and the name python comes not from the snake but Monty Python’s Flying Circus which the developers loved to watch while they were coming up with the language. Knowledge is power and sometimes funny too.

Permalink | Comments Off on Python
TCC

TCC – ORA-01791: not a SELECTed expression

February 19, 2020 – 8:44 pm

Love running into this error. What happens here is that I’m doing an export and I want it to return distinct rows and I want to sort it by a date, but I’ve modified the date to format it how I want to see it. For some reason when we preventDuplicates=”true” it sees that the date (having been converted) as not a selected expression.

So the trick is, on your sorting, you have to use the same custom function toChar to get it to sort by the order you want it to. Kind of a pain but in the end of the equation it makes sense that both the projection and the sort by are the same, too bad you can’t just use the alias…

Permalink | Comments Off on TCC – ORA-01791: not a SELECTed expression
TCC

TCC – Static Level Decode

January 21, 2020 – 3:36 pm

This is for when you’re trying to list out location1, location2, etc…
Much easier to build as a complex projection but then the GUI converts it so you have to start from scratch to change.

<quer:projection alias="LocationLevel1" xmlns:quer="http://www.taleo.com/ws/integration/query">
        <quer:decode>
            <quer:field path="JobInformation,PrimaryLocation,Level"/>
            <quer:string>1</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Name"/>
            <quer:string>2</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Name"/>
            <quer:string>3</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Name"/>
            <quer:string>4</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Parent,Name"/>
            <quer:string>5</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Parent,Parent,Name"/>
        </quer:decode>
</quer:projection>

<quer:projection alias="LocationLevel2" xmlns:quer="http://www.taleo.com/ws/integration/query">
        <quer:decode>
            <quer:field path="JobInformation,PrimaryLocation,Level"/>
            <quer:string>1</quer:string>
            <quer:string/>
            <quer:string>2</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Name"/>
            <quer:string>3</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Name"/>
            <quer:string>4</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Name"/>
            <quer:string>5</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Parent,Name"/>
        </quer:decode>
</quer:projection>

<quer:projection alias="LocationLevel3" xmlns:quer="http://www.taleo.com/ws/integration/query">
        <quer:decode>
            <quer:field path="JobInformation,PrimaryLocation,Level"/>
            <quer:string>1</quer:string>
            <quer:string/>
            <quer:string>2</quer:string>
            <quer:string/>
            <quer:string>3</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Name"/>
            <quer:string>4</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Name"/>
            <quer:string>5</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Parent,Name"/>
        </quer:decode>
</quer:projection>

<quer:projection alias="LocationLevel4" xmlns:quer="http://www.taleo.com/ws/integration/query">
        <quer:decode>
            <quer:field path="JobInformation,PrimaryLocation,Level"/>
            <quer:string>1</quer:string>
            <quer:string/>
            <quer:string>2</quer:string>
            <quer:string/>
            <quer:string>3</quer:string>
            <quer:string/>
            <quer:string>4</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Name"/>
            <quer:string>5</quer:string>
            <quer:field path="JobInformation,PrimaryLocation,Parent,Name"/>
        </quer:decode>
</quer:projection>
Permalink | Comments Off on TCC – Static Level Decode
TCC

TCC – WorkActionNumber

December 13, 2019 – 11:36 am

If you’ve ever tried to get at the requisition history, you’ll be aware of the WorkActionNumber that tells you what the event in the history is. The big issue is that even if you do an export on this table, it doesn’t give you all the elements (I have no idea why).
So here is the list in it’s full glory for your reference pleasure:
read more »

Permalink | Comments Off on TCC – WorkActionNumber
TCC

TCC – T-XML Subquery

November 8, 2019 – 10:47 am

So I’ve got a T-XML export from the requisition entity that needs to have the posting date on it. So I just created everything from the export to the SourcingRequest entity and all is good. Well not really, the destination folks that receive the file didn’t want to recode it on their side. So to make a long story short, here is how you pull the most recent open date in this situation from the requisition entity.

<quer:projection alias="LatestPostingDate" projectedValueType="string" projectedField="RuntimeString:LatestPostingDate" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:query projectedClass="SourcingRequest" alias="sqPostingDate" preventDuplicates="true" pagingsize="1">
		<quer:projections>
			<quer:projection alias="PostDate">
				<quer:maximum>
					<quer:customFunction name="TO_CHAR">
						<quer:field path="OpenDate"/>
						<quer:string>yyyy-MM-dd</quer:string>
					</quer:customFunction>
				</quer:maximum>
			</quer:projection>
		</quer:projections>
		<quer:filterings>
			<quer:filtering>
				<quer:equal>
					<quer:field path="Requisition,Number"/>
					<quer:field ownerQuery="PhenomExport" path="Number"/>
				</quer:equal>
			</quer:filtering>
		</quer:filterings>
	</quer:query>
</quer:projection>
Permalink | Comments Off on TCC – T-XML Subquery
TCC

TCC – Referencing a User-defined Field Explicitly in an Import File

October 9, 2019 – 4:18 pm

I ran across this and thought that it was odd. On a candidate import, instead of using the standard field Prefix, we were needing to target a user defined selection elements named Prefix. The error being returned was the following:

Error Detail:
Code: internal
Description: A general internal error occurred.
Reason: Relation “Prefix” not found for the entity “Candidate” in the product pack
The error occurred in the following step: Prepare Import
The corresponding code for this error in version 1.0 would have been: -1

It was odd because one is a field and the other a selection. The fix can be found in Doc ID 1047706.1 on the MOS.
Basically to explicitly declare a user defined field/selection, you need to prefix CustomField: to the field name in order to specify you’re speaking of a custom field. So Prefix,Description becomes CustomField:Prefix,Description and the error is solved.

Update:
This post isn’t as clear as it should be so here’s a better example. Let’s say you have a custom field in experience that’s called ReasonForLeaving. This is a custom field that is the same as the standard field called ReasonForLeaving. The reasons why it’s named the same are not important what is, is calling the right field:
Standard field would be:
TalentUser,Profile,Experiences,ReasonForLeaving
Custom field would be:
TalentUser,Profile,Experiences,CustomField:ReasonForLeaving

So make sure the ‘CustomField:’ declaration is for the field and not the reference location.


Permalink | Comments Off on TCC – Referencing a User-defined Field Explicitly in an Import File
PowerShell

Powershell – Archive Log Files by Day

October 9, 2019 – 2:27 pm

Log files, you have to love them because they allow you to figure out what’s going on but what do you do with them, by default they just keep writing and writing and at some point you need to do something with them.
I found this powershell script that can do exactly that and put it here for safe keeping

##############
# This script requires WMF version >= 5.0 -  Download WMF 5.1 from https://www.microsoft.com/en-us/download/details.aspx?id=54616
# The directory that has the files desired to be imported MUST HAVE TRAILING "\"
$path = "C:\onedrive_tt\Client\AbbVie\TCC_Touchpoints\doc\20190910-ResponseFiles\results\"
# Optional filter to only touch files with specified extension 
$mask = "*.csv" 
$ZipFileName= "CompressedBackup.zip"
#Filter to only touch files older than specified hours
$hours = 24
# Begin SMTP Server information 
# $SMTPPassword = "PlainText SMTP Password"
# $SMTPUser = "SMTP Authentication user, will also be SMTP From: address"
# $SMTPServer = "my.smtpserver.com"
# $SMTPTo = 'receive@myemail.com'
# $SMTPSubjectFail = "ZIP Folder script failed to run"
# $secpasswd = ConvertTo-SecureString $SMTPPassword -AsPlainText -Force
# $mycreds = New-Object System.Management.Automation.PSCredential($SMTPUser, $secpasswd)
# End SMTP Configuration
$problem = $false
$date = (Get-Date).tostring("yyyyMMdd")
#Pre setting error message to no issue, will change later in the script
$errorMessage="no issue"


#############


try {
    # Get all items from specified path, recursing into directories but not returning directories themselves. Excluding files with modificiation dates less than $hours
    $files = Get-ChildItem $path -Recurse -Include $mask | where {($_.LastWriteTime -lt (Get-Date).AddHours(-$hours)) -and ($_.psIsContainer -eq $false)} 
    foreach ($file in $files) {
        $Directory = $path + $file.LastWriteTime.Date.ToString('yyyyMMdd')
        if (!(Test-Path $Directory)) {
	        New-Item $directory -type directory
	    }
	    Move-Item $file.fullname $Directory
    }
    $folders = Get-ChildItem $path -Directory  | % { $_.FullName }
    foreach ($folder in $folders) {
        $zipname = $folder + $ZipFileName
        if (!(test-path $zipname)){
        compress-archive -LiteralPath $folder -DestinationPath $zipname
        }
        if (test-path $zipname){
        remove-item -Recurse $folder 
        }
        else {
        Write-host $zipname + "Did not create, so I'm not deleting" + $folder
        }
    }
}
catch {
    $problem = $true
    $errormessage = $_.Exception.Message
}
finally {
    if ($problem){
        Write-Host $errorMessage
        # Send-MailMessage  -SmtpServer  $SMTPServer -UseSsl -From $SMTPUser -To $SMTPTo -Subject $SMTPSubjectFail  -Body $ErrorMessage -Credential $mycreds  
    }
    else {
        Write-Host "Script ran sucessfully"
    }
}
Permalink | Comments Off on Powershell – Archive Log Files by Day
TCC

TCC – File Attachment Location by Candidate Visiblity

September 22, 2019 – 1:13 pm

File attachments are (of course) part and parcel of integration tasks. One of the things that needs to be understood is the difference between candidate visible attachments and candidate non-visible attachments.
This comes into play in two scenerios. Exporting candidate attachments and exporting application attachments as each case has candidate visible/non-visible attachments.

So to document where each is here is the breakdown:
CANDIDATE
If you’re only looking to pull candidate visible attachments, these are located here: Candidate,AttachedFiles,FileContent
To pull both candidate visible/non-visible attachments, those are located here: Candidate,TalentUser,Profile,AttachedFiles,FileContent
APPLICATION
If you’re only looking to pull candidate visible attachments, these are located here: Application,AttachedFiles,FileContent
To pull both candidate visible/non-visible attachments, those are located here: Application,ProfileInformation,AttachedFiles,FileContent

Permalink | Comments Off on TCC – File Attachment Location by Candidate Visiblity
TCC

TCC – Application Motive

September 18, 2019 – 4:11 pm

So one of the more painful points of pulling application data is to get at the motive. It’s hard because the motive is literally buried in the history of the application profile. Here’s a complex projection that can help with the excruciating process.

<quer:projection alias="CSWMotive" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
    <quer:projections>
      <quer:projection alias="Motive">
        <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.CSWMotives,Mnemonic"/>
      </quer:projection>
    </quer:projections>
    <quer:filterings>
      <quer:filtering>
        <quer:equal>
          <quer:field path="Number"/>
          <quer:field ownerQuery="MainQuery" path="Number"/>
        </quer:equal>
      </quer:filtering>
      <quer:filtering>
        <quer:equal>
          <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Step,Mnemonic"/>
          <quer:field ownerQuery="MainQuery" path="CSWLatestStep,Mnemonic"/>
        </quer:equal>
      </quer:filtering>
      <quer:filtering>
        <quer:equal>
          <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
          <quer:field ownerQuery="MainQuery" path="CSWLatestStatus,Mnemonic"/>
        </quer:equal>
      </quer:filtering>
      <quer:filtering>
        <quer:equal>
          <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Reverted"/>
          <quer:boolean>false</quer:boolean>
        </quer:equal>
      </quer:filtering>
      <quer:filtering>
        <quer:equal>
          <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
          <quer:query projectedClass="Application" alias="sqMaxDate" preventDuplicates="true" pagingsize="1">
            <quer:projections>
              <quer:projection alias="Motive">
                <quer:maximum>
                  <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
                </quer:maximum>
              </quer:projection>
            </quer:projections>
            <quer:filterings>
              <quer:filtering>
                <quer:equal>
                  <quer:field path="Number"/>
                  <quer:field ownerQuery="MainQuery" path="Number"/>
                </quer:equal>
              </quer:filtering>
              <quer:filtering>
                <quer:equal>
                  <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Step,Mnemonic"/>
                  <quer:field ownerQuery="MainQuery" path="CSWLatestStep,Mnemonic"/>
                </quer:equal>
              </quer:filtering>
              <quer:filtering>
                <quer:equal>
                  <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
                  <quer:field ownerQuery="MainQuery" path="CSWLatestStatus,Mnemonic"/>
                </quer:equal>
              </quer:filtering>
              <quer:filtering>
                <quer:equal>
                  <quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Reverted"/>
                  <quer:boolean>false</quer:boolean>
                </quer:equal>
              </quer:filtering>
            </quer:filterings>
          </quer:query>
        </quer:equal>
      </quer:filtering>
    </quer:filterings>
  </quer:query>
</quer:projection>
Permalink | Comments Off on TCC – Application Motive

« Previous Page — Next Page »

« Previous Page — Next Page »