How to get all the data from a single excel documents but different sheets

Hi Forum,

I have a question : a small use case where we have an excel document and in that excel we have 3 different sheets named for e;g sheet1 sheet2 sheet3

For E:G

  1. Sheet 1 contains data of 10 persons who we need to automate for a sign up process that is like they have first name last name dob mobile number address email country state etc

  2. Sheet 2 Contains more details of a person who was in sheet1 and here it has his salary his daily expenditure his travel area his and few more details we can look into

  3. Sheet3 contains more details relevant to sheet2 and sheet1 here he has his food eating details let it be breakfast lunch dinner.

so for 10 employees, we have 10 different data

If someone wants to automate all that data for a single person in one go how can we do it.

if I go with for each loop but it will close after the first sheet is done and we are unable to upload more data about him which is in sheet2 and followed by sheet3.

I am a newbie I have tried nested loop :smile: you guys might laugh at it but I want to know how we can do it.

TIA

Please advise how to fetch data and insert it into certains type into items. so that it can fetch all the data and type accordingly

sheet 1 - person 1 data
sheet 2 - person 1 - related data
sheet 3 - person 1 - related data

Hi @Rahul_Singh_Kamboj

Try with merge data tables and get it as single data table and Perform the process

Please refer the thread below for Merging!

Regards

1 Like

Thanks Pravin, I was wondering if I can get more advanced solution for this. as I dont want to merge the sheets.

Hi @Rahul_Singh_Kamboj

Well! You can try by joining Data tables Using LinQ.

Please refer the thread below!

And Looping @ppr @Yoichi @Palaniyappan @lakshman for more elegant Methods!

Regards

1 Like

Hi Pravin,

Thanks for your message and for taking the time out and commenting here. I have heard from my friend regarding data set or something I don’t know, but thanks for the Linq Queries it will be quite hard to join them like that. Waiting for more options.

But, I appreciate the time you have invested here as it has also made me learn new things

thanks

Hi @Rahul_Singh_Kamboj

Happy to help! :smiley:

Regards

1 Like

Hi @UiPathMaster

I was wondering if there is any one who can help please

Hello @pravin_calvin ,

Hope you are doing well mate,

I have been following your methods now as I am not able to find dataset option anywhere.

When ever I am trying to merge things its not making sense even join data table is not working properly

PFACleartax.xlsx (17.0 KB)
SequenceTest.xaml (12.0 KB)

@bcorrea @
@NIVED_NAMBIAR
@Yoichi
@sonaliaggarwal47
@prasath17
@StefanSchnell
@kumar.varun2
@jeevith
@Srini84

@Rahul_Singh_Kamboj

If there is a Primary key, then you can use Join Datatable and prepare a one Datatable and write back to the Excel sheet

Hope this may help you

thanks

1 Like

Thanks for your comment its much appreiciated mate.

I am sorry @Srini84 but we dont have any primary key here as every data in the different sheets are different even join data table is also workin very badly making copies of everything in all full inner and left join. I am sorry I am a newbie. I want to know much so that I can also help people like me here if there are any !

Hi @Rahul_Singh_Kamboj

If the Data table1 has any column that is Equals to Other columns in Datatable2 and Datatable3 then its possible using Join Datatable!

But In Your case the datatable Columns are Different.

Regards

1 Like

Sorry @pravin_calvin

yes all data is different so there is no simple way to learn this thing for a starter ? any way

PFA Excel sheet and code I was trying but it nots working

@Rahul_Singh_Kamboj

One way you can do is using For Each row
Build a Datatable with all the required headers
Now use For Each row activity, Create a index variable in properties of For Each
Inside that place assign and give value as
Let’s say
Name = dt1.Rows(Index).Items(“ColumnName”)
Occupation = dt2.Rows(IndexVariable).Items(“Column Name”)
Medical = dt3.Rows(IndexVariable).Items(“ColumnName”)

at last place a Add Data Row activity and pass the variables as an array to DatarowArray

Hope this may help you

Thanks

1 Like

@bcorrea @
@NIVED_NAMBIAR
@Yoichi
@sonaliaggarwal47
@prasath17
@StefanSchnell
@kumar.varun2
@jeevith
@Srini84

any body here knows about DATASET thing ??

Module Module1
Sub Main()
’ Two DataTables.
Dim table1 As DataTable = New DataTable(“patients”)
table1.Columns.Add(“name”)
table1.Columns.Add(“id”)
table1.Rows.Add(“sam”, 1)
table1.Rows.Add(“mark”, 2)

    Dim table2 As DataTable = New DataTable("medications")
    table2.Columns.Add("id")
    table2.Columns.Add("medication")
    table2.Rows.Add(1, "atenolol")
    table2.Rows.Add(2, "amoxicillin")

    ' Create a DataSet. Put both tables in it.
    Dim set1 As DataSet = New DataSet("office")
    set1.Tables.Add(table1)
    set1.Tables.Add(table2)

    ' Visualize DataSet.
    Console.WriteLine(set1.GetXml())
End Sub

End Module

this kind of thing >?? please

@Rahul_Singh_Kamboj

You can refer below documentation

Thanks

1 Like