Split the single row of excel into multiple columns

In excel sheet entire row data split into one column
Global(6,498,871),Courses(22,542),Videos(81,277),Websites(195,454),Pepole(187,395)

I need the output as below
A(Column)
Global(6,498,871)
Courses(22,542)
videos(81,277)
websites(195,454)
people(187,395)

Hi @sai3

Welcome to the community :slight_smile:

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 :slight_smile:

Good day :smiley:

hello @sai3
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)

1 Like

hi shubham,

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 :slight_smile:

  1. Split the row data by using split function on β€œ,” i.e. comma
  2. The result will come in an array
  3. Find the length of the array
  4. Do a for loop and simply use the array length as index to add data row in a single table in column wise :slight_smile:
  5. Print the data table into an excel :slight_smile:

Hope this helps…

smaple data.xlsx (8.1 KB)

Hi Shubham,

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 :slight_smile:

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.

1 Like