Picking up few columns from excel

Help Activities
, ,
1

Hello All,
I have an excel file with 470 columns in that I need to pick only 75 columns and write it in other excel file. Is it possible to write it through Linq Queries? Please help me with this.

2

@ISBS_ROBOT

Assign dtSelectedColumns
(From row In dtSource.AsEnumerable()
Select dtSource.Columns.Cast(Of DataColumn)().Where(Function(col) col.Ordinal < 75).Select(Function(col) row(col)).ToArray()).CopyToDataTable()

3

Hi @ISBS_ROBOT

selectedColumns= {“Column1”, “Column2”, “Column3”, …} ’ List the column names you want to select
DataTable = originalDataTable.DefaultView.ToTable(False, selectedColumns)

4

Hi @ISBS_ROBOT

You can use read range workbook activity to read the excel file and store in a datatable.
In the range field, give the range of the excel untill 75 columns.
After that use the write range workbook activity to write the datatable to another excel.

Check the below image for better understanding.
image

Hope it helps!!

5

Hello,
Columns need to pick randomly. they are not in order. I need to give each column name through Linq Query.

6

Okay @ISBS_ROBOT

=> Assuming dtSource is your source DataTable
=> columnsToRetrieve is a list of column names you want to extract
=> dtDestination is the destination DataTable to write the extracted data

  - Assign ->  columnsToRetrieve As List(Of String) = New List(Of String) From {"Column1", "Column3", "Column5"}

=> Use LINQ to filter the columns and create a new DataTable

  - Assign -> extractedData As DataTable = dtSource.AsEnumerable().
    Select(Function(row) dtSource.Clone().ItemArray.
        Where(Function(col, index) columnsToRetrieve.Contains(dtSource.Columns(index).ColumnName)).
        ToArray()).CopyToDataTable()

=> Assign the extracted data to the destination DataTable

  - Assign -> dtDestination = extractedData.Copy()

Hope it helps!!

7

Hello,
Do I need to give the headers name in the assign activity or just column1, column2…because randomly I need to pick like column1, column3, column7, column5… and few columns are I need to leave few columns in between.

8

No @ISBS_ROBOT

You have to update with Column headers.

Hope you understand!!