Data Collection from Excel Files in a Folder

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. (25.5 KB)

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

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

1 Like

@Pranee_007 Give me few moments.

1 Like

Sure. Thank you

Sure. Thank you @Shubham_Dutta

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

File_Names_and_Tabs.xaml (66.1 KB)

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

Thanks & Regards,
Shubham Dutta

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?

Adding Output Screen as well for your reference

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

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

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

1 Like

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

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

Thanks & Regards,
Shubham Dutta

@Pranee_007 have your problem been solve?

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

@Pranee_007 That’s great. Happy Learning.

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?

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

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

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

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