FIlling excel using LINQ

Hi Team,
Input:

Output:

I want to fill the blank rows of 7th column and 9th column with the value present in the preceeding row,i.e row before it. I want to do it using Linq.

Please help.

Here’s a sample workflow to accomplish this:

  1. Read Range: Use the “Read Range” activity to read the data from your Excel file into a data table.
  2. Assign activity: Create a new data table to store the modified data.
  3. For Each Row: Use a “For Each Row” activity to iterate through the rows of the original data table.
  4. Use LINQ to update the 7th and 9th columns of the current row based on the preceding row’s values.
  5. Add Data Row: Add the updated row to the new data table.
  6. Write Range: Finally, use the “Write Range” activity to write the updated data table back to the Excel file.

Below is a sample code snippet for the LINQ part inside the “For Each Row” activity:
// Initialize a variable to keep track of the preceding row.
DataRow previousRow = null;

// Process each row in the data table.
foreach (DataRow row in dataTable.Rows)
{
// Check if the 7th column is blank and update it with the preceding row’s value if applicable.
if (string.IsNullOrEmpty(row[6].ToString()) && previousRow != null)
{
row[6] = previousRow[6];
}

// Check if the 9th column is blank and update it with the preceding row's value if applicable.
if (string.IsNullOrEmpty(row[8].ToString()) && previousRow != null)
{
    row[8] = previousRow[8];
}

// Store the current row as the preceding row for the next iteration.
previousRow = row;

}

Remember to replace dataTable with the name of your original data table variable and adjust the column indices (e.g., row[6] and row[8] ) to match the 7th and 9th columns in your data table.

@supermanPunch and @Yoichi . Please help.

Cross-Reference:

This is a different question @ppr.

Dim previousColumn7 As String = ""
Dim previousColumn9 As String = ""

For Each row As DataRow In dataTable.Rows
     Dim column7Value As String = If(String.IsNullOrEmpty(row("Column7").ToString()), previousColumn7, row("Column7").ToString())
    Dim column9Value As String = If(String.IsNullOrEmpty(row("Column9").ToString()), previousColumn9, row("Column9").ToString())
    row("Column7") = column7Value
    row("Column9") = column9Value
    previousColumn7 = column7Value
    previousColumn9 = column9Value
Next

Try using this query in invoke code

2 Likes

it is filling up, right?

so we would do it with the same logic, just extended to two columns, but with the same base logic.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.