Read every four rows and save to datatable for each device

Help Studio
1

Hi,

i need to read this example excel test.xlsx (9.6 KB) which has data for three devices. Each device has 4 rows:

Ser.no. 200877
Inv.no. 151155
Date finished 06.09.2023. 10:27:25
Counted number 4326223

how to read every four rows and save to datatable for each device. The row count can differ.

2

Hi @markosc ,

Could you maybe also provide us with an Expected Output format for the Input data provided, so that we can suggest the logic accordingly for it ?

3

The best way it would be if we can make the ouput like this:
image

4

@markosc ,

Could you maybe try to check with the below Steps :

  1. Read the Excel sheet as a Datatable, say DT.

  2. Prepare the Expected Output Datatable Schema using Build Datatable Activity. Let is be called OutputDT.

  3. Next, Use Output Datatable Activity to Convert the Datatable data to Text.

  4. Use the text output of Output Datatable activity in the Matches activity with the below Regex configured.

"Ser\.no\.(.*)\r?\nInv\.no\.(.*)\r?\nDate finished(.*)\r?\nCounted number(.*)"

image
image871×333 22 KB

  1. We should then be able to get the Matched content and group it so that we add it to the Required Columns to the Output Datatable using the below Expression in an Assign Activity :
OutputDT = mc.Select(Function(x)OutputDT.Rows.Add(Enumerable.Range(1,4).Select(Function(i)x.Groups(i).ToString.Trim).ToArray)).CopyToDatatable

image
image886×188 11.3 KB

Overall Configuration :
image

Debug Panel :
image

Let us know if you are not able to follow the above steps.

5

Thank you very much works great! Can you please explain me this so that I know it for next time:

OutputDT = mc.Select(Function(x)OutputDT.Rows.Add(Enumerable.Range(1,4).Select(Function(i)x.Groups(i).ToString.Trim).ToArray)).CopyToDatatable

6

@markosc ,

Hope the below is understandable :

  1. mc.Select(...): Within this part of the code, the Select method is used on the Matches activity output, which is a Collection. It is employed to transform each element of the mc collection into a new structure. In this context, it’s converting each Match object in mc into an array of values.
  2. Function(x) represents an individual Match object x within the mc collection.
  3. OutputDT.Rows.Add(...): Within the Select function, for each Match object x, a new row is appended to the OutputDT DataTable. This row is filled with values retrieved from the subsequent expression.
  4. Enumerable.Range(1, 4): This generates a sequence of integers starting from 1 and ending at 4 (inclusive). The sequence contains the numbers 1, 2, 3, and 4.
  5. .Select(Function(i) x.Groups(i).ToString.Trim).ToArray: For each number in the sequence (1, 2, 3, 4), it accesses the corresponding group from the Match object x, converts it to a string, and trims any leading or trailing whitespace. The outcome is an array of strings containing values extracted from matched groups.
  6. .CopyToDataTable: In conclusion, the resultant array of arrays (representing values from the matched groups within each Match object) is transformed into a fresh DataTable using the CopyToDataTable method. This DataTable is then populated with the extracted data.

Maybe also check the below on Linq :

7

Thank you!

One more question, I have to add one more column “Device name”
so the input would be like this:
Device name Glory1258
Ser.no. 200877
Inv.no. 151155
Date finished 06.09.2023. 10:27:25
Counted number 4326223

and the table would then have the column “Device name” with value Glory1258 how can I add this in the assign acitvity?

8

@markosc ,

For this addition we can have the below modification :

  1. Add the new Column with name Device Name to Build Datatable Activity, so that we have the Expected Output Datatable Schema.

  2. Next, The Regex Expression should also be modified in the Matches Activity to also take into account the Device Name value. So the modified Expression should be the below :

Device name(.*)\r?\nSer\.no\.(.*)\r?\nInv\.no\.(.*)\r?\nDate finished(.*)\r?\nCounted number(.*)

image
image1081×509 45 KB

  1. Next, Change the Enumerable.Range(1,4) to Enumerable.Range(1,5) in the below provided Expression :

You should be able to get the Required Output.

9

I tired with 5 and 11 columns and I first added them to build datatable. The matches works fine and prints out the values correctly but when I use assign activity: mc.Select(Function(x)OutputDT.Rows.Add(Enumerable.Range(1,11).Select(Function(i)x.Groups(i).ToString.Trim).ToArray)).CopyToDatatable

the log says that outputDT has 9 rows which is okay but when I write it to excel it is empty. I can provide you my xaml if you want.

10

@markosc ,

You could share the workflow, but if you could provide us with the Screenshot of Write Range Activity and the Log Message Activity before it, then it would be able to help us analyse and also help figure out the issue.

11

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