How to read specific data from a excel file which is placed in a folder and folder contains 30 excel files

Learn Academy Feedback
1

Hi,
I want to read the data from excel files which is placed in a folder(the folder contains 30 excel files),
here i want to check the data in each excel file by using date or some id(if any of the excel file contains the data related to what i have searched is to be written into another excel file).

2 Likes
2

You need to use Directory.GetFiles(“FolderPath”,“File Extension”), this will give you an array then you can apply for each loop on it and perform operations on each file

1 Like
3

@PrankurJoshi
Ok,

It will ask for sheet name of excel file,is it possible to read excel file without sheet name.

1 Like
4

Its not possible to read the excel without sheet name. You can get the sheet name by creating Workbook variable in Excel Application Scope

2 Likes
5

@PrankurJoshi
Can u please give me the sample workflow for it.

1 Like
6

Here it is -

Main.xaml (8.0 KB)
Sample.xlsx (8.8 KB)

this will give you sheet name then you can use Read Range with this

3 Likes
7

@PrankurJoshi
That works,but for me i have excel workbooks in a folder(30 excel books) and 30 excel workbooks have different sheets with different names,now i have to read specific data from excel workbooks(such as based on date or id’s) for this i have to check each workbook in the folder with sheet name.
How can i get this,can u please help on it.

1 Like
8

Here is the example which would pick each excel file in the folder path given and for each excel file operations can be performed by Excel Application Scope

Main.xaml (10.2 KB)

Excel Files.zip (30.6 KB)

2 Likes
9

@PrankurJoshi
Hi,
Is it possible to read the data in excel files in your folder instead of reading sheet name.

1 Like
10

We read the sheet name because the sheet names for so many files can not be known. yes you can read the data by using Read Range activity in same workflow

1 Like
11

@PrankurJoshi
ok,in the reading excel file if the repeated values are coming,it is showing an error

“A column named (some value) is already belongs to this data table.”
Is there any solution for it.

1 Like
12

Column names should be distinct. For removing duplicates you can use Remove Duplicate Rows activity

2 Likes
13

@PrankurJoshi
Actually in my files there is no column names

1 Like
15

you want to read ex1.xlsx to ex5.xlsx right? and After reading?

1 Like
16

@PrankurJoshi
Yes,It is the sample files.
But in my original data there is no column names for it.

1 Like
17

what do you want to do after reading files?

1 Like
18

@PrankurJoshi
I have to write each file into an excel file with some column headers(A1,A2,A3,A4)like that.

1 Like
19

You would have to something like this to achieve what you want -

Main.xaml (15.0 KB)

1 Like
20

@PrankurJoshi
ok,
But in read range activity,i don’t want to specify sheet name,
is it possible to read data without specifying sheet name.
And also i have to write each excel file data into the separate excel file.(i don’t want it to be in single excel file).

1 Like
21

You will have to specify sheet name for read the file. Yes you can have the dynamic file name in excel application scope write range that will allow you to save files with different names

1 Like