PowerShell

Powershell – Deleting Empty CSV Files

December 7, 2020 – 4:04 pm

Sometimes in integration, you have jobs that run every 5 minutes or so, now these jobs run regardless if there’s data or not and they’ll produce an ’empty’ file. That’s to say there isn’t any usable data in it, just a header row. So you have one file every 5 minutes, that’s 12 an hour or 288 in a day. Some are going to have date, others not. While this can be done with a batch file (elsewhere on this site), Powershell offers a quicker way to do the same thing. Below is a Powershell script that looks through CSV’s and kills ones with only one line of data (header) leaving anything with real data in place.

$path="C:\onedrive_tt\Client\TCC_Touchpoints-Maintenance\scripts\20201207-CleanupTest\response\"
Get-ChildItem $path -Filter *.csv |
ForEach-Object {
$content = $path+"$_"
[int]$LinesInFile = 0
$reader = New-Object IO.StreamReader $content
 while($reader.ReadLine() -ne $null){ $LinesInFile++ }
$reader.Close()
#Write-Host($content+" - "+$LinesInFile)
if ($LinesInFile -eq 1)
{
#Write-Host("File: "+$content+" with "+$LinesInFile+" line will be deleted")
Remove-Item -Path $content -Force
}
}
Permalink | Comments Off on Powershell – Deleting Empty CSV Files
TCC

TCC – Employee Net-Change Musings

November 20, 2020 – 2:51 pm

If you’ve ever done time with TCC you’ll know that the bane of the out of the box integration possibilities is the employee net-change. This is because it’s a marriage of candidate and user and depending on both of those tables to be clean and ready for this is asking a lot.
I ran across a MOS article that actually made clear about the lookups for those two tables. It’s article Doc ID 1047682.1 and here is the PDF defining the candidate/user update columns in the employee net-change sample.

Doc-ID-1047682.1Download
Permalink | Comments Off on TCC – Employee Net-Change Musings
TCC

TCC – Text in Export Spec (warning)

November 9, 2020 – 4:37 pm

A long time ago I posted the code to be able to put test in a column in an export specification. This one in case you need to read the original.
There is an oddity with this in that if you alias “Comment” you will get a sax parsing error. The specific error is: “ORA-00936: missing expression”. I’m not sure why but you have to alias your comment as something other than “Comment”

8/20/2021 – Just found out an alias of Union will do the same thing.

Permalink | Comments Off on TCC – Text in Export Spec (warning)
TCC

TCC – What Templates Are Active

November 2, 2020 – 3:29 pm

So this is specifically for job templates but can be adapted to anything that needs to find out what languages are active for any given situation.

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="RC1704" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobTemplate" locale="en" mode="CSV" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:subQueries/>
	<quer:projections>
		<quer:projection>
			<quer:field path="Number"/>
		</quer:projection>
		<quer:projection>
			<quer:field path="JobCode"/>
		</quer:projection>
		<quer:projection>
			<quer:field path="State,Description"/>
		</quer:projection>
		<quer:projection alias="Lang_fr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr"/>
		</quer:projection>
		<quer:projection alias="Lang_en">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en"/>
		</quer:projection>
		<quer:projection alias="Lang_es">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="es"/>
		</quer:projection>
		<quer:projection alias="Lang_de">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="de"/>
		</quer:projection>
		<quer:projection alias="Lang_it">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="it"/>
		</quer:projection>
		<quer:projection alias="Lang_nl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="nl"/>
		</quer:projection>
		<quer:projection alias="Lang_fr_FR">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr-FR"/>
		</quer:projection>
		<quer:projection alias="Lang_en_GB">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en-GB"/>
		</quer:projection>
		<quer:projection alias="Lang_zh_CN">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-CN"/>
		</quer:projection>
		<quer:projection alias="Lang_ja">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ja"/>
		</quer:projection>
		<quer:projection alias="Lang_pt_BR">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt-BR"/>
		</quer:projection>
		<quer:projection alias="Lang_sv">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sv"/>
		</quer:projection>
		<quer:projection alias="Lang_da">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="da"/>
		</quer:projection>
		<quer:projection alias="Lang_pl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pl"/>
		</quer:projection>
		<quer:projection alias="Lang_ru">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ru"/>
		</quer:projection>
		<quer:projection alias="Lang_fi">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fi"/>
		</quer:projection>
		<quer:projection alias="Lang_cs">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="cs"/>
		</quer:projection>
		<quer:projection alias="Lang_zh_TW">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-TW"/>
		</quer:projection>
		<quer:projection alias="Lang_ko">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ko"/>
		</quer:projection>
		<quer:projection alias="Lang_th">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="th"/>
		</quer:projection>
		<quer:projection alias="Lang_ms">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ms"/>
		</quer:projection>
		<quer:projection alias="Lang_ca">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ca"/>
		</quer:projection>
		<quer:projection alias="Lang_tr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="tr"/>
		</quer:projection>
		<quer:projection alias="Lang_sk">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sk"/>
		</quer:projection>
		<quer:projection alias="Lang_et">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="et"/>
		</quer:projection>
		<quer:projection alias="Lang_no">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="no"/>
		</quer:projection>
		<quer:projection alias="Lang_bg">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="bg"/>
		</quer:projection>
		<quer:projection alias="Lang_hr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hr"/>
		</quer:projection>
		<quer:projection alias="Lang_hu">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hu"/>
		</quer:projection>
		<quer:projection alias="Lang_ro">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ro"/>
		</quer:projection>
		<quer:projection alias="Lang_el">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="el"/>
		</quer:projection>
		<quer:projection alias="Lang_sr">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sr"/>
		</quer:projection>
		<quer:projection alias="Lang_lt">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="lt"/>
		</quer:projection>
		<quer:projection alias="Lang_pt">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt"/>
		</quer:projection>
		<quer:projection alias="Lang_sl">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sl"/>
		</quer:projection>
		<quer:projection alias="Lang_in">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="in"/>
		</quer:projection>
		<quer:projection alias="Lang_ar">
			<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ar"/>
		</quer:projection>
	</quer:projections>
	<quer:projectionFilterings/>
	<quer:filterings>
		<quer:filtering>
			<quer:equal>
				<quer:field path="State,Description"/>
				<quer:string>Active</quer:string>
			</quer:equal>
		</quer:filtering>
	</quer:filterings>
	<quer:sortings>
		<quer:sorting ascending="true">
			<quer:field path="JobCode"/>
		</quer:sorting>
	</quer:sortings>
	<quer:sortingFilterings/>
	<quer:groupings/>
	<quer:joinings/>
