Remove excel data without removing headers

Help Studio
, ,
1

Hi,
I have an excel sheet and want to remove the data from it.
I had to remove the data from one column but without header.
Means delete the data and headers remains untouched
Testingsheet.xlsx (9.3 KB)
Can anyone help me with the solution?
Thanks in advance!!

2

Hi @Kunal_Jain

=> Use Read Range Workbook to read the excel and store it in an variable say dt_data.
=> Use Clear Data Table activity and pass the data table variable i. e dt_data.
=> Use Write Range Workbook to write the same data table which only contains headers into excel. Make sure to tick Add Headers option.

Hope it helps!!

3

Hi @Parvathy
Is there any other way by which I can directly delete the data without deleting the headers.
Thanks!!

4

@Kunal_Jain
This would be the easiest way to have only headers and delete the data . You can write Linq query too. The above suggested way would be easy and won’t take that much time to execute.

Regards,

5

Hi @Kunal_Jain

You can try this Linq query too. Step 1 and 3 will be the same.

dataTable = dataTable.AsEnumerable()
    .Where(Function(row, rowIndex) rowIndex = 0 OrElse Not row.ItemArray.All(Function(cell) cell Is DBNull.Value OrElse String.IsNullOrWhiteSpace(cell.ToString())))
    .CopyToDataTable()

Regards,

6

Hi @Parvathy
Row index is the index of the column ??

7

@Kunal_Jain

Sorry that I gave other syntax above. Follow the below syntax and I wish below explanation will give you a clear understanding about the syntax:

dataTable = (From row In dataTable.AsEnumerable()
             Where dataTable.Rows.IndexOf(row) = 0
             Select row).CopyToDataTable()
  1. dataTable = (From row In dataTable.AsEnumerable() ...: This part of the query converts the dataTable into an enumerable collection of rows, allowing you to use LINQ methods to manipulate the data.
  2. Where dataTable.Rows.IndexOf(row) = 0: The Where clause filters the rows in the DataTable. It checks if the index of the current row (dataTable.Rows.IndexOf(row)) is equal to 0. This condition ensures that only the first row (which is typically the header row) is selected.
  3. Select row: This part of the query selects the rows that meet the condition specified in the Where clause. Since we’re only interested in the header row, we select it.
  4. CopyToDataTable(): Finally, the CopyToDataTable() method is used to convert the selected row back into a DataTable. This creates a new DataTable containing only the header row, effectively removing all data rows.

After executing this query, your dataTable variable will contain a DataTable with only the header row, and all data rows will be removed.

8

Hi @Parvathy
I got the point
But where should I give the column name.
Because my datatable contains around 20 columns.
I just have to remove data from one column.
Thanks!!

9

Hi @Kunal_Jain

Try this

yourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row(“ColumnToRemove”) = DBNull.Value)

Hope this helps!!

10

Hi @lrtetala
Getting this error

image
image1366×768 98 KB

Actually I need the output as given in the below excel sheet
Testingsheet.xlsx (10.3 KB)