Get data in excel

Hello Team,
I have a query regarding getting data in excel sheet.
Scenario: I am using extracting data using data tabe and then filtering the data using Filter data.
Later i am uploading those filter data in the Queue.
After that in Process.xaml I am processing that data one by one.After processing I am sending an email.
Now I want those prcessed data in an excel sheet.
Any help how can i do this.

@amitkk_84,

In the DataTable , where you are passing it to process.xaml, Add one additional columns say status…

  1. For each in DataTable
  2. Row wise processing in Process.xaml
  3. If success - updates Status in Datatable as Success for the row which is iterating.
    4.After all - You can write the entire DT to a new excel or write the Status in front of source excel.


Mukesh

Thanks a lot Mukesh for the Information.
I am using REFrameWork. and after each process execution i am passing the status using set Transaction status activity…
Here is the process.
Under INIT
I am getting the data through data table , filtering it (I am passing only two parameters) and the uploading to queue.
Then in Process Transaction , i am processing each data.
Then in End process i am sending email.

In all the above process how can i get the processed data in an excel sheet and when i need to exactly use it in which flow since all the processed data should be captured.
Thats the confusion i m facing

In the Process Transaction , you can have one more arguement as DataTable say DT

  1. Pass the DataTable from Init to Process.xaml.
  2. Add one column in the DT say Status.
  3. Inside Process - where you are processing each data adn getting Success/ Failure.
    4.After this , you will put a Code to Update the status in DT by matching the DT Row with the item which is being processed.

so lets say the 2 parameter you are passing is Id and Name … You will Look up the Datatable DT for ID and update the status in it.

Hope it make sense !


Mukesh

Hello Mukesh,
I am getting what you are saying But how can i extract all the processed record into excel

You will put all the data in the excel…

Let me rephrase

  1. Let’s say we have 20 records initially in the beginning in a excel.
  2. We read this excel in init and get a data table with length 20
  3. In process.xaml , we pass this 20 row data table as a whole.
    4.Add an column status to this data table.

** Till here , we have datatable which is passsed in process.xaml along with the each data for transaction.

  1. Now we process each data … Let’s say each data resulted success.
    6.At the end of each row - I filter the datatable with 20 records for the for each row entity and update the status column in front of it.
    7.At the end , we will have a Datable with 20 records and a status column for them indicating success or failure.
  2. Use this datatable now to write back to excel.


Mukesh

Ok.
Thanks a lot Mukesh for the detailed information.
But i am not reading data from excel.I am taking data from SAP Table and then filtering it in data table and then passing it in the queue.
After each data is processed in process.xaml i am passing the status from get_transaction_status.xaml and passing the status to queue.

Hello Mukesh,
Please find attached screenshot data table and writing to excel which i have included now.

Now in the process.xaml i will have to take a counter variable and then put that to read through excel.
This can be something like this.Please correct me if i am wrong.

Is this logic correct?

Here problem is the counter is always set to 2 as i have declare it in the start so if i want to go ahead with this method then how to use this counter condition.
Also according to the point “6.At the end of each row - I filter the datatable with 20 records for the for each row entity and update the status column in front of it.”
If i add the filter data table thenwhat input should be given.as the filter data table i have dont in init and now i am using it in Process.

In the Init - After we have filtered the Data , How about copying the Entire Filtered DataTable to a New DataTable and passing the new copied DataTable to Process.Xaml.

Does it make Sense ?


Mukesh

I am passing the filtered data to queue.
Also i have tried another method of getting data to excel via build data row.But somehow i am getting error "Key is not part of dictionary " for the last item even though it is present.
Please have a look at the flow and let me for any suggestions.

This Seems Correct , Please Verify the name of the Content and recheck for any typing mistakes in name. any extra spaces ?


Mukesh

Ok.Thanks a lot.

Also one query regarding earlier workflow where i am declaring a variable and appending the excel sheet using the data.
Actually i have declare an argument in_counter and outside i am assigning the value as 2 so what i want is when it first comes to the process.xaml it should have value as 2 and during the process execution i am increasing the counter by 1 but it not again take the counter variable as 2 for the next transaction.
Thats my confusion

So , in_counter will always be 2 as it is passed as arguement … and Whenever the flow is called again , you are getting value 2 . remember arguments always take precedence over variables.

What you can do is Increment the counter outside the Invoke and pass the incremented counter where you are passing 2
or

Inside your Flow - Take Another variable whose default value is 2 and increment it inside the flow.


Mukesh

Here is the screenshot what i have done.
Here problem is when i again comes for processing the next transaction it is again taking 2.Actually it should have value as 3 and not 2

Hello Mukesh,
Ru talking something like this .
But here also i have to declare counter variable as 2 and later i am incrementing it by 1
SO when it again comes to process.xaml for processing next trasaction , it will still have value as 2,rt?
I am using this counter to insert value in excel mentioned just above the variable(which is commented)

Here , the scope of variable seems to be the Issue . So we Take a sequence say ExcelSeq here and drag the excel wite and the Counter+1 part inside it.

For the counter variable the scope will be now the ExcelSeq .

You will have to restrict the Counter variable scope to avoid it overwriting.


Mukesh

Thanks a lot for the reply.
Do you mean something like this.
image

Yes :slight_smile: Now you can use a writeline and print the value of counter before directly passing it and see it it works fine across iterations.

Hello Mukesh,
Even after including in other sequence the counter is again setting as 2. Please find the attached screenshot.Can you please help me what is the issue.