Hello Friends,
I want to extract HP laptop whose price between 30k - 50k along with ratings.
rating should be 4.0 not 4.0 out of 5 stars.
I tried datascrapping option and
It comes like that
Output required like below
Want data in excel only.
Appreciate your help!
After extraction, use string manipulations or regex to get the required output
Hope it helps!!
You can use the LINQ Expressions, Check the below steps,
→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Then use the assign activity and create a variable called Output_dt which has to be datatable datype and write the below LINQ Expression,
- Assign -> dt_Output = (From row In Input_dt
Let Ratings = CDbl(System.Text.RegularExpressions.Regex.Match(row("Ratings").ToString(), "[\d\.]+").Value.ToString())
Where CDbl(row("Price")) >= 30000 AndAlso CDbl(row("Price")) <= 50000 AndAlso CDbl(Ratings).Equals(4.0)
Select Input_dt.Clone.Rows.Add({row("Item Names").ToString(), CInt(System.Text.RegularExpressions.Regex.Match(row("Ratings").ToString(), "\d+").Value.ToString()), CDbl(row("Price").ToString())})
).CopyToDataTable()
→ Then use the Write range workbook activith to write the Output_dt to the excel.
The Output_dt contains the output data.
Check the below workflow for better understanding,
Sequence3.xaml (12.7 KB)
Input Data -
Output Data-
Hope it helps!!
Try this
Code:
resultDT = inputDT.Clone
For Each row As DataRow In inputDT.Rows
Dim itemName As String = row("Item Name").ToString()
Dim ratings As String = row("Ratings").ToString().Split(" "c)(0)
Dim price As Double = Convert.ToDouble(row("Price"))
If itemName.Contains("HP Laptop") AndAlso price >= 30000 AndAlso price <= 50000 Then
Dim newRow As DataRow = resultDT.NewRow()
newRow("Item Name") = "HP Laptop"
newRow("Ratings") = Convert.ToDouble(ratings)
newRow("Price") = price
resultDT.Rows.Add(newRow)
End If
Next
resultDT = resultDT.AsEnumerable().GroupBy(Function(r) r("Item Name")).Select(Function(g) g.First()).CopyToDataTable()
Input:
Output:
Regards,
Can you share the code for regex?
I tried this method but it throw error “Assign: Conversion from string “” to type ‘Double’ is not valid.”
Change the LINQ Expression in the Assign activity as below,
- Assign -> Output_dt = (From row In Input_dt
Let Ratings = CDbl(System.Text.RegularExpressions.Regex.Match(row("Ratings").ToString(), "[\d\.]+").Value.ToString())
Where CDbl(row("Price")) >= 30000 AndAlso CDbl(row("Price")) <= 50000 AndAlso CDbl(Ratings).Equals(4.0)
Select Input_dt.Clone.Rows.Add({row("Item Names").ToString(), CInt(System.Text.RegularExpressions.Regex.Match(row("Ratings").ToString(), "\d+").Value.ToString()), CDbl(row("Price").ToString())})
).CopyToDataTable()
Hope you understand!!
HP data.xlsx (9.1 KB)
Here is data, can you someone code this one? I tried but not run and throw the error.
Rating should be 4.0,4.1 format required
Can you share the expected output also… @Jeeru_venkat_Rao
Assign: Conversion from string “” to type ‘Double’ is not valid.
I used your code but throw this error
Use the below LINQ Expression,
- Assign -> Output_dt = (From row In Input_dt
Let Ratings = (System.Text.RegularExpressions.Regex.Match(row("Ratings").ToString(), "[\d\.]+").Value.ToString())
Where CDbl(row("Price")) >= 30000 AndAlso CDbl(row("Price")) <= 50000 AndAlso CDbl(Ratings) >= 4.0
Select Input_dt.Clone.Rows.Add(row("Item Names").ToString(), (Ratings), row("Price").ToString())
).CopyToDataTable()
Can you try the below workflow,
Sequence3.xaml (12.7 KB)
Output data is in Sheet2,
HP data.xlsx (9.7 KB)
Hope you understand!!
Working fine bro. Thanks for your quick response
It’s my pleasure… @Jeeru_venkat_Rao
Hope you find the solution, Make my post Mark as solution to close the loop.
Happy Automation!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.