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!!
=> Use Read Range Workbook to read the excel and store it in an variable say
=> Use Clear Data Table activity and pass the data table variable i. e
=> 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!!
Is there any other way by which I can directly delete the data without deleting the headers.
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.
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()
Row index is the index of the column ??
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()
dataTable = (From row In dataTable.AsEnumerable() ...: This part of the query converts the
dataTableinto an enumerable collection of rows, allowing you to use LINQ methods to manipulate the data.
Where dataTable.Rows.IndexOf(row) = 0: The
Whereclause 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.
Select row: This part of the query selects the rows that meet the condition specified in the
Whereclause. Since we’re only interested in the header row, we select it.
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.
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.
yourDataTable.AsEnumerable().ToList().ForEach(Sub(row) row(“ColumnToRemove”) = DBNull.Value)
Hope this helps!!