Read excel from a given file folder

Help
,
1

Hi everyone

I would like to read every excel file in a given file folder with different file name. The file name of them are irregular and convert them into one excel spread sheet (they all have same column name but different row amounts) How can I do it?

Thanks
Mightsuccess

2

Hello @mightsuccess

for each file in Directory.GetFiles(“C:\Temp”,“*.xlsx”)
read range
path: file.tostring read into tempDT
merge table activity: merge tempDT into MainDT
next

4

what is get files() operation? use assign to do it?
how to read path? Can you further explain a little bit? I’m not clear about the method to read path and get files operation

5

Directory.GetFiles(“C:\Temp”,“*.xlsx”) will actually search for any files with the extention .xlsx in the C:\Temp folder and add them to an array. You can use the Assign activity to do this…

Do a For Each of that array,
Get the the file path from the array and add a Read Range activity within the For Each loop. For the path name of the read range, you can give the current value of the item variable in the For Each.

and an output datatable variable to hold the extracted data.

Now, create another datatable variable. to hold all the data of each extracted file because the datatable variable used in read range will replace its data every time it reads an excel.

After the read range, add Merge Data Table activity and in that, for Input - give the datatable of the read range, and for the output, give the datatable of the new datatable you created…

Does it help?

8

Error occurred
Object reference not set to an instance of an object

9

Could you share a screenshot of the error and tell us at what point its occurring?

11

merge datatable.xaml (8.1 KB)

Take a look

12

I did some changes… There were few things that need to be done… Check out the workflow below. I have added few comments in places where I did the change…

This should work for you

merge datatable.xaml (9.9 KB)

If this works, please mark the answer as the solution so it will be helpful for others as well :slight_smile:

happy automating…

14

Sorry, doesn’t work
It didn’t combine all the excel files together. Conversely, it simply copy row 2 number into row 3 in each file(only 3 rows in my each excel file, row 1 is title, row 2 and row 3 are different numbers)

15

Hi… I’m sorry… my mistake…

This is working fine. And it adds all the data in excel files into one file… Check it out…

merge datatable.xaml (9.8 KB)

Sorry again for the previous one :frowning:

Hope this one helps you to get it to work :slight_smile: I got data from two excel files and added the data of those two into one… That worked fine over here…

Let me know how it goes…

5 Likes
17

Still some small problems
In my files all data are written in A and B column
but in final report, first file written in C and D column, second one written in E and F and third one written in G and H column
Is there a way to write all data in A and B column?

Others are working well

18

Can you share your write range activity to check

19

wad
wad1366Ă—768 50.1 KB

Here is the output result
I only change the working path of write range activity

20

Nope. I want to see your write range activity… and its configuration… the point it is used… Just to see why the columns are changing

21

merge datatable (2).xaml (9.8 KB)
I only changed workbook path

22

Hi…

I don’t understand why that happens. I tried executing the same workflow you sent… But it works fine for me… Do you mind sharing your set of excel files? If that is okay with you? I’m wondering whether its due to something in the excel file itself… Ideally when you specify the column in write range, it should only enter in that set of columns…

If you are able to share the excel files, I can check how those work in mine over here…

23

qw - Copy (2).xlsx (8.2 KB)
qw - Copy.xlsx (7.9 KB)
qw.xlsx (7.9 KB)
Here they are
By the way, did you change some default settings on your Uipath Studio?

24

I tried those with mine… It worked perfectly…

image
image840Ă—387 16.9 KB

Regarding the default settings, I did not change any default settings in Studio… Wonder why that’s happening…

@nimin, @nadim.warsi, if you have any idea to why this is happening, would be glad if you could share some thoughts here… I only know few guys by the name yet… So I added the names of guys I remember :slight_smile:

25

Its working as expected.
Can you delete your finalreport.xlsx file and run the process again?

26

Sorry, doesn’t work. My colleague run it have exactly the same problem

1 Like