Dynamics Community Forum Thread Details

Upload Excel file and update data in Data verse table

Hello SM-06021322-0,

Yes, it's possible to achieve this functionality of reading data from a file on a network drive and updating corresponding Dataverse records from a Canvas App, but it requires a combination of techniques and considerations. Here's a breakdown of the approach and potential challenges:

High-Level Approach:

File Selection (Canvas App):
- Use the Launch() function with a file picker dialog or a custom component to allow the user to select the file from the network drive.
File Reading (Power Automate):
- Trigger a Power Automate flow from the Canvas App, passing the file path or content.
- Use Power Automate's file system connectors or custom connectors to read the file's content.
Data Parsing (Power Automate):
- Parse the file's content (e.g., CSV, Excel, TXT) to extract the relevant data.
Dataverse Update (Power Automate):
- Use the Dataverse connector in Power Automate to update the corresponding records in the Dataverse table.

Detailed Steps:
1. Canvas App - File Selection:

File Picker (Limited):
- The built-in file picker in Canvas Apps is designed for local files, not network drives directly.
- You might need to explore browser-specific file picker options or create a custom component using Power Apps component framework (PCF).
Launch Function (Workaround):
- You could use the Launch() function to open a file explorer window, but this won't directly return the file content to the app.
- The user would need to manually copy the file path and paste it into a text input in the Canvas App.
Custom Component (Recommended):
- For a more seamless experience, consider creating a custom component using PCF.
- This component could use JavaScript to interact with the browser's file system API and allow the user to select files from a network drive.
- This is the most complex but most flexible solution.

2. Power Automate - File Reading:

Trigger from Canvas App:
- Use the "Power Apps" trigger in Power Automate to initiate the flow from the Canvas App.
File System Connectors (If Applicable):
- If the network drive is accessible through a supported file system connector (e.g., SharePoint, OneDrive), you can use that connector to read the file.
- If not, you might need to use a custom connector or custom code.
Custom Connector (Advanced):
- You can create a custom connector to access the network drive if it's accessible through a REST API.
Custom Code (Advanced):
- If the network drive is not accessible through a connector, you might need to use custom code (e.g., Azure Functions) to read the file.

3. Power Automate - Data Parsing:

Parse CSV/Excel/Text:
- Use Power Automate's built-in actions to parse the file content based on its format (CSV, Excel, TXT).
JSON Parsing (If Applicable):
- If the file contains JSON data, use the "Parse JSON" action.

4. Power Automate - Dataverse Update:

Dataverse Connector:
- Use the Dataverse connector's "Update a row" action to update the corresponding records in the Dataverse table.
Matching Records:
- You'll need to determine how to match the data from the file to the records in Dataverse (e.g., based on a unique ID or other criteria).

Challenges and Considerations:

Network Drive Accessibility:
- The most significant challenge is ensuring that the network drive is accessible from Power Automate.
- This might require configuring network gateways, custom connectors, or custom code.
File Format:
- The file format will determine how you parse the data in Power Automate.
Security:
- Consider the security implications of accessing files on a network drive.
Performance:
- Reading and processing large files can impact performance.
Error Handling:
- Implement robust error handling to handle potential issues with file access, parsing, or Dataverse updates.

Important Notes:

Browser Security: Browsers have security restrictions that prevent direct access to local or network files.
Power Automate Limitations: Power Automate's file system connectors have limitations, especially when dealing with network drives.

Key Recommendations:

Custom Component (PCF): If possible, create a custom component for file selection.
Power Automate for Data Processing: Use Power Automate to read, parse, and update Dataverse data.
Careful Planning: Plan the data flow and error handling carefully.

This is a complex scenario, but it is achievable with the correct design and implementation.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,
Daivat Vartak

Quick Links

Leaderboard