Working with Python

Preparation

Download and install python from https://www.python.org/downloads

Note: that for conda distributions to work properly, you need to add Format(’%s;%0:s\Library\bin;’, [Version.InstallPath] to your Windows path if it is not there already.

Python Version

• Our software automatically detects the version of python installed, and loads relevant DLL.
• 32-bit version of our software uses the 32-bit version of python and
• 64-bit version of our software uses the 64-bit version of python

Transforming data using Python

Here is a very basic example where we are converting a string into the upper case using python

Notes

• Input variables are passed into the script as strings by default.
• These variables can be referred to directly in the calculations.
• The user may convert input strings to the required types (e.g., integer, array) within the script.
• Using scripts is the slowest way to transform the data and it should be avoided.
• If you do not know how to transform the data ask the question on our support forum and we will help you.

Using Python inside package

The script checks a number of the day and shows an appropriate message

Execution Result

The execution result is determined by Result.Value, Result.Value=1 means success and Result.Value=0 means failure

etltools package

“etltools” package provides a way of communication between python and our software.
It is only available when python is run from our software.

Supported functions:

etltools.GetVariable('#VariableName#')
etltools.SetVariable('#VariableName#','Important Value')
etltools.WriteToLog('Important Message')
etltools.ExecuteObject(ObjectID)

Use ExecuteObject to execute object from the script inside the package

It returns the result of the package/transformation execution that was called.

• 0 = success
• 1 = failure

• Command Line Interface

ChatGPT/Claude.ai prompt

Guidelines for ETL Python Scripts (Windows Environment)

1. Always start with import etltools

2. Variable handling:

   • Retrieve: variable = etltools.GetVariable('variable_name')
   • Set: etltools.SetVariable('variable_name', value)
   • All inputs and outputs are strings
   • No need to initialize variables

3. Logging and error handling:

   • Use etltools.WriteToLog('message') for all logging, including errors
   • Example: etltools.WriteToLog(f'Error occurred: {str(e)}')
   • Only if specifically requested, use a variable for error messages: python current_errors = etltools.GetVariable('error_message') etltools.SetVariable('error_message', current_errors + " " + str(new_error))

4. Data handling and Windows optimization (Python 3.12):

   • Always assume UTF-8 encoding for input and output
   • When opening files: open(file_path, 'r', encoding='utf-8')
   • JSON handling:
     • All inputs are strings, so JSON must be properly loaded:
     • json_value = json.loads(etltools.GetVariable('<json_variable>))
   • When writing CSV files, use quoting=csv.QUOTE_ALL
   • Always use raw strings for file paths: r'C:\path oile'
   • Use Windows-specific libraries when appropriate
   • Be aware of path separators and line endings
   • Consider Windows file system peculiarities (e.g., file locking)

5. Script success/failure:

   • Use Result.Value = 1 for success, Result.Value = 0 for failure

6. Script structure:

   • For complex scripts, use a main() function
   • For simple scripts, omit main() and run everything directly
   • In both cases, ensure proper error handling and set Result.Value appropriately

7. If using webhooks:

   • Set Result.Value = 1 if response.status_code in [200, 202], else 0
   • For multiple webhooks, use time.sleep(0.001) between requests

More Examples

• Extracting text from PDF file
• Extracting data from PDF tables
• Extracting Information from MSG Files

Additional information

• The script is executed from top to bottom without needing any special entry point like main().
• The result must always be assigned to Result.value. This is case-sensitive; using result.value will not work.Always assign the final result to Result.value.
• The output should not be printed; it must be assigned to Result.value.
• There is no need to initialize the Result class; it is always initialized automatically.