The Ask
The client reached out to us due to the necessity of manually formatting and updating Excel workbooks daily, which consumed a significant portion of their time as they repeated the same task each day.
The Challenge
A client in the Financial Services sector received two Excel workbooks in their inbox each day. They were required to manually format these workbooks to upload them to a database through a webpage. Although they utilized some fundamental Excel formulas to aid in the data transformation, these formulas often malfunctioned whenever there were changes in the Excel file formats. This manual procedure consumed approximately one hour of the client’s time each day.
The Solution
We resolved this matter by establishing an O365 Shared Mailbox and advising the client to direct the pertinent emails to this mailbox. Subsequently, we created a Power Automate flow that activates automatically upon the receipt of a new email. When the flow identifies either of the Excel files within the email, it uploads these files to a SharePoint Library, executes an Office Script to create a new sheet in the workbook with the data formatted as specified, and then inputs the data directly into the database. Upon completion of this process, the flow sends an email from the Shared Mailbox to inform client stakeholders, including a link to the updated Excel workbook located in the SharePoint Library.
The Outcome
This solution optimized the client’s workflow by removing the necessity for manual data formatting, thereby greatly decreasing the daily time required for this task. The automated system is dependable and adaptable to variations in Excel file formats, guaranteeing consistent and precise data uploads to the database.