Help, I want to transfer data of (column sales) from time card.xlsx to book1.xlsx must input the sales by date and for the exact ID number. I am using STUDIOX
sample excel file is attached
Time Card Entry 2.xls (28.5 KB)
Book1.xlsx (8.4 KB)
Hi @flashdrive07 You have different ways to do that
*you can loop through datatable corresponding to book.xlsx and using Filter Data Table activity to find the corresponding value from timecard datatable
-
you can use vlookup activity in UiPath
-
I’m not sure if Invoke code activity is available in StudioX if yes, you can use vb.net code
this following should work for that. Just read excel values from both files to Dt_Timecard and Dt_Book and create a variable Dt_Result give the value New DataTable as default. Then pass all three as argumentsDt_Result.Columns.Add("Emp Id", GetType(String)) Dt_Result.Columns.Add("Employee Name", GetType(String)) ' Iterate through each employee in Dt_Book and populate Dt_Result For Each rowBook As DataRow In Dt_Book.Rows Dim empId As String = rowBook("Emp Id").ToString() Console.WriteLine("Employee ID: " + empId) ' Create a new row for the result table Dim newRow As DataRow = Dt_Result.NewRow() newRow("Emp Id") = empId newRow("Employee Name") = rowBook("Employee Name") Console.WriteLine("Assigned employee ID and name to Dt_Result.") ' Populate sales data for each date column For columnIndex As Integer = 3 To 8 ' Columns C to I Console.WriteLine("Column Index: " + columnIndex.ToString()) Dim dateColumn As System.Data.DataColumn = Dt_Book.Columns(columnIndex) Dim dateValue As String = Convert.ToDateTime(dateColumn.ColumnName).ToString("dd-MM-yyyy") Console.WriteLine("Date Value: " + dateValue) If Not Dt_Result.Columns.Contains(dateValue) Then Dt_Result.Columns.Add(dateValue, GetType(String)) End If Dim sales As String = (From rowTimecard In Dt_Timecard.AsEnumerable() Where rowTimecard("Emp Id").ToString() = empId AndAlso Convert.ToDateTime(rowTimecard("Date")).ToString("dd-MM-yyyy") = dateValue AndAlso Not String.IsNullOrEmpty(rowTimecard("sales").ToString()) Select rowTimecard("sales")).FirstOrDefault().ToString newRow(dateValue) = If(sales IsNot Nothing, sales, "") ' Handle null values Console.WriteLine("Sales Value: " + sales) Next ' Add the populated row to the result table Dt_Result.Rows.Add(newRow) Next
Try this:
-
Use Read Range Workbook Read time card.xlsx and give th sheet name(Sheet: desired_sheet) → Output: dtTimeCard
-
Filter Data Table
- Input DataTable: dtTimeCard
- Filter Expression: “[Date] = ‘desired_date’ AND [ID] = desired_ID”
- Output: dtFilteredData
-
Use Write Range Workbook to write it to book1.xlsx
- Write Range (Sheet: desired_sheet, Range: desired_range) using dtFilteredData.
Note: Replace desired_sheet, desired_date, desired_ID, and desired_range with your actual values. Also, ensure that the column names and data formats match the ones in your Excel files.
Hope it helps!!
Hi @flashdrive07 ,
You can read file input to get data table
filter data input to get only by date or Id
compare with book1 by ID and date
fill to book1 by cell
regards,
LNV
Hey @flashdrive07 ,
Here is the workflow for your requirement.
Main (1) (1).xaml (8.8 KB)
LINQ Expressions:
1.
Gets an array of ‘sale’ column for a specific employee
Adds the ‘sale’ values to the date colums
Here is the output for the above LINQ
The Excel file will contain the following:
@flashdrive07
Sequence - Time and Date Forum.zip (3.4 KB)
Check out this ! It works fine
OUTPUT :
Regards,
Hey @flashdrive07 ,
You can also use the LINQ expression below. It makes use of Join hence will ensure that the matching employee names will be updated.
(From row1 In dt_out
Group Join row2 In dt_in On row1("Employee Name").ToString Equals row2("Employee Name").ToString Into grp1=Group
From joinedGroup In grp1.DefaultIfEmpty
Let saleArr=(From row In joinedGroup.Table
Group By emp=row("Employee Name") Into grp2=Group
Let sales = String.Join(",",grp2.Select(Function(rowGroup) rowGroup("sales").ToString))
Select sales)
Select ra=row1.ItemArray.Take(3).Concat(saleArr(dt_out.Rows.IndexOf(row1)).Split(","c)).ToArray
Select dt_out.clone.Rows.Add(ra)).AsEnumerable.GroupBy(Function(x) x("Employee Name")).Select(Function(x) x.First).CopyToDataTable
Assign the above expression to out_dt
Output is similar:
1 Like
Hi, can u possible make an StudioX version of this sample ?
Hi, can u make a studiox version of this sample…
thank you so much!
Hi @Yoichi san, can u please help me again?
much appreciated if you can provide sample for studiox again. thank you!
can u please provide sample in studiox version ?
Exceldatavalidation.zip (69.0 KB)
This one is working for me. You might have to enable developer filter if its not working in your application
what studiox version you have ?
it says activities not available in StudioX
hi,
Please go through attached zip file.
result
Forum_Task_18_08_2023.zip (24.0 KB)
file in output folder.
Hi rushikeshlanke2, can u possibly make an studiox version of this file?
thank you!
Can anyone give support because i am new in StudioX
did you try enabling the developer mode as mentioned in the post, because only basic activites are used in that file
Yes that warning make sense because i have used code. But still the process will run, doesn’t it?
To go zero code you can refer to the answer given by parvathy here, she has mentioned the answer step by step
