The challenge is that there are multiple Doc No in one Assignment in Excel A.
When it happens, we need to split these multiple Doc No to different cells in the same column one by one
On the other hand, the other value should be only typed into first row of these different cells under the same Assignment. The rest rows of this assignment(which has multiple Docs No.) must be empty
May I know how to make it happen? I put 2 excel files in attachment for your reference. A.xlsx (10.8 KB) B.xlsx (10.9 KB)
In order to map the data you would be making use of âCopy/Paste Rangeâ Activity.
I would Suggest you to use read cell and Write cell.
Read Cell, To read the doc number from the cell probably in loop, The output will be stored in a String Variable(TestString).
Have a String array and split the string variable (TestString) using the split method based on newline in case if you have the data like
123465678
908765342
After splitting this would become {â123465678â,â908765342â}
Post the initialization of the string array in the above method you can check the length of the array if it is more than 1 - loop the array to write data in the first row of the column in corresponding sheet using the âWrite Rangeâ activity.
Thank you for your feedback. Could you help upload a xaml file for me to check when you have time? I am a beginner and not quite familiar with split and array
And I donât know how to put the rest value only in the 1st row of these invoice number
I have some questions while I study your workflow:
Why count=0 means there are multiple invoice number in one cell?
The final output in Excel B with different column name in Excel. I try to change it in multiple assign but it just turn out error. May I know how to change the column name in the final output?
You are creating Excel B or its a predefined template with the Column Name, you just need to add the details from Excel A to Excel B by mapping with column name ?
Count=0: For checking the no. of invoice in one cell and to identify that 1st row is completed, So for next row only Invoice number i need to add and other will be empty.
One solution giving with Linq Query, you may try once. am splitting the rows using it.
(From row In dtInput.AsEnumerable
Let arrValue = row("Invoice Number").ToString.Split(Environment.NewLine.ToCharArray,StringSplitOptions.RemoveEmptyEntries).ToList
From value In arrValue
Let index = arrValue.IndexOf(value)
Let replacedRow = row.ItemArray.TakeWhile(Function(item) Not item.ToString.Contains(value)).Concat({value}).Concat(row.ItemArray.SkipWhile(Function(item) Not item.ToString.Contains(value)).Skip(1))
Let newRow = row.ItemArray.Select(Function(item) If( item.ToString.Contains(value),value,"" ))
Select dtOutput.Rows.Add(If(index=0, replacedRow,newRow).ToArray)
).CopyToDataTable
