Convert mail body (array) to data table or dictionary

#1

Hallo everybody,

I receive an email which contains a parameter sheet like below and I have to process the values in the second column (“Value”)

image
image.png1106×529 18.4 KB

So my idea starts with getting the body as string and convert it into an array. After that I tried to clone a data table of that parameter sheet with the items in the array:
image

The problem is now that the items from my array are only inserted into the first column and not across all 3 columns.

My final idea was to convert the data table into an dictionary and use it similar to a config file where I say parameterSheet(“Period”).toString an get the value next to it.

How can I convert an array to a data table where the items are inserted across all three columns?

Thank you

#2

Is the parameter sheet sent as attachment or its the body itself?

#3

Hi,

yeah its the body itself.

#4

Hi @mamko46

In Add Data Row property pass your array in the ArrayRow field
image

#5

Hi @aanandsanraj

thank you for the suggestion but I get the following error: Add Data Row: Input array is longer than the number of columns in this table.

My columns look like this:
image

#6

@mamko46

I think array has more than 3 length. Check the array length and array data.

#7

@aanandsanraj

The array contains every element from the parameter sheet which is more than 3. Somehow empty entries are still in the array even though I used following code:
image

So all in all 68 elements (I’m using the original parameter sheet which contains more than the example above)
image

1 Like
#8

Hey I am working on a different solution but this is also not working:

So Im creating a new datarow and passing the values of the array to the correct column.
image
image

But as soon as the variable “data_row” (type is datarow) has been added to the datable I cant use the same variable again. The error message is that this row already belongs to this table.

Is it somehow possible to give the variable data_row a dynamic name?

Increment with something like data_row1?

#9

Fine
you were almost done
just a small correction
–use a assign activity bodayArray = mailBody.Split(Environment.Newline.ToArray())
–then build datatable activity
–use a for each activity and pass the bodyArray and change the type argumet as string
–now use a add data row and mention the array row as
split(item," ") and in the datatable mention the datatable name
–then we can write this datatable to excel with write range

hope this would help you
Cheers @mamko46

#10

@Palaniyappan

Thank you for your help but the error message still remains: Input array is longer than the number of columns in this table.

If I play around with split(item," ") so split(item,"") add data row does work but the result is not correct
image

#11

For everyone with a similiar problem:

Since the values in column “Paramater” are static:

  • determine the index of a string: bodyArray.ToList.FindIndex(Function ( c ) c.ToString=“My String”
  • index +1 for the next value
  • bodyArray(index) to get the value