How to extract specific column value and paste in other excel file

Help Activities
, ,
1

Hello,

I have 2 Excel file. which consists of data.

File1 has been attached.
Basically I need to Fetch "Credit " for “Acquirer WDL Transaction Amount” and “Acquirer WDL Transaction Amount (Micro-ATM)” , from ‘FILE1’

And write those amount in other Excel file! (FILE 2)
FILE1.xlsx (10.2 KB)

Regards,
Nidhi

2

Hi

Hope these steps would help you resolve this issue

  1. Use a excel application scope and pass the file path as input and get the output dt with READ RANGE activity

  2. Now use assign activity and mention like this

dt = dt.DefaultView.ToTable(False,”Description”,”Credit”)

  1. Now dt will have only the first and second column

  2. Then use the below assign to get the datatable with those two rows in exact

dt = dt.Asenumerable().Where(Function(a) a.Field(of String)(“Description”).ToString.Contains(“Acquirer WDL Transaction Amount”) AND a.Field(of String)(“Credit”).ToString.Contains(“Acquirer WDL Transaction Amount (Micro-ATM)”)).CopyToDataTable()

  1. Now use a write range activity and pass dt as input

Cheers @nidhi.kowalli

3

Hi,

Hope the following helps you.

img20210920-1
img20210920-1619×610 15.5 KB 

dt = dt.AsEnumerable.Where(Function(r) r("Description").ToString="Acquirer WDL Transaction Amount" OrElse  r("Description").ToString="Acquirer WDL Transaction Amount (Micro-ATM)").CopyToDataTable

Sample20210920-1.zip (12.1 KB)

Note: You might need to modify range of ReadRange activity if you want to process only first table in the sheet.

Regards,

1 Like
4

@Palaniyappan

Got RUN TIME ERROR!
" Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’ "

5

Thank you!
works.

If you could see, there are 2 entries for “Acquirer WDL Transaction Amount”.

Out of which I need to fetch only the first part!
This is where I’m facing issue.

6

Hi,

I need to fetch only the first part!

I just modified it as the following.

img20210920-4
img20210920-4642×613 16.3 KB

dt.AsEnumerable.Take(num).Where(Function(r) r("Description").ToString="Acquirer WDL Transaction Amount" OrElse  r("Description").ToString="Acquirer WDL Transaction Amount (Micro-ATM)").CopyToDataTable

Sample20210920-1v2.zip (10.6 KB)

Hope this helps you.

Regards,

7

Hi @Yoichi
Thank you for the solution.
That is working.
But Can you please explain me why have you used VLOOKUP and Filter here?

Regards,
Nidhi K

8

Hi,

In the Sheet, each part starts with cell which has “Description”. So we can get index number of the cell using LookupDataTable (Lookup for “Description”) (Let’s say num as the index number)
Then, we can get first num rows using Take(num) method in the middle of the expression.
As a result, we can get the first part and get target rows in it.

Regards,

1 Like
9

Hello,
Thank you for knowledge.
Sharing you an actual file.
Issue m facing is, The above code works well with “Acquirer” part.
I also need to Fetch “Issuer” part . which is giving me error!

I have highlighted Cells, which i need.
please get me a solution!! NTSLJSF030821_1C.xls (9.7 KB)

10

Hi,

Your xls file seems illegal format. Can you check it?

Regards,

11

CYCLE1.xls (36.5 KB)

12

Hi,

Can you try the following expression? I haven’t test it, but it will work probably as expect.

dt = dt.AsEnumerable.Take(num).Where(Function(r)  r("Description").ToString.Contains("WDL Transaction Amount")).CopyToDataTable

Regards,

13

ERROR!

Assign: Column ‘Description’ does not belong to table DataTable.

14

Hi,

In my environment, it works without error.

Can you check the following?

Sample20210920-1v3.zip (20.5 KB)

Regards,

1 Like
15

Thanks a lot!
Its working fine.

Probably I must have done some mistake. Will check with that.

16

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