Inhaltsverzeichnis
This post and video will show how to combine Power Automate and Office Scripts to convert Excel file to CSV format and store in SharePoint. The approach shown only requires a few lines of code in the Office Script and it is leveraging standard connectors (no license required) and it doesn’t use any third-party app.
During the video, we go over common challenges such as dynamic data ranges, shared script management in SharePoint and pagination limits.
In einem unserer vorherigen Blogbeiträge haben wir gezeigt, wie dynamische Genehmigungen erstellt werden, die in einer Einzelne Anfrage zur gleichen Zeit, um mehr darüber zu erfahren, Schau es dir an.
Anwendungsfallbeschreibung
For this example, we’re working with a file that can have “N” number of rows (we don’t know how many rows we will get) and 5 columns. The file looks as follows:
The file is received in XLSX format and we want Power Automate to convert it into CSV format and save the output in SharePoint. Für dieses Video und diesen Blog arbeiten wir mit SharePoint-Listen, aber dieselbe Logik kann unabhängig von der referenzierten Datenquelle, wie SQL oder Dataverse, angewendet werden.
Lösungsbeschreibung
Preparing the Excel File and Creating the Office Script
- The Excel file is uploaded to SharePoint.
- We use the “Automate” tab to create the Office Script.
- We create an Office Script dynamically convert the flat data range into a table with a fixed name (“DataTable”) – the getUsedRange function is leveraged to handle the dynamic number of rows the file may have.
The script is saved and stored in OneDrive by default, but we move it to SharePoint.
Create the Power Automate Flow
- Hinzufügen Auslöserwhen a new file item is created in SharePoint folder (or any other trigger depending on your needs).
- Use the “Run script from SharePoint library” action to dynamically run the previously created Office Script on the newly uploaded Excel file.
- Use the “List rows present in a table” Excel action to read the rows from the dynamically created table.
- We have to enable Pagination with a row limit (e.g., 1,500) to accommodate large datasets beyond the default row limit for the Excel action.
- Using the Select action, columns are customized to exclude unwanted system-generated columns and optionally rename headers in the CSV output.
Finally, we add “Create file” SharePoint action to save the generated CSV in the same folder as the original Excel file.
Our expertise in Power Automate and Power Platform Beratungsleistungen enables us to tailor solutions for complex data transformations, like converting XLSX to CSV. With a focus on streamlining operations and enhancing efficiency, we ensure your automation projects are both seamless and impactful. Reach out to discuss how we can advance your initiatives.
Schritt-für-Schritt-Anleitung
Watch the video below to see a step-by-step walkthrough by Graciela, where she shows how to convert Excel files with flat ranges into CSV format:
Wie unsere Power Automate Entwicklungsdienste Ihnen helfen können
Combining Office Scripts and Power Automate creates an end-to-end automation that can be applied to a wide range of use cases. Unsere Power Automate und Power Platform Beratungsleistungen, we have implemented many automations that combine different tools from the Microsoft ecosystem. If you’d like some help working with Excel Online files and Office Scrips, Kontaktieren Sie uns, Wir würden uns sehr freuen, uns mit Ihnen unterhalten zu können und zu sehen, wie wir Ihr Team unterstützen können.
