I have an excel file with this type of data, and I want to split all these in rows each cell having only one value.
Like in first cell I have two values in one cell . I want to put this in two different cells .
How can I do this ?
Loop through the data table and get the value. Then split the values by using Environment.NewLine.ToCharArray as
row(“column name”).tostring.split(Environment.NewLine.ToCharArray)(0) is the first value and
row(“column name”).tostring.split(Environment.NewLine.ToCharArray)(1) will give you the second value.
Use write cell activity to write in to a new cell 
1 Like
hello @shikharno.7
Arr1 = cellValue.split(cchar(Environment.newline))
to access the first use Arr1(0)
for second value use Arr1(1)
Regards
ajay
if the datatable name is “dtcol4” what will be assigned in arr1 ?
You can write directly or assign to a variable and write that variable to the excel … either way is fine
1 Like
This is the value i am assigning.
value1 is in string format.
I used a message box to see the value ,
only the first two cells are being outputted after that “out of bound array exception is showing”
Yes, your first two rows have the second value to split and the remaining rows are having only one value. So you need to give a condition there or surround the split assign activity with try catch and then check @shikharno.7
As your first and second rows have the data in two lines and we are split the string with new line everytime, the first two worked fine. But the third one won’t have the data split but the code trying to split with new line. So, it is erroring out.
We need to give the condition as , if the string contains two lines, then split. Instead use Try catch activity and assign variables inside the try catch. So, if the value has two lines, it will split … otherwise it will simply skip @shikharno.7
1 Like
Thank you
Hi
–hope these steps could help you resolve this
–use a excel application scope and pass the file path as input
–use read range activity and get the output with a datatable variable type named outdt
–use a build datatable activity and create a same datatable column structure of that in excel and get the output with a variable of type datatable named Finaldt
–use a for each row loop and pass the above outdt as input
–inside the loop use a if condition and mention the condition like this
row(“yourcolumnname”).Split(Environment.Newline.ToArray()).Count > 1
if the above condition passes it will go to THEN part where we can mention like this
–within the THEN part use a assign activity like this
out_valuearray = row(“yourcolumnname”).ToString.Split(Environment.Newline.ToArray())
–then use a add data row activity and mention the datatable as Finaldt and ArrayRow property as {row(0).tostring, row(1).tostring, out_valuearray(0).tostring.trim, row(3).tostring,…row(n).tostring}
here i though that column is in third position, we can change the position of the out_valuearray(0).ToString at any position in the ArrayRow property as per the column order in the datatable
–now again use the same add data row activity and mention like the same but with changed index number like this to include the second line data as a new row
{row(0).tostring, row(1).tostring, out_valuearray(0).tostring.trim, row(3).tostring,…row(n).tostring}
–now if the condition fails it will go to ELSE part where we can simply use a add data row activity mentioning the finaldt as input for datatable and arrayrow property with all the rows as values
like this
{row(0).tostring,row(1).tostring, row(2).tostring,…row(n).tostring}
simple isnt it
hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @shikharno.7
1 Like
I have a requirement to make this dynamic. In the snapshot 2 values are there in a cell , but it can be 3 or 4 or more. In that case what robust changes should i make ?
Thank you very much for yesterday’s help.
Try this @shikharno.7,
Use a variable of type string array and split the text with Environment.NewLine.Tochararray .
It will store number of values you have in a string array. Then use the length of the string to write to new cells each time. SO that if the length is one, you can write in only one cell or if length is 10, you can write to 10 new cells
Could you upload a sample xaml ?
Anil_G
Ajay_Mishra
pravallikapaluri
postwick
AJ_Ask
Anonymouss
vignesh.maruthappan
mkankatala
mrohan.senapaty
ATHEEQ_UR-RAHMAN