2 rows in 1 column Need to split and process one by one in Ui element

[chaitanyaKumar]

Hi Communiy,

i have an issue which the input file will have data like 2 rows of data in 1 colum. as per below

now i need to split the data and process like 2 individual data in web portal.

can you please help on the split code.

Regards,
thanks.

[Mir.Jasimuddin]

Hey @chaitanyaKumar,

Use Split(Environment.NewLine) or vbCrLf on each cell value to separate multiple rows. Loop through the resulting array and process each item individually in the web portal.
Refer:

Activities - Split Text

[prashant1603765 (Prashant Kumar)]

Hi @chaitanyaKumar

Pls check this thread:

Splitting A2 Cell Data into Columns and Rows

Hi All, Im getting all data into A2 cell from webpage. I just want split data into mutiple columns and rows using Collon(“:”). how can i create a workflow. below is the sample data. Column-0 “DESC. 3: REDEMPTION DATE: JANUARY 01, 2023 REDEMPTION PRICE: 1000.00000 INTEREST: 0.00000000: MATURITY DATE: MAY 01, 2041 CODE:2: COUPON: 3.500000 (APPLICABLE, NOT AVAILABLE) DATED DATE: JULY 28, 2016 TYPE OF CALL: PARTIAL PUBLICATION DATE: NOVEMBER 30, 2022 CALL AMOUNT: 56.00: DEPO. 1: UTRUST, …

[manasrlenka25 (Manas Lenka)]

Hi @ChaitanyaKumar,

To get the result, I cloned the original DataTable and used a “For Each Row” loop to go through the data. I split the cell values using line breaks and added the split values as new rows using the “Add Data Row” activity.

Since the data in Excel was split using Alt+Enter, Environment.NewLine didn’t work. So I used:

cellValue.Split({vbCrLf, vbLf, vbCr}, StringSplitOptions.RemoveEmptyEntries)

I’ve attached my workflow for reference. Let me know if you need any further assistance!
Excel_2 rows in 1 column.xaml (13.1 KB)
Excel_2Rows.xlsx (9.5 KB)

[gksumit (GKS)]

Hi @chaitanyaKumar ,

To achive this, follow below steps :

1. Create a datatable with same structure as your excel file e.g dtOutput using build datatable activity

2. Create a variable temp of type List and assign the value “dtInput.AsEnumerable().SelectMany(Function(r) Enumerable.Range(0, r(“Name”).ToString.Split(vblf, StringSplitOptions.RemoveEmptyEntries).Length).
   Select(Function(i) dtOutput.Rows.Add(
   r(“s no”).ToString,
   r(“Name”).ToString.Split(vblf, StringSplitOptions.RemoveEmptyEntries)(i).Split(”)“c)(0).Trim(),
   r(“Class”).ToString.Split(vblf, StringSplitOptions.RemoveEmptyEntries)(i).Split(”)"c)(0).Trim()
   ))).ToList()
   "

3. You can change the names of header in this code as per your excel file. I have tried it with dummy names. also you can add the extra columns in the same manner as shown.

4. the dtOutput is your required data.

If this resolves your problem statement, please mark this as resolution.

Thanks,
Gaurav