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)
Hope these steps would help you resolve this issue
Use a excel application scope and pass the file path as input and get the output dt with READ RANGE activity
Now use assign activity and mention like this
dt = dt.DefaultView.ToTable(False,”Description”,”Credit”)
Now dt will have only the first and second column
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()
- Now use a write range activity and pass dt as input
Hope the following helps you.
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.
Got RUN TIME ERROR!
" Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’ "
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.
I need to fetch only the first part!
I just modified it as the following.
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.
Thank you for the solution.
That is working.
But Can you please explain me why have you used VLOOKUP and Filter here?
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.
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)
Your xls file seems illegal format. Can you check it?
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
Assign: Column ‘Description’ does not belong to table DataTable.
In my environment, it works without error.
Can you check the following?
Sample20210920-1v3.zip (20.5 KB)
Thanks a lot!
Its working fine.
Probably I must have done some mistake. Will check with that.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.