Excel Read Cell Raw Value

Hi. 

Was wondering if it was possible to use the Read Cell Excel Operation to read a date value (any value really) that has been formatted and to just get the raw (calculated value)

For example, we have a file with a date as DD/MM/YYYY (eg. 30/08/2023) but there is custom formatting of d-mmm which then renders it as 30-Aug

Is there any way to get the underlying 30/08/2023 value in this case?

Thanks Bruce

Hi Bruce, 

I was able to solve part of this puzzle by using DateReformat transformation . See LOOM VIDEO  


PS- Screenshot became heavy , hence the video !

Thanks. Good solution and can be adapted to my situation as well. In my case I was adjusting an existing package that had a Excel Operation to read a cell and then populate a package variable. You can do that with your solution as well. What I have done is create a script that takes looks for some parameter environment variables and then outputs another with the date we need.

This is then coupled with a Package Excel Operation Action to read the cell we are after.

I did it this before your reply only because setting up a transformation seemed to be a lot of heavy lifting, but certainly could be used here. Thanks for your time. CHeers

var dte: TDateTime;
var fmt_in: string;
var fmt_out: string;

begin

  fmt_in := GetVariable('<B2B_FromDateFormat>');
  fmt_out := GetVariable('<B2B_ToDateFormat>');

  dte := FormatStrToDate(GetVariable('<Excel Operation Cell Value>'), fmt_in);
  SetVariable('<B2B_AsOfDate>', FormatDateTime(fmt_out, dte));

  result := True;

end;

Hi Bruce,
I absolutely love scripts too. In-fact AETL can run python scripts too which, I use xlwings libraries which can help you manipulate excel. Using chatgpt , I put together a script which opens up a script , reads from cell 'B2' and updates its formatting . All in 1 single script  

LOOM



Script

If you ever want to jump into deep end of running python with AETL packages , here is the prompt I use . Copy paste this and simply ask chatgpt to give you a script of choice

Guidelines for ETL Python Scripts 
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 + "\n" + 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_string'))`
   - When writing CSV files, use `quoting=csv.QUOTE_ALL`
   - Always use raw strings for file paths: `r'C:\path\to\file'`
   - 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'.

Yes. Agree. Been cutting code since 1980. I have used Python used in AETL, just conscious of the load time. I have also replaced some VBA in Excel with xlwings to hookup a few things. Hopefully the team at etl-tools may extend on the python functionality over the Pascal implementation.

Cheers