How to remove top few lines of an excel file that does not have a table

[galos]

Every step I attempt with power automate shows me another hurdle I can't get past. My other questions can't get resolved unless I now have this step completed.

I have an excel file which is an export of data from another application (XLSX format). The excel document does not have a table, because the flow that creates a table breaks due to the fact that this excel file has document information at the top of the excel file, that needs to be deleted before a table can be created.

Is there any way to remove the top 14 lines of an excel file (14 lines of document information) using power automate? But keep in mind there's no table. 

If a table is required for this to happen, then how could I create a table in an excel document that ignores the first 14 lines of information, so I can delete it after the table is created?

Thanks in advance

[abm]

Hi @galos 

Could you attach a sample file which I can try from my end?

Thanks

[galos]

I had to anonymize and reduce the file size, so only a few lines here, but the data would be much larger. Let me know if this helps. I tried to keep the formatting identical

[galos]

Had to delete the last one and update it with one item. Here is an updated copy with anonymized data

appreciate your help @abm !

[galos]

@abm I'm wondering if using OFFSET/SUBTOTAL would work here? I wonder if there's a way to start with the header row and end with an empty cell (as each time the report generates, it can be a different length / # of rows). If you noticed, the output that is generated to this excel file (from our application) breaks the standard A1:A$ cell names. It's strange with R and C?

[abm]

Hi @galos 

Thanks for sending the anonymize file. Let me try and see. Will give you an update soon.

Thanks

[abm]

Hi @galos 

I got a solution for you. I have tested this and working as expected. I will make a video for this soon.

Thanks

[galos]

@abm excited to see what you did!

[creativeopinion]

You can try this Excel automation script. Add it to Excel.

function main(workbook: ExcelScript.Workbook, numberOfRowsToDelete: number) {
	let selectedSheet = workbook.getActiveWorksheet();
	let startRow = 1;
	let endRow = startRow + numberOfRowsToDelete - 1;
	let rangeToDelete = selectedSheet.getRange(`${startRow}:${endRow}`);
	rangeToDelete.delete(ExcelScript.DeleteShiftDirection.up);
}

In your flow, add a Run Script action and select the script. You can specify the number of rows to delete by entering it in the last field.

You can then run another script that will create a table(s) for you. I recently posted a video that goes through this process: https://youtu.be/GNLhPqP1cjM

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[galos]

Thank you @creativeopinion  ! I will try this. One question though - is the excel automation script something that needs to be in the excel file prior to running it with power automate? Because I need to automate this process and adding that in manually to an excel file will break the flow. Can power automate add that function into the excel file?

[galos]

Oh I see, this script is to be put directly into it's own excel file and run, correct? If that's the case, how do I get it to point that action of deleting 14 rows to the other file I have? Sorry I'm not an advanced power automate user yet, still at basic level, but I do have a strong background on other power products

[creativeopinion]

You have to create a New Script in Excel first so that you can use it in Power Automate. It can be in a new file, that file you are trying to remove rows from. The script isn't tied to a file—but it's tied to Excel.

If you watch the video I linked, you will see how I added a script to create tables. You can follow that. The process is exactly the same.

After you run the script to remove those rows—I'd recommend following that video to run a script to create a table (if that's what you require).

[creativeopinion]

As mentioned previously. You aren't adding the script to any file. You are adding it to Excel so you can run it on any file. 

Open a new workbook and add the script. You can access the script from Power Automate and run it on any file. Does that help clarify things?

[galos]

It does help. I'll have to go through your video and understand how to add this into my flow. Will post back here as soon as I figure this out. Thank you!

[creativeopinion]

No problem. Just follow the video up until the point where I add the script for creating tables. Instead, add the script I included below a few posts ago.

Run a test to see if it works. After the Run Script action, add whatever actions you need in your automation. Or as I mentioned before you could add another Run Script action to automatically create tables in your workbook (all the details of that are in the video tutorial).

[abm · Accepted solution]

Hi @galos 

Here is the video. Sorry was busy all day.

https://youtu.be/AaLgfCMxXps

Thanks

[abm]

Hi @galos 

Watch my video. I have explained it. Once you created the script you can reuse the same for other Excel files too. 

Thanks

[galos]

This worked beautifully, thank you @abm 

As a quick follow up question - is there a way to remove the last 6 lines from the same xlsx file? This file varies by day, so it's not possible to remove the same line number, it would have to be the bottom 6 lines that contain data in the xlsx file.

Many Thanks again!

Thank you!

[abm]

Hi @galos 

You could find the total number of rows first and subtract 6 will give you the lines you needed. The use the apply each with a counter and check each data until it reaches the last row. Then subsequent rows you can delete using the Delete row action step.

Thanks