Data Collection from Excel Files in a Folder

Help Studio
, , ,
1

Hi team,

I have lot of excel files in a folder. Each excel file can contain 2-3 sheets.

I am attaching couple of sample files here. I am looking to collect specific data from these excel files.

Data I am looking to collect are File Names, Tab/Sheet Names, Check if some columns are present (If Yes, Check if there are any duplicates & If No, Duplicate check is not applicable)

Output file attached here shows the data the I want to collect. I want to collect this data for further analysis. Is it possible to get the data in a tabular format like in attached output file.

Kindly help me with this usecase. Thanks in advance.
UseCase.zip (25.5 KB)

2

@raja.arslankhan @Gokul001 @Shubham_Dutta - Can you provide your inputs if you think this is feasible?

3

Hi @Pranee_007 ,
It is possible give me sometime I will code it and provide

1 Like
4

@Pranee_007 Give me few moments.

1 Like
5

Sure. Thank you

6

Sure. Thank you @Shubham_Dutta

7

Hi @Pranee_007 ,
I am sending you the final code just add your folder address here:

image
image758Ă—140 6.16 KB

File_Names_and_Tabs.xaml (66.1 KB)

You will get output in: “Final_Output.xlsx” file

Thanks & Regards,
Shubham Dutta

8

Hi @Shubham_Dutta - Thank you for building this. I tried to open the workflow and I am getting some error.

I am attaching the screen with error here. Would you be able to help?

Error
Error1761Ă—846 27.8 KB

Adding Output Screen as well for your reference

Error1
Error1889Ă—265 18.4 KB

9

Hi Raja - Were you able to get any breakthrough here? Or can you guide me to start working on this?

10

@Pranee_007 Its version compatibility issue and I do not know which shubham_Dutta is using

11

@Pranee_007
Ok. I can give you favor when I will be free from my work, I will guide you which activities are missing

1 Like
12

Hi @Pranee_007 ,
Please try to update your excel activities and uipath activities.
image

This are my versions.
After updating to respective versions you can run the activities in the workflow.

Thanks & Regards,
Shubham Dutta

13

@Pranee_007 have your problem been solve?

14

Yes @raja.arslankhan. Shubham Dutta’s answer is working absolutely fine.

15

@Pranee_007 That’s great. Happy Learning.

16

Hi @Shubham_Dutta,

Thank you very much for your time. The workflow is working perfect.

One last thing - I am not very good with VB expressions. So, I used System.IO.Path.GetFileNameWithoutExtension(File.ToString) - Which is working fine.

However, I am trying to figure out how to remove .xlsx from the file name (1st column of the output) using expression. Can you help with the expression?

17

@Pranee_007 are you want to get file name without extension and store to excel for each file?

18

Hi @Pranee_007 ,
Happy to hear you got your solution for just file name in column 1 you can use this assign activity.
image

The expression is: file_name.split("."c)(0)

If there are no more queries please mark the answer as solution and close the thread.

Thanks & Regards,
Shubham Dutta

19

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