PYCOMMAND command

Concept Information

Integrating Python scripts

Passes an Analytics table to an external Python script as a dataframe, and creates a new table in the Analytics project using the returned output from the Python script.

Syntax

PYCOMMAND <<FIELDS> field <...n>|<FIELDS> ALL> <IF test> <TO table_name> SCRIPT path_to_script <WHILE test> <FIRST range|NEXT range> <KEEPTITLE> <SEPARATOR character> <QUALIFIER character> <OPEN>

Parameters

Name Description

FIELDS field_name <...n> | FIELDS ALL

optional

The fields from the source Analytics table, or the expressions, to include in the dataframe that is sent to the Python script.

  • FIELDS field_name include the specified field, fields, or expressions

    Separate field names with spaces. Fields are exported in the order that you list them.

    To export a related field, specify a fully qualified field name (table_name.field_name). For example: Vendor.Vendor_Name

  • FIELDS ALL include all fields in the table

    Fields are exported in the order that they appear in the table layout. Related fields are not exported.
IF test

optional

 A condition that must be met to process the current record. The dataframe passed to the Python script contains only those records that meet the condition.
TO table_name

optional

The location to send the results of the command to:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

The output table is created from the dataframe that the Python script returns.
SCRIPT path_to_script

The full or relative path to the Python script on the file system. Enclose path_to_script in quotation marks.
WHILE test

optional

A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached.

Note

If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range | NEXT range

optional

The number of records to process:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

Use range to specify the number of records to process.

If you omit FIRST and NEXT, all records are processed by default.
KEEPTITLE

optional

Treat the first row of data as field names instead of data. If omitted, no field names are sent to the Python script.

This option is required if you want to retrieve data using column names in the Python script.
SEPARATOR character

optional

The character to use as the separator between fields. You must specify the character as a quoted string.

The default character is a comma.

Note

Avoid using any characters that appear in the input fields. If the SEPARATOR character appears in the input data, the results may be affected.
QUALIFIER character

optional

The character to use as the text qualifier to wrap and identify field values. You must specify the character as a quoted string.

The default character is a double quotation mark.

Note

Avoid using any characters that appear in the input fields. If the QUALIFIER character appears in the input data, the results may be affected.

OPEN

optional

Opens the table created by the command after the command executes. Only valid if the command creates an output table.

Examples

Roundtrip data between Analytics and a Python script

You send accounts payable data from Analytics to a Python script, perform some analysis or operations on the data in Python, and then export the Python results back to Analytics.

Analytics command

PYCOMMAND exports the specified accounts payable fields to the Python script testInOutput.py, waits while the script executes, and then saves the results from the Python script to a new Analytics table called AN_Python_roundtrip.

OPEN Ap_Trans
PYCOMMAND FIELDS Vendor_No Invoice_No Invoice_Date Invoice_Amount Prodno Quantity Unit_Cost SCRIPT "testInOutput.py" KEEPTITLE TO AN_Python_roundtrip.fil

This version of the command exports all fields in the Ap_Trans table to the Python script.

OPEN Ap_Trans
PYCOMMAND FIELDS ALL SCRIPT "testInOutput.py" KEEPTITLE TO AN_Python_roundtrip.fil

Python script (testInOutput.py)

import sys
import os
import logging
import pandas as pd
from acl_py_util import acl_py_util
from acl_py_util import logger

def main(args):
    error_file = os.getenv("ACL_PY_ERROR_FILE")
    if not error_file:
        error_file = os.getenv('LOCALAPPDATA') + "/acl_py_utl.error"

    # Import data from Analytics to Python
    # Use Python function included with Analytics
    df = acl_py_util.from_an()

    ''' Sample user code '''

    # Create acl_py_util log entry with column names and rows from dataframe
    logger.info(df)

    # Copy the original dataframe to a new dataframe
    user_df = df.copy()

    # Multiply the Invoice_Amount column by 2
    user_df.Invoice_Amount = user_df.Invoice_Amount * 2

    # Create acl_py_util log entry with column names and rows from dataframe
    logger.info(user_df)

    ''' End sample user code '''

    # Export data from Python to Analytics
    # Use Python function included with Analytics
    acl_py_util.to_an(user_df)

    # Create acl_py_util log entry recording script completion, and add an empty line
    logger.info(f"*** Python script completed successfully ***\n")

