For each mail attachment, get cells information and send these informations in the transaction queues

Build
,
#1

Hi everyone !

As shown in the title, i’m seeking to process each new outlook mail with an Excel file, get some cells information (always the same cells for each Excel) and put these information in the transaction queue to be reused in another robot (software provisioning).

If you have any idea, you are most welcome :slight_smile:

Thank you guys !

#2

@Vaha

Could you please confirm the below steps:

  1. Read every new email from outlook and check for an Excel file as attachment. If yes, download that file.
  2. Read excel file and get few cells information.
  3. Upload the data to the queue.
#3

In fact, i don’t want to download the Excel file, just read them without saving them in the computer. Is it possible ?

#4

@Vaha

Unfortunately, it’s not possible to read the content of a file from attachment in an email.

If you want to achieve the same, what you can do is download the file and save it on your local PC. Read the data and upload it to queue on the orchestrator.

At last, delete the file from the local so that no one could access the file in future. Just a thought on alternate approach for your task.

Let me know if it’s good to go.

#5

Well, that was what i thought.
Thank you for the response.

How would you proceed to read the data and upload it to queue on the orchestrator?

I’ am trying something like this after the download:

  • Excel Application Scope
  • Read cells (7 for all the cells i need)
  • For each item in (i’m stuck right here)
  • Add queue item
  • Debug: Write Line ("Add Queue:…)

Thank you for your help :slight_smile:

#6

@Vaha

  1. In Excel application scope, use read range activity and keep range equals to “” so that we can read entire data into datatable. Suppose dtOutput is that data table.
  2. Use for each row activity to iterate through the data table dtOutput.
  3. Inside for each row, use assign activity to store required values by creating separate variables for that. Syntax for fetching value from data table is row(“Column Name”). ToString
  4. After retrieving all values from data table, at the end within for each row , use add queue activity to upload data to orchestrator queue.

Please refer to below screenshot for adding required arguments in ItemInformation field under properties.

image
image.png1149×498 26.4 KB

Let me know if you still face any issues.

#7

I do have an issue, the information i want to get in the Excel files are 7 cells in the column F. But there is no column name.
For example, I Just want F11/F13 (string), F17/F19 (date dd/mm/aaaa), F25/F27/F32/F34/F36 (string).
To be reused separatly in a software for provisioning.

So, how can i fill the ItemInformation Input?

I though to 7 read cells, one build datatable and then an add queue item.

Thank you @singhonkar

#8

@Vaha

If possible, can you please share your excel file or screenshot for data.

#9

Of course, here is a screenshot

image
image.png469×571 4.45 KB

It is formated and hard to modify

#10

@Vaha

Yes, i can recall the same as i also came across this type excel in usecase. So, for these kind of pre-formatted cells, their positions won’t change for their respective field names. Like Job start Date and it’s textbox.

What i can suggest you is that please note down their exact Range numbers F11, F15 etc.
Use read cell activity and directly pass the range number in that, create variables for each value( Job start date etc) and pass those variables in the output.

Once you retrieve all values for one file, at last use add queue item activity to add data. Instead of using row(“Column Name”).ToString in arguments, pass the variable names.

This should work for you. Let me know for any further query.

#11

@singhonkar

Thanks for your help !
The dispatcher works like a charm.

Now, i have few question for the Perofrmer.
Have you some best practice to get all the variables from Orchestrator ? (All the cells to be reused in performer activities)

And i will have to split the two date variables to have a click day, click month and click year on both of them. Have you an idea how to proceed ?

Thank you for your time.

KR,

1 Like
#12

Dear @singhonkar,

I bought a licence, that is why i changed my account, but i’m still Vaha :slight_smile:

Just a refresh for my question to get all the variables from Orchestrator ? (All the cells to be reused in performer activities => just recreate all the arguments with a “in” ?)

And i will have to split the two date arguments to have a click day, click month and click year on both of them. Have you an idea how to proceed ?

Thank you for your time.

Kind regards,
Vaha

#13

I am looking to do something exactly like this.

  1. Read new email from an outlook folder, and check for Excel attachment.
    If yes Download file(s) (could be multiple).
  2. Read the excel file and get data from same cells.
  3. Upload data in system.

I am having trouble compiling the list of excel attachments, any thoughts?