How can I read all .xlsx files in a folder and read tab names from each excel

Help Studio
, ,
1

Hi team,

I am having around 150 files in a folder. Each file has different name & different number of tabs.

I want an output that gives each file name and tabs within them. Is it possible?

I have started with For Each File in Folder. Can you guide me to what needs to be done next?

2

@Pranee_007 yes it is possible

Hi

Welcome to uipath forum

Hope the belwo steps would help you resolve this

  1. Use a Assign activity like this

Arr_filepaths = Directory.GetFiles(“yourfolderpath”)

Where arr_filepath is a variable of type array of strings

  1. Then use a FOR EACH activity and pass the above array variable as input
    Change the type argument as string in the property panel
  2. Inside the loop use a EXCEL APPLICATION SCOPE and pass the filepath as item.ToString
  3. put veriable in excel scope properties " output"
    5-for each and put output variable in it.
    like->
    image
    image1181Ă—721 33.2 KB

code/////
Sheets (3).xaml (9.5 KB)

updated solution like your requirement
|File Name|Sheet Name|
|Apple|Ooty|
|Apple|Shimla|
|Mango|Delhi|
|Mango|Goa|
|Mango|Shimla|
Sheets (3).xaml (12.9 KB)

1 Like
3

Hi @raja.arslankhan - Can you please guide me as to how this can be done?

4

@Pranee_007 I am try to send you a code

1 Like
5

Hi @Pranee_007 ,
File names okay possible.
What do you mean by tabs can you please elaborate a bit with sample files.

Thanks & Regards,
Shubham Dutta

6

Hi @Shubham_Dutta - By tabs, I mean sheets in excel.

Let’s say I have 2 excel files in a folder. One file name is Apple that has sheets - Ooty & Shimla. Other excel file name is Mango that has sheets - Delhi, Goa & Shimla

I am looking for an output like below.

|File Name|Sheet Name|
|Apple|Ooty|
|Apple|Shimla|
|Mango|Delhi|
|Mango|Goa|
|Mango|Shimla|

7

@Pranee_007
Check I have placed guidline above and now I am try to code for you

8

Hello @Pranee_007 Welcome to Uipath Community
Try this method
Use For each file in folder activity. it will get all your file from the folder and can filter “*.xlsx”
To know file name, CurrrentFile.Name
image

9

Hi @Pranee_007

Check out the XAML file

RenameFiles.xaml (12.5 KB)

image

Regards
Gokul

10

Hi @Pranee_007 ,
I am attaching a zip file with code and sample excel files in a folder for your reference.
I hope it will help you.
New folder (2).zip (13.9 KB)

Please change the folder location here in the code:

image
image779Ă—182 6.39 KB

Thanks & Regards,
Shubham Dutta

11

@Pranee_007 I have attached code and guidance in my first comment. please check

12

Thank you @raja.arslankhan for very quick turnaround. Can I not get the output in a tabular format or on a excel file?

13

@Pranee_007 yes sure. give me few moments

14

Sheets (3).xaml (14.0 KB)
please follow this one

15

Okay. Thank you @raja.arslankhan . How do I retrieve the data table once execution is completed as I am not able to see anything in the outputs window

16

@Pranee_007
please in last put read range to write data in excel file.
Sheets (3).xaml (14.5 KB)

17

@Pranee_007 please mark this one as solution and like if you get any help so others can get help from it

18

Hi @Pranee_007
Thank you so much Response. Happy Learning.

Regards,
Raja Muhammad Arslan Khan

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.