Hi all,
This is my input.
My output file:
Separating the data with respect to comma. And save the different data in different rows. Removing the RS. And saving the absolute value only.
Please help.
hi @yashchoursia25 ,
Assuming your input column is row("Amount").ToString:
System.Text.RegularExpressions.Regex.Matches(row("Amount").ToString, "\d+\.?\d*").Cast(Of System.Text.RegularExpressions.Match).Select(Function(m) m.Value).ToList()
Assuming all the values are comma separated in a a single column with name “values”:
DataDT.AsEnumerable.Select(function(x) x.Field(Of Object)("values").ToString.Replace("Rs ","").Split(","C))
this will give you an enumeration of arrays(rows values)
I want output in excel format only
then give us more details on your requirement.
how would we know where you are writing the data?
your post says “LINQ for separating particular row with respect to comma”
After you use the LINQ provided, it should provide you with a Array of the rows values.
From there you would simply need to convert this to a Datatable, and then use Excel activities to write that Datatable it into your Excel file.
Would you like me to show you how this is done?
Yes please tell me how to convert this list to datatable.
I have done it slightly differently using an “Invoke Code” activity - it’s quicker and easier than a LINQ:
Here is the code inside the “Invoke Code”:
dt_ResultTable = New DataTable()
dt_ResultTable.Columns.Add("Test", GetType(String))
For Each row As DataRow In dt_ExcelData.Rows
Dim values = row("Test").ToString.Split(","c)
For Each rawVal As String In values
Dim cleanVal As String = rawVal.Replace("Rs", "").Trim()
If Not String.IsNullOrWhiteSpace(cleanVal) Then
dt_ResultTable.Rows.Add(cleanVal)
End If
Next
Next
And here are the input/output variables:
This is what the output looks like in a Datatable ready for Excel:
