Fetching data from excel to update fetched data to attendance excel sheet

Help Studio
, , , ,
1

Hi there,

Looking for help on the below scenario.

I have two excel workbook,
from excel 1 have to read data and fetch Employee Code, Employee Name, InTime, OutTime (If In and out time is blank have to put 0 (zero) as value), also if any employee is starting date is other that 1 of month then the rest of the date’s In and out will be 0(zero) and put those data in excel 2 field.

The format of both excel is completely different,

{Input data sample}
input -
Daily Attendance.xls (165 KB)

image

{Output data sample)
Output-
Output.xlsx (12.4 KB)

image

Thanks in advance

@Palaniyappan @Yoichi @ppr @THIRU_NANI

2

Hello @indrajit.shah , You can use If activity in For each Row Loop Activity
Condition: currentrow(input).IsNullorEmpty use write cell and enter “0”
currentrow(Output).IsNullorEmpty use write cell and enter “0”
Then write currentrow(input).tostring

3

@Gokul_Jayakumar ,

My concern is just not only putting zero its about fetching the rest of the data from input excel and making it organized as per the headers in output excel file, please see the post one more time.

4

@indrajit.shah Do you need a Range of Header from the output based on input? Use the Lookup range Activity and Get the Exact range of the header in Output. based on it You can write data in Output file.

5

I have tried to replicate but result in failure, that’s why I posted. :frowning:

6

@indrajit.shah Here Follow the steps you may get some Idea

  1. Read Full Rage
  2. Look up the name you needed. For example: Pradip Das
    You will get LookupName= H39
    System.text.RegularExpression.regex.match(LookupName, “\d+”).tostring, will get row number 39
    image
    image950×554 27.8 KB
  3. Again add read range activity, Feed range activity as “B”+(System.text.RegularExpression.regex.match(LookupName, “\d+”)+1).tostring, The bot started reading from B40. You can get data from Pradip Das and Write data on the necessary output data field.
  4. To find Total Duration You may use the Regex expression to split each data and can use it.
7

Hi @Gokul_Jayakumar ,

Thanks for your suggestion, but I am looking for more dynamic, plus I will not have any list to compare, I will have to extract every thing from the input file and put it in output, Can you give it a try to extract the data from input and get it in output including the intime and outtime?

8

Hello @indrajit.shah , If the value in certain structure is possible for more dynamic and easily. But
It has some complications structure. Dynamic is possible but we have to spend more time for it.

9

Hi @Gokul_Jayakumar ,

I am expecting more data in input file, but the structure of the data will be fixed, dose this make sense?

I have to extracted data such has Employee code, Employee name, and put it in under the exact column and similarly, have to get the InTime and OutTime from the input file and put the values under same date as it is in the input file.
I have put the screenshot in the topic(expected output screenshot)