How to split the single column into multiple column

Help
, ,
1

Hi,

I have the excel with single column as per below.

Apr
May
June
0
23
0
77
0
6
0
-12
0
1
0
8

I want this to to be three separate columns for Apr,May and June and the values also should come into corresponding month instead of single column,
Sample Output should be like this in excel .Please suggest on how to achieve this.

Apr May June
0 23 0
77 0 6
0 -12 0
1 0 8

2

So for each row - you know your count is 3
So you can do a while loop to extract each item - you will need to create a new datatable from the first 3 items ( to create your headers)

then after that create your rows

3

As @TimK mentioned… Just adding a few steps in the event you are new to UiPath

Use an Excel Application Scope, linked to your excel file you with to read
Within the Excel Application Scope, use a Read Range activity; Save that to a DT_SingleColumn

Assign rowCount to DT_SingleColumn.Rows.Count
Assign count to 0
Use a Build Data Table activity, DT_OutTable, with 3 columns set up

Use a While Loop with a condition of Count < rowCount
Within the Loop{
Add Data Row - Set the ArrayRow to (DT_SingleColumn.Rows(count).Item(0).ToString, DT_SingleColumn.Rows(count + 1).Item(0).ToString, DT_SingleColumn.Rows(count + 1).Item(0).ToString)

Assign count = count + 1
}

Use an Excel Application Scope set to your output Excel File.
Use a Write Range Activity to write DT_OutTable to an Excel File

2 Likes
4

Maybe, if possible, look into how you got that excel, is it from a system, is it with xslx extension or csv?

5

@asatheeshk21
have a look here:

its a similar situation just change the segment size from 5 to 3

6

Got an exception when tried this. Exception happened in Add data row inside while condition

system.indexoutofrangeexception

7

I got this excel format by means of below steps.

  1. Attach browser
  2. Using GetFull text,Scrap the table format of 3columns and store into string variable “sInp”.
  3. Assign rowcount=1
    4.Use Excel application scope to write into excel
  4. Generate data table with Column seaprators as “” and Input as “sInp” and output data table as “dtInp”
  5. Assign rowcount=rowcount+1
  6. Write range “A”+rowcount.Tostring and binded with that data table"dtInp"

With this it got stored into single column in excel. Please suggest

8

I checked this, You have mentioned the values of a,b,c,d,e in build data table column1. But in my case the values are dynamic it will not be hardcoded, so i have removed that and ran then it is not working and getting an exception as "The source contain no data rows Exception Type:“System.InvalidOperationException”. In that case how to write it. Please suggest.

9

@asatheeshk21
When WE do preimplementations Here WE are using build datatables for Dummy values. Sure in the later Implementation this ist replaced by User and brought to dynamics e.g. with an Excel Read in.

About your exception: Just provide the xaml and some Sample Data, so WE can have a Look on this.

10

You will need to assign count variable to 0 before the loop somewhere :slight_smile:

11

You will need to increment your counter by 3 instead of one if just using while loop - also the last DT_SingleColumn.Rows(count + 1).Item(0).ToString should be DT_SingleColumn.Rows(count + 2).Item(0).ToString

Also you need to use {} curly brackets for the array:

 {DT_SingleColumn.Rows(count).Item(0).ToString, DT_SingleColumn.Rows(count + 
 1).Item(0).ToString, DT_SingleColumn.Rows(count + 2).Item(0).ToString}
12

I think would be best to work on your scraping so you can get that table directly into the format you want, right now you are doing too many steps to achieve what you want…

14

I have tried all the ways of scraping mechanism. It copies in the single column only. If I’m not in the right way of scraping as mentioned in the steps. Can you pls suggest how it can be achieved.

15

Hi,

Requirement got changed. Can you please see this. I have opened one more forum. It will be helpful if could share ur ideas .

16

Requirement got changed, I have opened one more forum it will be helpful if you could share the idea/ xaml for that pls.

This is prioirty for me. Kindly help on this