Logic Work flow to read from excel file

Niren Adhikary (NAD) 146 Reputation points
2022-08-04T22:26:38.69+00:00

Hi

I am trying to setup a logic app work flow to read records from an excel file and update tables in SQL database.

To achieve this I believe it requires Get Table or List rows present in a table connector can be used. But it needs table name I am not sure if the file will contain a table or not. So could you please suggest how to read the records from excel and then take records in a column in excel and update sql table using Update SQL trigger or something else , its not just a record update but to match existing records and update the values in a column in the table.

![228267-image.png]1

Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
3,560 questions
Community Center | Not monitored
0 comments No comments
{count} votes

Accepted answer
  1. Bruno Lucas 4,436 Reputation points MVP
    2022-08-07T05:39:03.43+00:00

    Hi @Niren Adhikary (NAD)

    Based on documentation the excel must have a table. If you check both Excel Online Onedrive: https://learn.microsoft.com/en-us/connectors/excelonline/ and Excel Online Business: https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/

    the actions "get row" and "list row present in table" have the parameter "table" as required

    Can you control the excel format? If the excel has data in a worksheet you just need to add a table using the data range you need:
    228861-image.png

    just make sure you add a proper name to it:

    228846-image.png

    If you can't control the format of the excel it may become more challenging, but you are on the right track just need to use the name from the previous action:

    228700-image.png

    if the file name and table name don't change you don't need the previous step

    228847-image.png

    If your users can't guarantee an excel fixed format with the same table name or same worksheet name, you can allways use a condition to email or log an error

    228827-image.png

    For Sql server you just need to find a key on your Excel that matches your Sql table pk

    228799-image.png

    Other suggestions would be to use CSV: https://blog.devgenius.io/parse-csv-files-as-json-objects-in-azure-logic-apps-and-power-automate-4d99d23306f7 or a third party connector like Cloudmersive: https://cloudmersive.com/examples/excel-spreadsheet-get-rows-and-cells-in-power-automate to read direct from the worksheet:

    228881-image.png

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.