Looping through excel sheets

Hi all, i have some excel sheets from each excel sheet i want first row data and store it in one excel sheet
Can anyone suggest me how can i do these process

  1. Get sheet names using above link
  2. Iterate the sheet names using for each activity
  3. Read Range inside the body of the loop
  4. Get first row of datatable - Dt.Rows(0)
  5. Add this datarow to output datatable using Add datarow activity. (Before For loop, create output datatable using build datatable activity.)
  6. Finally after the loop write the outputDatatable using write range activity.

Thanks!

1 Like

Hi @Manisha541,

First of all if you have multiple sheets in one excel workbook, then get the sheets by assigning sheets=workbook.GetSheets.
Now use For Each activity to loop through all the sheets. Then use For Each Row activity inside the For Each activity to read the rows of the sheets. With the help of Break activity you can read only one row i.e., the first row. Now write the row into a pre-built Data Table .
Then write the data table into an excel sheet.

Thanks & Regards,
Apurba

@apurba2samanta ,i have a folder which has some excel sheets each excel sheet need to be opened and first row should be extracted and saved into one excel sheet

Okay @Manisha541,

So to get all the excel sheets use the below syntax -
Directory.GetFiles(“Your Directory”, “*.xlsx”,SearchOption.AllDirectories)

Now use For Each activity to loop through all the sheets. Then use For Each Row activity inside the For Each activity to read the rows of the sheets. With the help of Break activity you can read only one row i.e., the first row. Now write the row into a pre-built Data Table .
Then write the data table into an excel sheet.

Please feel free to discuss if you stuck anywhere.

Thanks & Regards,
Apurba

1 Like