Split single column into multiple columns

Help Studio
1

I have an excel sheet that is not formatted correctly. In column A, it has all the info into one column:

row (1) Cust # Cust Name Code Acc #
row (2) 1 Test 0001 5000
row (3) 2 Test2 0002 50001

I just need to seperate the column headers into different columns… Cust # to be in column A, cust Name column B, etc…

2

@mkkajtez
Read your excel file and use for each datarow with input as datatable obtained after read excel.
While processing first row split the value with space and create the headers in the new datatable with array obtained and for rest of the rows after splitting the value and add the array as datarow.

3

Hey, how would you perform “While processing first row split the value with space and create the headers in the new datatable with array obtained and for rest of the rows after splitting the value and add the array as datarow.” ?
What functions would you be using?

I have a similar scenario and I would need to divide the cell based on “_”
Sample Cell Value = “ABC_1234_KJH_3_UIO” and would require to move it to 5 different columns.

4

@Meyammai

  1. split first row of required column value wrt to separator, with string split function providing separator you can split the string and create headers in the datatable for each value in array
  2. from next row onwards split required column value wrt to separator, with string split function providing separator you can split the string and add array as datarow using add datarow activity and this step is repeated for next rows in datatable
5

Hi @Meyammai

Could you please share the sample input and output excel files, so that we can understand your requirement correctly.

Thanks.

6

Hey,

Thank you for your reply, but I managed to resolve it by using the activity → Generate Data Table :slight_smile:

3 Likes
7

I have a similar problem. I have large CSV file which I am able to quickly convert to excel using “Copy Sheet”. However in the excel I now have all data in single row. So I need to do the same thing you needed to.

If you could please share the configuration of Generate Data Table activity you used to solve your problem.

8

Hi all,

I am also facing the same issue.

Can any one explain the solution for this…