Trying to Have Flow Execute a SQL and write the data to a new excel file

[srduval]

I'm trying to build a flow to create a new excel file, add a table, then add rows from SQL server to the excel file/new table. I couldn't find a way to create a blank excel file from scratch, but I was able to create a blank workbook to serve as a template. I'm able to use the get file contents and create file with the dynamic field from the previous step, but when I try to interact with the newly created excel file, flow keeps saying file not found any ideas on what i'm missing? If i navigate to the folder, i can see the excel file and open it correctly in excel, so everything up to that point works perfectly

I'm missing?

[yashag2255]

Hi @srduval 

Did you create the excel worksheet template after adding the excel action in your flow? Can you confirm that you have selected the excel file from the selector and not typed in the value as a custom value?  You can click the file picker to see the excel file. 

Also, you might want to try deleting the current connection to excel and create a new one. Alternatively, you might want to log out of the current session and close the window, start a fresh session and re run the flow to test if you are getting the similar error. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[srduval]

@yashag2255Thanks for the response. I added the excel actions after I created the template, as I was originally trying to do the create worksheet option in the new workbook, but when that failed I deleted it and tried using the get worksheets instead to just use an existing sheet to add the table to only to have that fail as well now.  I did use the file picker, not the custom value. and my computer was rebooted last night, and still failed today, so the session is "fresh" I hadn't thought of that yesterday, so that's good to keep in mind for future reference.

[srduval]

@yashag2255is it possibly trying to use a GUID when I browse to the file? I'm browsing to a file, but that file is being deleted and recreated each time the flow runs. I tried the get rows delete rows from excel but it took along time. So I was tring to find a better method.

[yashag2255]

Hi @srduval 

I get it now, the error is popping because the worksheet ID being chosen is getting deleted and it is not getting pointed to the new one. 

Can you share a screenshot of the expanded action of the Delete File, Get file Content Using Path and Create File actions? Because if you are using the same file in all the actions, I belive it is getting the error at the Get FIle Content Using Path action itself, as the file is not found. 

[v-lin-msft]

Hi @srduval,

Because you deleted the previous Excel file, the Worksheet you obtained through the file path is no longer the previous file.

According to your flow configuration, I probably know that what you want to do is to delete all the records in the original file and then import new records from SQL, right? If this is the case. You don't need to delete the source file, just empty the table in the file. Here are the steps to clean the table:

I hope it can help you. If you still have questions, please contact us.

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[srduval]

@v-lin-msft 

yes that is my ultimate goal. Thanks. Is there anyway to dynamically pass the file name? For example if I wanted to create this file everyday, but use a new file name (without deleting the old file). I have several scenarios where I would like to use flow to get data from SQL and output to excel on a daily, weekly or monthly basis. and I don't want to have to create 365+ files in order to fill them with data.

[srduval]

@yashag2255The get file content step refers to a different file than the one i am deleteing. It's a blank excel sheet saved in a different document library all together then the file I'm deleting, so my "template" remains unchanged. 

[v-lin-msft]

Hi @srduval,

It is hard to be dynamically set the filename currently with the excel file. Since the newly created file could not be used by this turn in the flow. If you want to store the data from SQL, you can create a list and export the excel file.

This is another way to create an excel file.

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[srduval]

Thanks @v-lin-msft unfortunitly that doesn't help, as the idea is to automate the extraction of data, not add a manual step...

sounds like i'll have to stick to SSRS

[juresti]

Hello,

I myself have similar processes with excel files.

One thing I found is that you do use the folder selector to get the file and not type it manually.

Note: I use the same file name so it never changes.

However, you should type in the worksheet and table name manually, otherwise the connector uses an ID which no longer exists since it is a new one.

If you type the worksheet and table names manually on the "Get actions" it should work.

I do not get worksheets on my flow but I do get tables that way and they work.

Again, I do the same thing. Delete the file and recreate it from a blank copy and then create the table.

[srduval]

@juresti 

Thanks i'll have to try the table name manually and see if that works, I don't know that I tried passing a typed in file name just passing it dynamically from a get file or similar.

Another solution I discovered is if you create the file and table and save it somewhere (with no data other than table structure), you can then run a quick one time flow that goes and gets the file content. Then looking at the results of that flow, you can copy and paste the output of the get file contents into your orignal flow as a variable (I use compose). Then pass that as the source for your create/update file. This way you no longer need to store out a blank file to copy from each time. I have to do this as an update (not sure if create does anything different than update if the file isn't first deleted) . If I deleted the file first it didn't work.

[juresti]

Yes, I just replace the same file. 

I have tried a typed file name and has not worked for me. However typed tables do work. Worksheets I have not tried.

This is a great idea reusing an existing output from a flow run.

Then you have an empty file content replaced each time from the output and not from a physical file.

Thanks.