Add Data row using split and for each

Help
1

I have an Excel sheet with a number of columns. For one of the columns I would like to split the data and write each split item into a new sheet, with an item on a new row, so there would only be one column.

I try the following but cant get it to work:

  1. Read Range to get data in the worksheet into a DataTable
  2. For Each row in DataTable
    2.1 WriteLine to output for the column to split so I can see the data
    2.2 Condition if column contains ; it shoud be splitted
    2.2.1 Split the column data into an array String, variable stringSplitted
    2.2.2 For Each item in stringSplitted
    2.2.2.1 WriteLine item.ToSting() to output to see each item
    2.2.2.2 Add Data Row item - !!! but this I cant get to work.

How can I item to the datatable with Add Data Row ?

2
  1. Create a new data table with necessary colums
  2. In Add Data Row Item activity, use array parameter {“xyz”, 12, 13, 14, split[0], split[1]} within for each loop.
  3. Write the data table outside for each loop using Write Range activity.
3

Thanks for your reply.

I am not sure about what you mean with “2. In Add Data Row Item activity…”

Found the answer: In Add Data Row the Input field ArrayRow it should be {item}

4

@kilarsen

Let us take you are having a datatable dta
You take new data table with out the column which you want to split. let us take it as dtb
dtc=dtb.Clone()
Add new Columns to the dataTable dtc by using Add DataColumn Activity based on the values.

dtc= (From p In dtb.Select
        Let x= string.Join(",",p.ItemArray)+","+string.Join(",",dta(dta.Rows.IndexOf(p))("ColumnName").ToString.Split({"Split Parameter"},StringSplitOptions.None))
      Select dtc.Rows.Add(x.Split({","},stringSplitOptions.None))).ToArray.CopyToDataTable

Column Name- Name of the column whose value you have to split.
Split Parameter- Parameter based on which you have to split the value

Regards,
Mahesh