Pulling data from unformatted excel spreadsheet into formatted table in another document

[ShAnc]

Desired Outcome

We have a daily export of data from a WordPress website that creates an excel file in a OneDrive environment. The data from this spreadsheet should then be uploaded to a SharePoint list.

What Works

• I'm able to create the import file in the OneDrive environment using another workflow automation package that integrates with WordPress
• Uploading data from import file to a SharePoint list and then deleting rows from the same file
• In theory, the email notification works (I've built these in the past, so I'm confident it won't be a problem)

Problem

• The export file is in xlsx, but it is not formatted as a table. As such, the "Get Rows" action in MS Flow is not able to fetch the content and move it to the import file

Attempted Solutions

• I've tried to build a flow that triggers when a file is created in OneDrive. Once triggered, it creates a table in the Excel file. I then use a filter array to remove blank rows and get row content from the body of the array. In theory, I should then be able to write that content to the import file and trigger the rest of the sequence. Outline below:

• This is the latest iteration of the flow. In another version I had included a filter that only triggered the next steps in the flow if the "File Name" field conained the keywords "website_export". The Flow always read as successful in that it didn't recognize the "File Name" field as having the keywords mentioned above.

• I always get one of the following errors: Status 400: The parameter 'drive' has an invalid value, or Status 404: Bad request. (the latter relating to the use of the "File Path" variable)
• The issue is that the file in question is always going to have a consistent filename (that includes "website_export") and the table I'm creating is always going to be called "climport".  I have tried another type of flow that's triggered manually and creates a table, but then referring to the table by its "name" variable (or even by the term "climport" consistently triggers another error (this happened yesterday and I didn't document the error. I can recreate it if necessary, but I'm hoping that I'm just missing something pretty obvious to the experts in this community.

Thanks in advance for your help.

[AlanPs1]

Hi @ShAnc 

Thank you for the detailed post.

If I understadning correctly, you say:

"

Problem

• The export file is in xlsx, but it is not formatted as a table. As such, the "Get Rows" action in MS Flow is not able to fetch the content and move it to the import file

"

Have alook at the below blog post from @Pieter_Veenstra . I haven't had a chance to work through it myself but it seems to suggest "No need to use data tables in Excel."

https://veenstra.me.uk/2019/03/04/microsoft-flow-custom-connectors-how-to-read-an-excel-file-from-sh...

If you have found my post helpful, please mark thumbs up.

If this post has solved your problem, please click "Accept as Solution".

Any other questions, just ask.

Thanks, Alan

[ShAnc]

Hi Alan,

Thanks for the suggestion. This is great info and I'll make use of it some time for sure. For the problem I have listed above, I ended up finding a temporary manual process that feeds into a powerapps collection. Both the business user and myself are satisfied with it, but I'll definitely keep this on the back burner for later use.

[AlanPs1]

Hi @ShAnc 

That's great news that the information was helpful and you have found an alternative fix too.

If you were to post details of the working solution you have located below I would be happy to mark it as a solution in order to assist others in the future.

Thanks, Alan

[ShAnc]

Hi @AlanPs1, thanks and sorry for the delay. I suppose it's just a half solution right now because I'm not able to get around one key manual process.

So, like I said in my OP, I was able to get the data from my WordPress site into a OneDrive and then uploaded to SharePoint. 

In order to get the data from those export files to my SharePoint list (which supports a PowerApp) I maintain a basic import file that has consistent headings with the WP export files, but which is formatted as a table. Every day, people copy data from the export file into the import file. After that, I manage the import process in a PowerApp.

From the PowerApp I import whatever data is in the Excel import file into an importCollection. I have another collection that brings in information from the SP List called spCollections. I compare the contents of the two and if there are any duplicates text is formatted appropriately (green for no duplicates, red for duplicates). I also have an icon option that allows people to remove the duplicates from the importCollection. It's then a matter of using the Patch() function to push items from importCollection to the SP List.

Flow does play in, but only after the upload to the list. Once the SP List is patched, it triggers the following:

In the Filter Query box I type the following, which captures all items created in the list since beginning of day.

Created gt 'startOfDay(utcNow(),'yyyy-MM-ddTHH:mm:ssZ')'

The Compose action uses the following parameter:

length(body('Get_items')?['value'])

And that populates a subject line in the email action that tells people how many new items were added to the list that day.

It's hardly perfect, and we're user testing it now to spot some pitfalls (I'm really not happy with the manual bridging process), but it "works" in the way that a generous application of duct tape can be said to have "repaired" a leaky pipe.