Need help in excel automation

Help Activities
, ,
1

Hi Everyone, I’m facing an issue in excel action. i’m attaching my excel for reference in that i have mentioned my INPUT and Required OUTPUT. Can anyone help me to solve this issue.

Split Excel.xlsx (12.9 KB)

1 Like
2

@Rana_Reddy
you can read excel file and split first cell with new line as arrry and write that array into sheet. same as you do for other cell as well

3

Hi @sandeep13 New line array means how? I’m not sure about it

1 Like
4

suppose you read excel file and output as datatable. below code give arrry
dt.rows(0)(Values).split(“\n”)
here 0 means first row index

.028 (182) - 10.80
.028 (2868) - 123.30
Y0001 - 2,225.12

5

ok @sandeep13 let me try this

6

@sandeep13 this i’m not getting it is not working for me

7

Hi,

Can yo try the following?

arrDt = dt.AsEnumerable.Select(Function(r) Tuple.Create(r(0).ToString.Split(vbCrlf.ToCharArray,Stringsplitoptions.RemoveEmptyEntries),System.Text.RegularExpressions.Regex.Matches(r(1).ToString,"\w+"))).Select(Function(t) Enumerable.Range(0,if(t.item1.Count>t.item2.Count,t.item1.Count,t.item2.Count)).Select(Function(i) dt.Clone.LoadDataRow({if(i<t.item1.Count,t.item1(i),""),if(i<t.item2.Count,t.item2(i).Value,"")},False) ).CopyToDataTable).ToArray

This returns array of DataTable. Then write them to each sheet using ForEach as the following.

img20211224-a2

Hope this help you.

Regards,

1 Like
8

Hi @Yoichi ARRDT is Array of string right data type

9

Hi,

arrDt is Array of DataTable ( DataTable[] )

Regards,

10

@Yoichi when i use Array of string it is showing error to me

11

Hi

FYI

img20211224-4

12

Thanks @Yoichi it is working. Just have a small doubt in read range how you got this IDX , is that any counter variable or you took any other

13

Hi,

Yes, it’s counter.
Can you try to create Int32 variable named idx, then set it at Index property of ForEach?

Regards,

14

@Yoichi just need some info in that code where you specified column index actually if i want to modify in future means it will be helpful because my excel having other data also

15

Hi,

If you want to modify output datatable, need to modify the following expression
dt.Clone.LoadDataRow({if(i<t.item1.Count,t.item1(i),“”),if(i<t.item2.Count,t.item2(i).Value,“”)},False)

In the above case, as structure of input and output is same, use dt.Clone. If you need to other structure, create other empty datatable, then use it like otherDt.Clone. Then add each item as argument of LoadDataRow.

Regards,

1 Like
16

Ok Thanks @Yoichi

17

Hi,

FYI, I’ll attach workflow without LINQ as the following.

Sample20211224-a1.zip (13.1 KB)

Regards,

18

@Yoichi ,

In “Write Range” Activity you mentioned about “idx.ToString”.

Can you please explain me this line???

Thanks & Regards,
Nirmalya Sarkar

19

Noticed it hadn’t been answered. Seems someone wanted the range from the data table to be written to different sheets on the workbook. The idx is likely a number or integer. “Idx” is probably defined in the loop over the data tables. The ToString converts it to text since the sheet must be expressed in text form.

Guess I didn’t spend enough time on this to understand why multiple days tables are even needed… It seems odd.

Honestly you may be able to omit the ToString since it is being appended to another string (text). Often code tooling may handle some conversions for you; otherwise, explicitly ToStringing it is needed.

20

Thanks @Yoichi this is working for me i can understand this code completely. Thank you so much for the solution