how to store the index of the header in a variable in power automate desktop

(0) Share

Report

Posted on by kiran_g

150

I want to get the Column Screenshot (46).png index of First Name, Company Name & Phone Number from the excel

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Several ways to do it. You need to first use Read from Excel worksheet to read the contents. You can then use Find or replace in data table to find matches. It will return a data table called %DataTableMatches% which will contain the column index of the first match in %DataTableMatches[0]['Column']%.

So, you can do that 3 times to get it. Somewhat like this:

Here's a snippet you can copy and paste into PAD flow designer to create all those actions for you:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\RPA\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData Excel.CloseExcel.Close Instance: ExcelInstance Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: ExcelData AllMatches: True ValueToFind: $'''First Name''' MatchCase: True MatchEntireCellContents: True DataTableMatches=> DataTableMatches SET ColumnIndex_FirstName TO DataTableMatches[0]['Column'] Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: ExcelData AllMatches: True ValueToFind: $'''Company Name''' MatchCase: True MatchEntireCellContents: True DataTableMatches=> DataTableMatches SET ColumnIndex_CompanyName TO DataTableMatches[0]['Column'] Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: ExcelData AllMatches: True ValueToFind: $'''Phone Number''' MatchCase: True MatchEntireCellContents: True DataTableMatches=> DataTableMatches SET ColumnIndex_PhoneNumber TO DataTableMatches[0]['Column']

Note that you should adjust the file path in the Launch Excel action.

Also, if you expect the header names to also be found in the data somewhere, it might be a good idea to only read the first row instead of all data. But to do that, you would need to also use Get first free column/row from Excel worksheet to find the last column to read. When you read all available data, this is already being run in the background, but if you want a specific range, you need to specify the end.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
kiran_g 150 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, It's working but instead of taking 3 times can we try with 'For each'. I have tried it's not working properly, I'm trying to delete the columns by using the Index Number.

Was this reply helpful? Yes No
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
You have too many loops in there. No need to loop through your data at all. You just need one loop to iterate through the list of headers.

Also, you would then need to read your data again in each iteration, because your columns would change after you delete one of them. So, you cannot use the same %ExcelData% that you retrieved before deleting the first column. You must refresh it.

Also, you probably need to convert the index to a numeric value first, because for some reason Find or replace in data table returns the indexes as strings. So, using Convert text to number should help with that.

Overall, a flow that deletes the columns based on the column names should look like this:

Here's a snippet:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\RPA\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance SET Headers TO ['First Name', 'Address', 'Company Name'] LOOP FOREACH CurrentHeader IN Headers Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData Variables.FindOrReplaceInDataTable.FindItemInDataTableEverywhere DataTable: ExcelData AllMatches: True ValueToFind: CurrentHeader MatchCase: True MatchEntireCellContents: True DataTableMatches=> DataTableMatches Text.ToNumber Text: DataTableMatches[0]['Column'] Number=> ColumnIndex Excel.DeleteColumn Instance: ExcelInstance Column: ColumnIndex + 1 Excel.SaveExcel.Save Instance: ExcelInstance END Excel.CloseExcel.Close Instance: ExcelInstance

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No