Multiple excel files

Help
1

Hi

I’ve bulk excel files. Now, I want to add one column and print the data.

How to get files from the folder and update the new column?

2

hello @ganesh_rajan

1)use read range activity to read the excel file
2) then add Data column activity to add column
3)then append range activity to add column in excel

hope it help

4 Likes
3

@ganesh_rajan

To read Excel files from folder:

strExcelFiles [ ] = Directory.GetFiles("Folder Path","*.xlsx")

It will give you output as string array. Then use For each loop to iterate files one by one and follow the steps mentioned by @sandeep13

3 Likes
4

Sure guys…will try it and let you know…thanks

5

One more question
For example: look like invoice

But now i want to take table only,Don’t want balance details.How to take table from mulitple excel sheet?and finally all tables combine into a single excel sheet.

6

hello
you use read range activity .you can specified range like “A1-10” of excel that contain table .

1 Like
7

Hi @ganesh_rajan

Buddy,…

  1. Use a assign activity wth left side variable out_files of type string array and right side like
    Directory.GetFiles(“Your path”,“*.xlsx”)
  2. Use a for each loop to iterate through this string_array and mention the type of for each loop as string in type argument
    Here is your solution buddy
  3. Use excel application scope, Once you open a excel application scope you will have a property workbook which gives us the workbook type value, get it with a variable name out_workbook of type workbookapplication
  4. Use a assign activity with left side of string array type variable named out_sheets and right side like
    out_sheets = Out_workbook.Getsheets.ToList()
  5. use for each loop to iterate the sheets with this out_sheets as input and mention the item
    from for each loop to iterate the out_sheets
  6. Then you can use write range to enter the datatable
    4.Thats all buddy you are all done

Cheers

Cheers

8

image
image1292×674 28.1 KB

image

Actually i’m trying to add one more column like exno,sno,name,address

How to do that?

9

Buddy @ganesh_rajan

You can do like this buddy

  1. read range with out variable of datatable type - named out_dt
    2.invoke method with object as out_dt.Columns.Add
    and method as SetOrdinal with which you can add the column anywhere by menitioning the parameter the index where the column to be added

image
image1021×503 20.7 KB

Hope this would help you
Cheers

1 Like
10

that’s great.let me try

1 Like
11

image
image1275×696 24.7 KB

its showing "exno"on runtime only.after that i’m not able to see in excel file in this columns.

12

Buddy usually append range adds to the existing range in excel where your existing range doesn;t have this column buddy

Better we can create a new sheet2 instead of sheet1 and try to use write range buddy this will work buddy

Cheers

13

can you send .xaml file for this?

14

yah sure buddy @ganesh_rajan,

Here is your xaml
adddatacolumn.xaml (7.7 KB)

Hope this would help you
Cheers

15

Thanks buddy

1 Like
16

@ganesh_rajan
Kindly let know if it works or not buddy…else we can tweak a bit more on it

Cheers buddy

17

It’s not working actually.i tried different way.now working good… using build datatable.created successfully.

2 Likes
18

Are you in linkedin?..we can talk to there.

19

Thats amazing buddy. Kindly close this thread with right comment marked solution that could help others looking for idea under your topic

Cheers…Buddy @ganesh_rajan

Keep going

20

Yes i m there in same name buddy