In excel sheet entire row data split into one column
I need the output as below
Welcome to the community
For this you can use the split function, and then pass it to a single column using add to datarow using the for loop.
Do let me know in case you need more help
you can use this linq code here
(From coll In DT.Columns.Cast(of DataColumn) Let x=String.Join(",",(from p in DT.AsEnumerable().Select(Function(r) r(coll.ColumnName)).ToArray)) Select DT.DefaultView.ToTable(True,"ColumnName").Clone().Rows.Add(x.ToString.Split("|"c))).ToArray.copytodatatable
this will split your rows and than convert it in to column
Just make sure you put the correct columnName in the code
Check this workflow for better understanding
Linq Row to Col.zip (13.6 KB)
But in single row i have large amount of data how can i put all data in array row.
Let me try breaking up the whole scenario
- Split the row data by using split function on “,” i.e. comma
- The result will come in an array
- Find the length of the array
- Do a for loop and simply use the array length as index to add data row in a single table in column wise
- Print the data table into an excel
Hope this helps…
smaple data.xlsx (8.1 KB)
Can you please find the excel attachment I tried but i am unable to achieve desired output. Can you split the single row entire one single column.
am getting the data like that
Sorry for late reply, try this out
You already have solution above using split function. The only thing you have to take care is the separator. The comma “,” will not work here as the values inside parenthesis () already have comma.
Use following trick.
Replace “),” with “);” and then use semi colon “;” as separator in Split function.