Create a Table if No Table Exists (can't use Office script)

[gyjhfyjdrfhj]

I have a workflow where I am importing the contents of a spreadsheet dropped into a given directory. I need to grab the data from the sheet and send it to a function that will do my transformation, but it seems in order for me to get the data from the spreadsheet into an array and then JSON object, I first need to read the rows from the spreadsheet... and to do that, it needs to be a table.

The problem is that there is no table to begin with, so I am checking if a table exists and if not, then creating a table. The problem is I can not have a Read Rows block if the Table doesn't already exists -- it's not letting me Save the flow. I found a solutions from some time ago using an office script to check if the table exists... but this is not something I can use.

Is there perhaps a better approach to this? The data getting imported from the spreadsheet is all over the place, so creating a table is not easy either because it's not clear how big the table even needs to be. Would it perhaps be easier to convert the Excel file to CSV and then maybe CSV to JSON? Any suggestions would be welcome

[abm · Accepted solution]

Hi @gyjhfyjdrfhj 

Please have a look at my video tutorial about how to create table dynamically.

https://www.youtube.com/watch?v=Q4Q_OWEa-Jw

[gyjhfyjdrfhj · Accepted solution]

Oh! That is super helpful. I know roughly how many columns the data takes up, but the number of rows will always vary, so this looks like it would solve the table size problem. 

Also I think I solved the other problem as well. This is what I am doing:

• Get tables from sheet
• Check if length of the values output is 0
• YES - Table Does Not Exist
  • Create table using the proposed method from @abm : 
    https://www.youtube.com/watch?v=Q4Q_OWEa-Jw
  • List Rows in that table, table name is [name] output from previous block
  • Pass the values to Compose
  • Pass the output and store in my_json variable
• NO  - Table Exists
  • Apply to each ['body/value'] from the Get tables step
  • List Rows in table - Table name is ['name'] from Apply to each step
  • Pass the values to Compose
  • Pass the output and store in my_json variable
• Possibly do some light manipulation/filtering of the data extracted
• The my_json variable will then be sent via HTTP to Azure function

[wizardwalksby]

Did you ever figure out how to check if any table at all exists? I have a flow that creates a table in Excel on a raw dataset so I can load it to a SQL server. The problem is that after the flow runs, it runs again and throws a "table cannot overlap another table" error. I think it runs again because something small changes in the file after the table is created and triggers the "on change". So, I want my flow to look to see if there is any table at all in the Excel and exit if so. There's tons of advice on how to check for a certain table with get tables, but if there is no table, get tables fails and the whole flow fails.

[abm]

Hi @wizardwalksby 

Have a look on this thread.

Solved: Check if excel Table exists - Power Platform Community (microsoft.com)

Thanks

[wizardwalksby]

Thanks, I saw that solution and tried it out. Problem is that if the flow looks for a specific table, and there is no table at all, it breaks the flow.

[abm]

Hi @wizardwalksby 

In which step you are getting failures. Could you post a screenshot please?

Thanks

[wizardwalksby]

I came up with a solution that works well to trap the error and cancel the flow if the table had already been created using "Scope" and Try, Catch, Finally per Jamie McAllister's video Power Automate: Flow Error Handling with Try, Catch, Finally - YouTube .