</quer:query>
Permalink | Comments Off on TCC – What Templates Are Active
PowerShell

TCC & Powershell – CSV to Set Filters

October 26, 2020 – 4:41 pm

If you’ve been working with TCC for any length of time you’ll know the purple squirrel, rainbow unicorn and pot at the end of the rainbow is being able to set the filters based on a CSV. This ability would mark a boon for automating ad hoc integrations.

Now there are options, Python could pull it off but Python doesn’t live and play on a stock server I run across 95% of the time. Powershell does however, well it usually lives there, not sure how much play is involved.

In any case, it turns out we can do this whole process in Powershell without a whole bunch of headache. 

#Declare variables
$sourcePath = "..\..\scripts\mappingFiles\mappingContestNumberToJobCode.csv"
$targetPath = "..\..\scripts\mappingFiles\mappingFilterList.txt"
$targetContent = @()
$outputContent = New-Object System.Text.UTF8Encoding $false
#Loop through source file to build filter
Import-Csv $sourcePath | ForEach-Object {
    $targetContent += "<quer:string>$($_.Requisition)</quer:string>"
}
#Write out file as UTF8 no BOM
[System.IO.File]::WriteAllLines($targetPath, $targetContent, $outputContent)
#Update export specificaitons with the new filter set
((Get-Content -path ..\..\scripts\RequisitionExport\RequisitionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionExport\RequisitionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionMappingExport\RequisitionMappingExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionCollaboratorExport\RequisitionCollaboratorExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOpenExport\RequisitionOpenExport_sq.xml;
((Get-Content -path ..\..\scripts\SourcingRequestExport\SourcingRequestExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\SourcingRequestExport\SourcingRequestExport_sq.xml;
((Get-Content -path ..\..\scripts\CandidateMatchExport\CandidateMatchExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\CandidateMatchExport\CandidateMatchExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionQuestionExport\RequisitionQuestionExport_sq.xml;
((Get-Content -path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExportTemplate_sq.xml -Raw) -replace 'FilterList', (Get-Content -path  ..\..\scripts\mappingFiles\mappingFilterList.txt -Raw)) | Set-Content -Path ..\..\scripts\RequisitionOtherLocationExport\RequisitionOtherLocationExport_sq.xml;
Permalink | Comments Off on TCC & Powershell – CSV to Set Filters
TCC

TCC – LRD when it doesn’t exist

October 19, 2020 – 6:02 pm

As you know the automated process for producing exports relies heavily on the last run date. I’ve always been curious as to what happens when you pull the trigger on one that uses it without a lrd, turns out if there is no lrd file is gives you the last 24 hours and instantiates the lrd. I’ll sleep better tonight knowing this.

Permalink | Comments Off on TCC – LRD when it doesn’t exist
Python

Python – Reading CSV as input

August 10, 2020 – 9:43 pm

One of the more fascinating aspects of python is it’s file manipulation capabilities. I have from time to time needed to process (create) 160 or so new batch files to call warehouse integrations. With proper naming convention in development, this process is handled with 21 lines of python code. Love it!!!

with open('_RecruitingNewExport.csv') as file:
  for line in file:
    line = line.replace("\n", "")
    outputLineTwo = line[:-6]
    with open("Recruiting_"+str(line)+".bat", "w") as file:
        file.write("REM This batch file executes the "+str(line)+" integration\n")        
        file.write("\n")
        file.write("REM Set directories and variables\n")
        file.write("@ECHO OFF\n")
        file.write("cd /d \"%~dp0\"\n")
        file.write("Call Environment.bat\n")
        file.write("TITLE %~nx0 – %TALEO_HOST% – %date% %time%\n")
        file.write("SET timeStamp=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%\n")
        file.write("SET timeStamp=%timestamp: =0%\n")
        file.write("\n")
        file.write("REM Run the export integration\n")
        file.write("Call core\TCC.bat \"%SCRIPTS_FOLDER%\\CandidateExportScripts\\"+str(line)+"\\"+str(line)+"_cfg.xml\" \"%SCRIPTS_FOLDER%\\CandidateExportScripts\\"\
                   +str(line)+"\\"+str(line)+"_sq.xml\" \"%OUTBOUND_FOLDER%\\"+str(outputLineTwo)+"_%timestamp%.csv\"\n")
        file.write("Exit /B %ERRORLEVEL%\n")
        file.write("\n")
  print()
Permalink | Comments Off on Python – Reading CSV as input
Taleo Web Services

Taleo SSO Metadata URL

July 8, 2020 – 1:35 pm

I can’t for the life of me keep track of this so here it is so I know where to look for it.

https://ZONE.taleo.net/smartorg/sp/metadata.jss?target={ENTITY-ID}

https://ZONE.taleo.net/careersection/sp/metadata.jss?target={ENTITY-ID}

Permalink | Comments Off on Taleo SSO Metadata URL
Batch

File – Data or No Data

May 26, 2020 – 10:22 am

A lot of the work we do for integration is of course automated. Sometimes we have to extract a data set and then use that export as the import to another integration. This is fine and well if there is data but if you have to run it every, let’s say, 5 minutes, there may very well be a situation where there isn’t anything to pick up. The matter is complicated by the fact that even if there isn’t any information there will be a header row.
This file is now completely useless and if left in place it will run through the process and wind up in the archive and produce a results file meaning you have to dig through empty files to try to find what’s going on.
To fix this I came across a batch file that will check the file and delete it if it has only a header row, a further check makes sure that that if any data is there is ignores the file. For your consideration and my reference here is the file that does this.

@echo off
setlocal enableExtensions disableDelayedExpansion

for %%a in ("..\..\data\inbound\*.csv") do (
   call :checkFile "%%~a"
   if errorlevel 1 del %%~a 
   echo File contains no content
)
endlocal
goto :eof


:checkFile
:: %~1   name of the file to check
:: returns 0 if file contains data, else 1 (== header only).

:: if file contains more than one line: data found
for /F "tokens=2 delims=:" %%a in ('find /c /v "" "%~1"') do (
   for /F "delims= " %%b in ("%%~a") do (
      if not 1 == %%~b exit /b 0
   )
)

:: if only line does not start with "Ident", then line contains data
for /F %%a in ('findstr /v "^Ident" "%~1"') do (
   exit /b 0
)

:: header only
exit /b 1
Permalink | Comments Off on File – Data or No Data
TCC

TCC – Date Certain Step/Status Achieved

April 29, 2020 – 12:31 pm

I ran into an issue trying to get everyone within the last 24 hours that had achieved a certain step/status combination. The hitch was that they could have been moved out of the target step/status so we have to muck around in the history to get it. As it’s a date you need to make sure the projected value you’re comparing to is a date as well since the last 24 hours is always going to be date time. This did the trick. Remember to pay attention to the value types in sub queries like this.

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:greaterThan>
		<quer:query projectedClass="Application" alias="sqLocation" preventDuplicates="true" pagingsize="1">
			<quer:projections>
				<quer:projection alias="AnotherDate">
					<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
				</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:string>RequiredStep</quer:string>
					</quer:equal>
				</quer:filtering>
				<quer:filtering>
					<quer:equal>
						<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
						<quer:string>RequiredStatus</quer:string>
					</quer:equal>
				</quer:filtering>
				<quer:filtering>
					<quer:equal>
						<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
						<quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
							<quer:subQueries/>
							<quer:projections>
								<quer:projection>
									<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:string>RequiredStep</quer:string>
									</quer:equal>
								</quer:filtering>
								<quer:filtering>
									<quer:equal>
										<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
										<quer:string>RequiredStatus</quer:string>
									</quer:equal>
								</quer:filtering>
							</quer:filterings>
						</quer:query>
					</quer:equal>
				</quer:filtering>
			</quer:filterings>
		</quer:query>
		<quer:castAsDate>
			<quer:addDays>
				<quer:date type="now"/>
				<quer:integer>-1</quer:integer>
			</quer:addDays>
		</quer:castAsDate>
	</quer:greaterThan>
</quer:filtering>
Permalink | Comments Off on TCC – Date Certain Step/Status Achieved

« Previous Page — Next Page »

« Previous Page — Next Page »