Working with Excel data not in table format

Question

Scenario, Email received with Excel file (.xls) attached, file contains data (Sheet1) that is not formatted as a table.

Q: Is there some way using Power Automate to run a script against it to apply transforms?

Ideal outcome would be: Excel file received as email attachment, delete header rows in Sheet1 (rows 1 and 2), save as CSV file and email new file to user.

score 2 · Answer 1 · answered Nov 11 '20 at 18:14

Thank you for posting the question. There are 3 parts to it.

Extracting the email attachment (xls) and saving it as xlsx. Converting xls > xlsx may be possible - but I'm not sure about the exact steps. Are you sure you are getting xls and not xlsx?
Removing header row is possible with Office Scripts. You can try recording your action in Excel for web and use that script. Be sure to change the sheet name as shown below.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet('Sheet1');
    // This can also be first sheet if you know it'll always be first sheet that you want to operate on.
    // let sheet = workbook.getFirstWorksheet();
    sheet.getRange("1:2").delete(ExcelScript.DeleteShiftDirection.up);
}

To export as CSV, there seems to be online resources to do refer to: https://www.youtube.com/watch?v=Ik-TLaMByhw&ab_channel=AndersJensen

Good luck and let us know how it worked out.

Working with Excel data not in table format

1 Answers1