Python Script Error

Hello, yesterday this python script ran just fine inside of a package. All it does is move files from one destination to another and it's the only object inside of the package. This morning, I changed the destination path and now I get this error: 'Could not convert variant of type (Null) into type (OleStr)'

Even if I change the path back to what worked yesterday, I still get this error. The script runs just fine on it's own as .py file. I also tried having the execution agent run the .py file but I get an error saying the agent can't open the file even though it's all the same user. Any help would be appreciated!

I am open to learning how to do this using the native tools in the software, but I am still learning it and wanted a quick solution to move very specific files from multiple directories.

import os
import shutil
from datetime import datetime
def move_files(base_dir, dest_dir):
   
    if not os.path.exists(dest_dir):
        os.makedirs(dest_dir)

    today = datetime.now().date()
    # Loop through the variable parts of the path under base_dir
    for root, dirs, files in os.walk(base_dir):
        # Filter for the path that ends with 'Automations\EOB\Output Data\VSP'
        if 'Automations' in root and 'EOB' in root and 'Output Data' in root and 'VSP' in root:
            for filename in files:              
                if filename.startswith('all_') and filename.endswith('.835'):
                    source_file = os.path.join(root, filename)
                    modified_time = datetime.fromtimestamp(os.path.getmtime(source_file)).date()
                    if modified_time == today:
                        dest_file = os.path.join(dest_dir, filename)
                        try:
                            shutil.copy2(source_file, dest_file)
                        except Exception as e:
                            print(f'Failed to move {filename} from {root}: {e}')

base_directory = '{my source directory}'
destination_directory = r'{my destination directory}'
move_files(base_directory, destination_directory)

Hi.

I am glad you are making progress 

Assuming  you are talking about package action

AETL need to know if the execution was successful or not

This is done by assigning value 1 or 0 to the result

Please have a look the following article (towards the end)

www.etl-tools.com/wiki/knowledgebase/tips/working-with-python/

Please keep us posted on your progress



 

Thanks for the quick reply! The package runs as expected after assigning those values.

HI Nick, this is is my general PROMPT which works fabulous with chatgpt and Claude.ai to make/verify packages of python for AETL

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

Do you understand these guidelines? Please respond with 'yes' or 'no'.

Thank you Prashant. This a very helpful set of guidelines!