if __name__ == "__main__":
    main(sys.argv)

 

Use a Python script to import data to Analytics

You use a Python script to import accounts payable data to Analytics.

For the purposes of the example, the Python script generates some sample data. Typically, a script would import data from an external source to Python, perform analysis or file preparation in Python, and then export the results to Analytics.

Analytics command

PYCOMMAND calls the Python script testOutput.py and then saves the results from the Python script to a new Analytics table called Input_from_Python.

PYCOMMAND SCRIPT "testOutput.py" TO Input_from_Python.fil

Python script (testOutput.py)

import sys
import os
import logging
import pandas as pd
from acl_py_util import acl_py_util
from acl_py_util import logger 

def main(args):

    # Initialize sample accounts payable data
    data = {'Vendor_Number': ['12701', '10134', '11663'],
            'Vendor_Name': ['Harris Projects', 'Stars Trading', 'More Power Industries'],
            'Vendor_City': ['Baton Rouge', 'Milwaukee', 'Los Angeles'],
            'Invoice_Number': ['232556', '74841', '5986811'],
            'Invoice_Date': ['2024-10-22', '2024-10-22', '2024-10-23'],
            'Invoice_Amount': [2064.48, 18883.34, 1145.58],
            'Product_Number': ['030302903', '030302303', '090501551'],  
            'Quantity': [204, 458, 626],
            'Unit_Cost': [10, 41, 1]}

    # Create dataframe
    df = pd.DataFrame(data)

    # Create acl_py_util log entry with column names and rows from dataframe
    logger.info(df)

    # Sample user code

    user_df = df

    # End sample user code

    # Export data from Python to Analytics
    # Use Python function included with Analytics
    acl_py_util.to_an(user_df)

    # Create acl_py_util log entry recording script completion, and add an empty line
    logger.info(f"*** Python script completed ***\n")

if __name__ == "__main__":
    main(sys.argv)

Remarks

How PYCOMMAND works

PYCOMMAND passes an Analytics table to an external Python script as a dataframe, waits while the Python script executes, and then creates a new table in Analytics using the returned output from the Python script. You can use PYCOMMAND as many times as required in the course of an Analytics script.

Two Python functions are included with the Analytics installation and you use them in the Python script to input and output data:

  • Input dataacl_py_util.from_an() imports data from Analytics to Python

  • Output dataacl_py_util.to_an() exports data from Python to Analytics

Log files

Analytics creates two log files associated with PYCOMMAND. The logs can assist you with troubleshooting the integration between Analytics and Python, and with debugging errors in the associated Analytics and Python scripts.

The log files are stored in the Analytics project folder:

  • aclpython.log – logs Analytics error messages when PYCOMMAND fails

  • acl_py_util.log – logs informational messages output by the Python script

Set up acl_py_util.log

acl_py_util.log is a configurable log included with the Analytics installation. Follow the steps below to set up and configure the log.

Note

You must set up and configure the log individually for each Python script in which you want to use it.

  1. In the import statement block at the top of a Python script, add this statement:

    from acl_py_util import logger

  2. At any point in the Python script where you want to create a log entry, add this line:

    logger.info(Python_object)

    For example:

    • logger.info(dataframe_name.head(10)) creates a log entry with the column names and the first 10 rows from the current state of the named dataframe.

    • logger.info(f"*** Python script completed ***\n") inserted at the end of the script records script completion in the log, and inserts an empty line between log details for separate script runs.