Remove rows which are not having Dates

Help Activities
, ,
1

Automating a process involving an Excel sheet that contains dynamic rows and up to 40+ columns. Each column may or may not contain a date. My objective is to transfer the dates from these columns to a application .

My planned approach includes the following steps:

  1. Process each row individually.
  2. Eliminate columns that do not contain any dates.
  3. Utilize a “Do While” loop to sequentially input the dates into the application until all dates from the row are entered.

The challenge I’m facing is efficiently removing the columns that are empty. How can I achieve this column removal effectively?

Thank you for your assistance!

2

Hi @aaravsharma_dsc ,

Isit removing the columns that are empty or removes rows which are not having Dates ??

Regards
Sandy

3

it is removing the columns that are empty

4

@aaravsharma_dsc

LINQ query to filter out the rows that do not contain valid dates in a specific column

filteredDT = dt.AsEnumerable().Where(Function(row) DateTime.TryParse(row("DateColumn").ToString(), New DateTime())).CopyToDataTable()
1 Like
5

Hi @aaravsharma_dsc ,

Detailed Steps:

  1. Read Range:
  • Use the “Read Range” activity to read the Excel file and store it in a DataTable variable dt.
  1. Identify and Remove Empty Columns:
  • Iterate through each column in dt.Columns.
  • Use the “Filter Data Table” activity to filter rows where the current column is empty. Store the result in filteredDt.
  • Use an “If” condition to check if filteredDt has zero rows.
  • If filteredDt has zero rows, use the “Remove Data Column” activity to delete the current column from dt.
  1. Process Each Row:
  • Use the “For Each Row” activity to loop through each row in the modified DataTable dt.
  • Initialize a list nonEmptyColumns to keep track of non-empty columns in the current row.
  • Use a nested loop to identify non-empty columns and add them to nonEmptyColumns.
  • Use a “Do While” loop to iterate through nonEmptyColumns and input each date into the website.

Regards
Sandy

6

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