Hi all!

1. I created a process "Import_ENG_MonthlyReport_Detail" to import xls data located on a file server.
\\domain\fs\Common\... ...\TM1_Export\MototHours\ENG_Monthly.xls
This process runs like a dream in PM

2. When I execute or run this process from another process then I get the error:
Error: Prolog procedure line (0): Unable to open data source "e:\servers\iss_assets\live\data\Import_ENG_MonthlyReport_Detail.blb".

I tried to use RunProcess or ExecuteProcess:
  RunProcess('Import_ENG_MonthlyReport_Detail', 'pYear', pYear, 'pMonth', pMonth);
  ExecuteProcess('Import_ENG_MonthlyReport_Detail', 'pYear', pYear, 'pMonth', pMonth);

3. Running this first process from the PAW environment (on-prem) also gives the same error.
- nb. when creating a process for the same purpose in the PAW seems not to be possible because the only option seems to be dropping a local file to the environment.

Can you please help me resolving the nr. 2 and 3 above?

------------------------------
Asgeir Thorgeirsson
------------------------------

#PlanningAnalyticswithWatson

Hi,

You've hit something a bit awkward here.
So natively TM1 just doesn't accept XLS files as a source, Performance Modeler came with a function that allowed you to do it but I *believe* the source engine was never changed and instead PM manually converted the file from XLS to CSV in the background (CSVs can be imported) - this probably however means that you need to be manually in PM for it to understand that it needs to action the conversion.

Ideally, if you could provide the XLS file in CSV format instead then it will work for you. Failing that you can look at somewhat "replicating" the work PM does, by adding a line that calls a bit of python script at the start of the TI process to convert the source file into CSV and then importing that. Note you would need to change the datasource options in your TI to point to the new filename/type.

However, I must admit to never using the PM functionality to import XLS files in anger so there may be someone else who can point out that it should work as is and its an easy tweak.

Thanks,

Declan

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------

Hi Asgeir

Another trick to read an XLS file directly is to create an ODBC connection to the Excel file. You can then read it using the ODBC data source option in the TI process, using a Select * from ... as though it was a table in a SQL database. I haven't done it for a while but it used to work perfectly. 

However, if you don't have a burning need to read directly from Excel then converting it to a CSV is probably easier.

Regards

Paul Simon

------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Wed January 20, 2021 08:37 AM
From: Declan Rodger
Subject: Import file from file server - ExecuteProcess not working

Hi,

You've hit something a bit awkward here.
So natively TM1 just doesn't accept XLS files as a source, Performance Modeler came with a function that allowed you to do it but I *believe* the source engine was never changed and instead PM manually converted the file from XLS to CSV in the background (CSVs can be imported) - this probably however means that you need to be manually in PM for it to understand that it needs to action the conversion.

Ideally, if you could provide the XLS file in CSV format instead then it will work for you. Failing that you can look at somewhat "replicating" the work PM does, by adding a line that calls a bit of python script at the start of the TI process to convert the source file into CSV and then importing that. Note you would need to change the datasource options in your TI to point to the new filename/type.

However, I must admit to never using the PM functionality to import XLS files in anger so there may be someone else who can point out that it should work as is and its an easy tweak.

Thanks,

Declan

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------