Update Excel Rows

Like (1) Share

Report

Posted on 3 Jul 2024 16:57:45 by DN1

Hi all,

I am new to PAD. Please help me update the Excel sheet. For example, the sheet contains employee names and employee numbers. The correct format for employee numbers should be 'ID93434343', where it starts with 'ID9' followed by a 7-digit number. Some rows are correct, but others do not start with 'ID9' or 'ID'. Please guide me on how to resolve this issue.

Categories:

Process Advisor

All responses (3)

Answers (1)

DN1 86 on 08 Jul 2024 at 20:44:55

Like (1)

Report

Re: Update Excel Rows

Hi @NathanAlvares24 ,Thank you for your response. It is really helpful. In my folder i have multiple pdf files. The PAD is extracting the pdf file data and writing the data into excel sheet and reads the excel sheet and manipulating the data's (adding the prefix). could you please guide me how to create a separate excel sheet for each pdf file data's and save the excel sheet based on the pdf file name which is present in the folder.
Deenuji_Loganathan_ 6,204 Super User 2025 Season 2 on 03 Jul 2024 at 17:34:37

Like (2)

Report
Re: Update Excel Rows

@DN1

Please follow the below approach:

Please modify excel path as per your file and folder name.
1. **Launch Excel**:
- Launch and open the Excel file `C:\\Boot\\EmpDetails (1).xlsx` with visibility enabled.
2. **Read Excel Data**:
- Read all cells from the Excel file into a data table, using the first row as headers.
3. **Initialize Counter**:
- Set a counter variable to `0` for tracking row indices.
4. **Loop Through Data Table**:
- Loop through each row in the data table.
5. **Modify Employee Number (7 characters)**:
- If the employee number is 7 characters long and does not start with "ID", prepend "ID9".
6. **Modify Employee Number (8 characters)**:
- If the employee number is 8 characters long and does not start with "ID", prepend "ID".
7. **Increment Counter**:
- Increment the counter variable at the end of each loop iteration.
8. **Launch New Excel Instance**:
- Launch a new Excel instance with visibility enabled.
9. **Write Modified Data**:
- Write the modified data table to the new Excel instance, including column headers.
10. **Save Updated Excel**:
- Save the updated Excel file as `C:\\Boot\\UpdatedExcel.xlsx`.

Code:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Boot\\EmpDetails (1).xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> EmployeeExcel SET Counter TO 0 LOOP FOREACH CurrentEmployee IN EmployeeExcel IF (NotStartsWith(CurrentEmployee['Employee Number'], $'''ID''', True) AND CurrentEmployee['Employee Number'].Length = 7) = True THEN Variables.ModifyDataTableItem DataTable: EmployeeExcel ColumnNameOrIndex: 1 RowIndex: Counter Value: $'''ID9%CurrentEmployee['Employee Number']%''' END IF (NotStartsWith(CurrentEmployee['Employee Number'], $'''ID''', True) AND CurrentEmployee['Employee Number'].Length = 😎 = True THEN Variables.ModifyDataTableItem DataTable: EmployeeExcel ColumnNameOrIndex: 1 RowIndex: Counter Value: $'''ID%CurrentEmployee['Employee Number']%''' END SET Counter TO Counter + 1 END Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance1 Excel.WriteToExcel.WriteCell Instance: ExcelInstance1 Value: EmployeeExcel.ColumnHeadersRow Column: $'''A''' Row: 0 Excel.WriteToExcel.WriteCell Instance: ExcelInstance1 Value: EmployeeExcel Column: $'''A''' Row: 1 Excel.SaveExcel.SaveAs Instance: ExcelInstance1 DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Boot\\UpdatedExcel.xlsx'''

How to copy/paste above code into your Power automate desktop flow?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
Verified answer

NathanAlvares24 1,673 Moderator on 03 Jul 2024 at 17:34:17

Like (2)

Report
Re: Update Excel Rows

Hi @DN1 !

Please try out this flow.

Output:

Before:

After:

Code (just copy paste into your flow):

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Username\\Desktop\\EmpDetails.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData SET nCounter TO 2 LOOP FOREACH CurrentItem IN ExcelData IF (NotStartsWith(CurrentItem[1], 'ID9', True) AND CurrentItem[1].Length = 7) = $'''True''' THEN Text.Pad Text: CurrentItem[1] PadPosition: Text.PadPosition.Left PaddingText: $'''ID9''' TotalLength: 10 PaddedText=> PaddedText Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: PaddedText Column: $'''B''' Row: nCounter Variables.IncreaseVariable Value: nCounter IncrementValue: 1 ELSE IF CurrentItem[1].Length = 8 THEN Text.Pad Text: CurrentItem[1] PadPosition: Text.PadPosition.Left PaddingText: $'''ID''' TotalLength: 10 PaddedText=> PaddedText Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: PaddedText Column: $'''B''' Row: nCounter Variables.IncreaseVariable Value: nCounter IncrementValue: 1 ELSE Variables.IncreaseVariable Value: nCounter IncrementValue: 1 END END Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

I hope this helps.