Delete column values

[neha_shisodiya]

I want to delete some random columns from a datatable by keeping the header of the column so thatt I can write those in Excel file. How to proceed with that

[postwick (Paul Ostwick)]

If you keep the header you aren’t deleting the column. Do you mean you want to set all the values to blank?

For Each Row in Datatable

• Assign CurrentRow(“columnName”) = “”

[Nitya1 (Nitya Tomar)]

Hi @neha_shisodiya

To delete specific columns from a DataTable in UiPath while keeping the column headers so that you can later write the modified DataTable to an Excel file, you can use the following approach:

1. Retrieve the List of Columns to Delete:

   • Create a list (e.g., columnsToDelete) that contains the names of the columns you want to delete. Populate this list with the names of the columns you want to remove.

2. Filter the Columns to Keep:

   • Use LINQ to create a new DataTable that contains only the columns you want to keep. You can use the CopyToDataTable method to achieve this.

3. Write the Modified DataTable to Excel:

   • Write the modified DataTable (with the desired columns removed) to an Excel file using the “Write Range” activity.

Here’s an example workflow in UiPath:

1. Assign activity:
   - Create a List(Of String) variable named columnsToDelete and populate it with the names of columns you want to delete.

2. Assign activity:
   - Create a DataTable variable (e.g., dtModified) and set it to the result of a LINQ query that filters the columns to keep.
   - Example LINQ query:
     dtOriginal.AsEnumerable().Select(Function(row) row.Field(Of String)("Column1")).CopyToDataTable()

3. Write Range activity:
   - Use the "Write Range" activity to write the dtModified DataTable to an Excel file.

4. End of Workflow

Replace “Column1” with the actual column names in your DataTable, and repeat the LINQ query for each column you want to keep.

This workflow will create a new DataTable (dtModified) that only contains the columns you want to keep, and then it writes this modified DataTable to an Excel file while preserving the column headers.

Thanks!!

[postwick (Paul Ostwick)]

They don’t want to delete the columns.

[Nitya (Nitya Tomar)]

@postwick

This is what she requested, she is looking to delete the random columns from a datatable.

“I want to delete some random columns from a datatable”

[postwick (Paul Ostwick)]

@neha_shisodiya

by keeping the header of the column so thatt I can write those in Excel file.

Keep reading: "… by keeping the header of the column so thatt I can write those in Excel file. "

[Nitya (Nitya Tomar)]

I read the full question already and provide the solution accordingly.

Why don’t you let her decide which solution fit for her query. If the user will have any query they can directly ask me and also I didn’t asked for the feedback on my solution.

[neha_shisodiya]

I want to delete the values not the rows and again I want to write that datatable to Excel file.

[neha_shisodiya]

Sorry but not following your solution as well

Basically my use case is I want to read an Excel file and delete values from 3 different columns keeping the column name intact.

[postwick (Paul Ostwick)]

Then you use For Each Row in Datatable to loop through the datatable, and use Assign to set each column to blank.

[neha_shisodiya]

The columns are not consecutive columns so at a time I can read only one column. Also it is a huge file. So can’t read the full Excel.

[postwick (Paul Ostwick)]

It doesn’t matter if they’re consecutive columns.

You use Workbook Read Range to read the whole file, then For Each Row in Datatable to loop through it and set the columns you want (referenced by column name) to set them to blank values. Then you can write back to Excel.

[neha_shisodiya]

I can’t read the whole file it is taking soo much time . That’s why I’m reading one column at a time in the Excel scope.

[postwick (Paul Ostwick)]

So Read Range that column, For Each Row in Data Table and Assign to blank out the values, then Write Range back to the Excel file to overwrite the column with the blank values.

[Kartheek_Battu]

Hello @neha_shisodiya

Assuming you have a DataTable named ‘inputDataTable’ and a List of column names to delete named ‘columnsToDelete’

Remove the specified columns
columnsToDelete.ForEach(Function(column) inputDataTable.Columns.Remove(column))

Write the modified ‘inputDataTable’ to an Excel file

WriteRange(activity: WorkbookPath = “C:\Path\To\Your\Excel\File.xlsx”, SheetName = “Sheet1”, DataTable = inputDataTable, Range = “A1”)

Thanks & Cheers!!!