Hi , We have a requirement where we want Celonis to automatically pick a XLSX file from a folder, upload the file , create a table and execute a data Job. IS this possible ? How do we accomplish this ?
Best answer by guillermo.gost12
Thank you ! This helps. Is there a python code snippet to extract a file and trigger a data job readily available ?
A code snippet? Not to my knowledge.
To get the excel file it will depend on what's the repository.
To read the excel and convert as dataframe, use pandas (you will need also openpyxl for the engine:
import pandas as pd
import openpyxl
dfws=pd.read_excel(filename, sheet_name=table, engine='openpyxl')
Then use pyCelonis to select the pool where you want to work, and create the table with pool.create_table(dfws,tablename)
To trigger the datajob execution, use the call execute() on the datajob object
HTH
2 approaches: Action Flows or scripting via ML workbench. I prefer ML as I find AF a bit complex (specially as you need to combine AF and Skills)
In AF will be something like this:
and then the skill called something like this
Note that you need to invoke a ML script in order to trigger the execution of the data job (I couldn't find other way). That's why I prefer using ML only, as it allows me more control and I avoid having to mix AF, skills, and ML
HTH
Thank you ! This helps. Is there a python code snippet to extract a file and trigger a data job readily available ?
Thank you ! This helps. Is there a python code snippet to extract a file and trigger a data job readily available ?
A code snippet? Not to my knowledge.
To get the excel file it will depend on what's the repository.
To read the excel and convert as dataframe, use pandas (you will need also openpyxl for the engine:
import pandas as pd
import openpyxl
dfws=pd.read_excel(filename, sheet_name=table, engine='openpyxl')
Then use pyCelonis to select the pool where you want to work, and create the table with pool.create_table(dfws,tablename)
To trigger the datajob execution, use the call execute() on the datajob object
HTH
Where that folder is located? In Celonis/ML workbench or rather in sharepoint? In other case it's much harder to get it as much more steps are requred, however it's possible and working well.
Where that folder is located? In Celonis/ML workbench or rather in sharepoint? In other case it's much harder to get it as much more steps are requred, however it's possible and working well.
For now we are looking at loading the file into ML workbench. But the ideal solution would be to upload through a sharepoint folder or something. Can you walk me through the steps please ?
A code snippet? Not to my knowledge.
To get the excel file it will depend on what's the repository.
To read the excel and convert as dataframe, use pandas (you will need also openpyxl for the engine:
import pandas as pd
import openpyxl
dfws=pd.read_excel(filename, sheet_name=table, engine='openpyxl')
Then use pyCelonis to select the pool where you want to work, and create the table with pool.create_table(dfws,tablename)
To trigger the datajob execution, use the call execute() on the datajob object
HTH
Thank you Guillermo Gost ! This helped and it worked.
Hi,
1) First you need to create go to Azure portal and create App there, here's the guide
https://docs.celonis.com/en/microsoft-sharepoint-connection.html
2) You'll have to use app_id, tenant_id, client_id and client_secret to create so called "msal.ConfidentialClientApplication" object: here you've got authorization code snippets: https://stackoverflow.com/questions/71862246/how-to-use-ms-graph-api-and-python-to-add-a-client-secret-to-aad-application
Using that you will be able to receive bearer token which will be used in every API call.
3) Next step is to get Sharepoint SiteURL. You can find SiteURL by sending api requests with URL: https://graph.microsoft.com/v1.0/sites/?$search="<INSERT SITE NAME>"'.
4) After having SiteURL you can try to use published Celonis functions, however they are not working out of box, and by sake of simplicity of the maintenance I've rewritten them. Unfortunately I cannot share these. Publicly available functions are available here: Ready functions are available here: https://files.celonis.de/download.php?id=11912&token=WXWVLlpdc2T0Chl2rhgjrX8ItRpPgttk
5) Main steps here is to first get file ID by name, and also DriveID. Then get file using that information. After getting the file you'll need to interact with that excel file to retrieve information needed. I can recommend storing data only in Excel tables (Insert: table feature) as it's far easily to get data out of that. Helpful article: https://blog.atwork.at/post/Access-files-in-OneDrive-or-SharePoint-with-Microsoft-Graph-and-the-Excel-API
6) Then you need to transform response in JSON into DF. Code snippets how to do it can be found on Celopeers, and in the link mentioned in next point. DF stands for Pandas Data Frame.
7) Data frame need to be pushed as table, or you can use different options as for example upsert command. More info: https://celonis.github.io/pycelonis/1.7.1/tutorials/api_tutorial/02_Pushing_and_Editing_Data_in_Pool/#2-find-the-data-pool
As you can see there's a lot of catches and research needed, I doubt if anyone will provide you with ready solution.
+262 approaches: Action Flows or scripting via ML workbench. I prefer ML as I find AF a bit complex (specially as you need to combine AF and Skills)
In AF will be something like this:
and then the skill called something like this
Note that you need to invoke a ML script in order to trigger the execution of the data job (I couldn't find other way). That's why I prefer using ML only, as it allows me more control and I avoid having to mix AF, skills, and ML
HTH
Hi @Guillermo Gost,
I came across this example, you can use the Data Push API directly in the action flow, so that a skill is not necessary anymore: Create and Push CSV to Data Integration (celonis.com).
Hi @Guillermo Gost,
I came across this example, you can use the Data Push API directly in the action flow, so that a skill is not necessary anymore: Create and Push CSV to Data Integration (celonis.com).
Thanks a lot @janpeter.van.d, I didn't know about that solution! :)
However it a bit contrary to an idea of action flow (AF as automation way for less technical users) - as you can see that AF is pretty complicated, and you have a much less control over the data flow (Extracting AF logs, graphical interface as limitation, limited data transformations compared to python), but it still require pretty vast IT knowledge (using webhooks, creating custom JSON structures and creating HTTP requests based on that), however it's good to have that option in your arsenal!
Hi @Guillermo Gost,
I came across this example, you can use the Data Push API directly in the action flow, so that a skill is not necessary anymore: Create and Push CSV to Data Integration (celonis.com).
I think AF is more oriented to automate the response after Celonis has identified any action. Thus, not well suited to data ingestion scenarios like this one.
That's how I see it... of course, it is just another opinion.
Under that view, it is an "antipattern" to use AF to ingest data into a data model from a external source
But it is ok to get an excel to "complement" data that comes from the process-model. I.e. use a excel to add information to the records we got from Celonis. Or to get a list of destinations for the notifications, etc...
Again, just my 0,02 €
BTW... anybody happen to know any "architectural" source for Celonis? Like a collection of patterns and antipatterns for data ingestion, analytics, AF, etc... ?????
+26Hi @Guillermo Gost,
I came across this example, you can use the Data Push API directly in the action flow, so that a skill is not necessary anymore: Create and Push CSV to Data Integration (celonis.com).
Hi @deleted deleted,
I totally agree, it's not a simple out-of-the-box solution unfortunately. However, because it is listed in the Studio itself, I find it a more transparent solution than Python (which I still use for more advanced use cases) since all the components are listed within the same package. Also, If somebody is not familiar with Python, it takes some effort to get to know to the syntax, which is not neccessary for the Action Flow.
+26Hi @Guillermo Gost,
I came across this example, you can use the Data Push API directly in the action flow, so that a skill is not necessary anymore: Create and Push CSV to Data Integration (celonis.com).
Hi @Guillermo Gost,
We also use Action Flows more often to handle signals from Celonis indeed. However, the Celonis example shows us that it isn't limited to, and can handle more general RPA kind of tasks.
Regarding your question, the diagram in the KM docs page (see Knowledge Model (celonis.com)) comes the closest to an architectural picture. However, it is very simplified and this example to ingest data via Action Flows is not covered in it. Since this type of data ingestions are very hidden in the docs and not in the courses (which makes sense in my opinion since it is kind of a work around), I would say this is the reason that platforms as Celopeers exists ;)
Kind regards,
Jan-peter
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.