Add columns based on date in excel sheet

Help
, ,
1

Hi all,
I am trying to add the columns from multiple excel sheets of different dates into one excel sheet… Here i am providing two example sheets(input sheets) EmployeeInOutDuration 01.08.2019.xls (57 KB) EmployeeInOutDuration 02.08.2019.xls (59 KB). From that two sheets, based on the dates I want to write the data in the below sheet.
Final out.xls (28 KB)
Please help to solve this.
Thanks in advance!

2

Hi Sharmila,

Could you please give us more details? Which step is blocking you?

Thanks!

Best Regards,
Susana

3

Hi Sharmila.

Please find the below points for the solution of the scenario

  1. Add build datatable and add the required columns (Common Columnns in the excel and add date column)

2.Place the excel files in to specific folder

  1. Using ForEach file In Directory.GetFiles(folder) and get the path file name Path.GetFilename(file)

  2. Split the date from the filename using regex or string manipulation.

  3. Using Read Range activity get all the data from file.

  4. Using one more for each activity and loop the read range output datatable variable and store it into new datatable which was created “Step 1” using Add data row activity. In that add row you can add all the values with the extra date column value.

7.Finally convert the datatable to excel using write range activity outside foreach loop of directory folder.

If you have any doubts in the above steps. Please let me know.

Thanks,

Pradeep Sridharan

4

Hello @Pradeep
Can you please provide with .xaml file because I cannot able to go through some of your points
Thanks in advance

5

Hi @Susana,
I cant able get the Dates.When ever i run the bot, i want to get the date from file name.
Please help me out
Thanks in advance

6

Hi @Sharmila,
I have gone through your Xls files but could you please get me through what is your exact problem you are facing.

Thanks,

Happy Learning

7

Hi Sharmila,

Here i am working in citrix environment. So i cant able to get the xaml file to outside from my environment. So please let me know which point do you want to clarify from me.

Do you want to add the date columns in the new excel and consolidated data from the two excel in to single excel based on date?right?

Thanks,

Pradeep Sridharan

8

Hi @Sharmila

I have attached the workflow for your Ref
with same Input files .
InputFiles
image
OutputData
image

PFA,
testing.zip (48.7 KB)

Is this OK

9

Hello @Brian_Patel,
From these files EmployeeInOutDuration 01.08.2019.xls (57 KB) EmployeeInOutDuration 02.08.2019.xls (59 KB) I have to get the column (InDuration (in hrs)) in output sheet i.e., Output.xls (29.5 KB) accordingly to the dates mentioned in the excel sheet. I want to read and write the data based on the dates. But I cant able to get the data in specific dates.
Finally i want the output like the following sheet Final out.xls (32.5 KB)
Thanks in advance!

10

Hi @VISHNU ,
Great idea! Thanks for your Zip file,but i was little confuse ,where should i attach this with my work flow.
Thanks in advance

11

Yes exactly @Pradeep, like the below format

image
image1366×768 61.7 KB

1 Like
12

@Sharmila Yes. I’m Working on your Problem and I’ll get back to you ASAP.

Thanks,

Happy Learining.

13

Where u need to use those datas separated from fileName?