Hello!
I have an Office Script that extracts and transforms data from an Excel file into a list of lists that I'm eventually trying to write into a SQL Server table, with the thought of using the Write to CSV action to stage the data in a CSV and then write it to SQL Server.
My flow below gets a list of Excel files of various types (xlsx, xlsb, xlsm) from a SharePoint folder, extracts the data from one of the worksheets from each file and transforms it into a normalized list of data via an Office Script. The challenge is that while the script quickly and efficiently returns the needed data, it returns it as a list of lists and that's where I get stuck in trying to parse it into a data table.
Here's what the flow looks like before adding a Write to CSV action:
I get either an error or objects output as opposed to rows & columns when I put a Write to CSV action after the Run Script action because Run Script returns a list of lists, as follows:
I can't seem to figure out a good way to parse this list of lists into a data table to save it appropriately to any file type, other than using a "For Each" to add it to a new list, but with 9,518 items per import file it would take weeks at best to extract this data from all the import files.
My thought was to use a Write to CSV action to dump these lists into a file I could then easily import into SQL Server and then just re-write that file on each pass of the loop, but the CSV file only outputs the list objects as the only row of data:
Is there a way to parse that nested list to get the CSV output to write the data within each list as opposed to writing 9,518 columns of System.Collections.Generic.List'1[System.Object] as the response?
Thanks so much for your help!
