I am seeking advice on a smoother way to connect different languages/software together in a flow for a business problem I am trying to solve in my organization.
Business Question: Currently the only source of data stored for a specific process in our organization is in Excel, and this data is critical for tracking our expenses. I would like to create, implement, and engineer a business process that enables user to enter data onto an online form which writes-back data to the database I create off of the existing Excel file. This form should have data validation criteria and be able to populate rest of the data based on some critical data the user enters.
Off of this database I'd like to use Python to create multiple PDF's (Price Lists) and save them onto the designated locations. I am currently using the following libraries: 1) pywin32.client to replace VBA and create pivot tables, os module for navigating directories.
Currently, the available tools in our organization include Microsoft suite so I have access to all the Power Platforms but not the premiums. I am familiar enough with Python and SQL as well and am open to suggestions on the best tech stack for this business use-case.
Here is what I have in mind so far:
| Process | Preliminary Tools | Question |
|---|---|---|
| 1. Clean data | PowerQuery OR Python pandas library |
Is it possible to use PowerQuery output as one of the Flows in Power Automate? Using pandas would simplify a bit of this tech stack but I already have PowerQuery set up. |
| 2. Create a database from .xlsx | SSMS OR dbt | Our organization doesn't have or use dbt. I believe this is free to use but if I use it, would it trigger security concerns? |
| 3. Create multiple PDF contracts | Python pywin32 |
Is pywin32 the best tool to do this job (platform continuity, ease of use)? |
| 4. Save these PDF contracts to the designated locations | os module with some for-loops |
Thank you for your help!