check Column A if values are “0” then write on column B “no” and except 0 like any other values , then put “Yes” on column B.
Please let me know if anything else needed.
FYI : I tried to execute with for each loop but got error of assign: exception has been thrown by the target of an invocation.
Thanks in advanced.
use assign activity and pass the below expression
dt=dt.AsEnumerable.Select(Function(a) dt.Clone.LoadDataRow({a(0).ToString,If(a(0).tostring.equals(“0”),“No”,“Yes”)},False)).CopyToDataTable
you can replace column Names in place of 0 and 1
dt.AsEnumerable.Select(Function(a) dt.Clone.LoadDataRow({a(“Column1”).ToString,If(a(“Column2”).tostring.equals(“0”),“No”,“Yes”)},False)).CopyToDataTable
yourDataTable.AsEnumerable().ToList().ForEach(
Sub(row) row(“ColumnB”) = If(row(“ColumnA”).ToString() = “0”, “No”, “Yes”)
)
For my Excel It is taking so much time to read the data. Any other way to read only those 2 columns which I am using in this formula ? I am not sure How Can I read 2 columns together ?
you can select the range which you want to read the data
or
after reading you can use this expression
dt.defaultview.totable(false,“Column1”,“Column2”)
as far we can derive from the use case description
read range - dtData
when needed - Add Data column activity: DataTable=dtData, ColumnName=“Status”, Type: String
Depending on the strategy (see Blog) update Status Columns
Write Range
We do feel that the presented Approach of using DataColumn.Expression is a good fit for this case[quote=“ppr, post:7, topic:670078, full:true”]
(How to Update Data Column Values of a Data Table | Community Blog
as far we can derive from the use case description
read range - dtData
when needed - Add Data column activity: DataTable=dtData, ColumnName=“Status”, Type: String
Depending on the strategy (see Blog) update Status Columns
Write Range
We do feel that the presented Approach of using DataColumn.Expression is a good fit for this case
[/quote]
Getting this error while reading entire Sheet of the Excel. “message”: “Read Range Workbook: A column named ‘Compare’ already belongs to this DataTable.”,
cool
i think there two columns with the same name
try to read the excel without headers
in the properties of read range activity you can see the option
check excel first row, it looks like duplicated values
(1) I have the column name “Compare” on column I and Sheet name Sales and compare and column T also has same name “Compare”.
(2) Column A and column B has almost same values on each of the rows.
just share some sample screenshots. Thanks
sounds like your excel has in first row column I and Column T the same value: “Compare”
When using read range (addHeaders flag activated) the first row will be used for the column Names
But Duplicated ColumnNames are not possible within a DataTable and therefore you will get this error.
Rename one of the Column Values e.g. col I Compare → Compare_1
Thanks for responding. Yes, Add headers are activated while I am reading an excel. Also I’ve changed the same column name in excel. But concern is - taking so much time to read the excel so, is there any way to read only 2 columns which I need to compare ?
Ex : column H need to check - and update column Y.
we would recommend not to merge too much things into one
But topic was introduced by the below
So lets scope and focus this only within this topic. For the logic you can also work with adapted sample data representing your typical data
Got it. I am able to read it but somehow suggested formula is not working as expected. I want to write"No " when there is 0 and want to write “Yes” when there is other values than 0. So current output is “Yes” everywhere and copied only those 2 columns in the Datatable so confused how Can I rewrite that to the main sheet.
we don’t know what was done.
A simple crosscheck had the following result
We keep in mind, that some Functions from the Row Filter syntax cannot be combined with others e.g. an IFF along with a TRIM.
But as mentioned in the BLOG from above we do have more options (e.g. LINQ DatTable Reconstruction, also simple For each would work)
As another Alternate we can do:
dtData.AsEnumerable.Select(function (x) IF(x("Column1").ToString.Trim.Equals("0"), "NO", "YES")).ToArray
Collection to DataTable Activity
Activities - Collection to Data Table
Assign Activity | Optional Adapt Column Name
Write Range | use Range e.g. C1 to replace the C Column in Excel
I tried first expression and not working for me. giving error - Exception has been thrown by the target of an invocation.",
I used assign activity for this.
As you can see there is a syntax difference
related Docu: