Transposing Excel Data Into a DataTable

Help
1

Hi

I’m incredibly new to RPA and am struggling to transpose data within my Excel spreadsheet.
Unfortunately my set of data is not organised in the regular CSV/Table format. My column data is set out vertically with the next set of data directly below the first set. I will provide an example of how my data looks. uipathforum

I’m able to transpose one set of data into the correct format using Excel, but attempting to transpose it all at once just recognizes all of my sets as new headers.

Is there a way to automate the process of transposing all of this data into my desired format?

Thanks

2

Hi,

  • use ReadRange activity in cycle and in each loop read one data set (ID,name, day, month) into a temp datatable
  • calculate “range” dynamically, e.g. varRange = “A” + cstr(row) + “:B” + cstr(row+3)
  • in each loop increment row by 4 to move to next data set, e.g. row = row + 4
  • append individual data sets from temp datatable to final datatable
  • finish loop when reach end - temp datatable is empty

Intentionally do not post complete workflow here as it would not help you to grow :smiley:

Cheers

3

@Jeavo

Not sure if this will help in your case but just have a look into it

Transpose.xaml (14.7 KB)

Can modify based upon your requirement.

4

Here is another way to @J0ska but in a similar fashion -

If you know the structure - i.e. each set of data has 4 rows and it the same through out, you can read in the whole excel file to a datatable.

You can then build a new datatable with your correct headings (ID / Name / Day / Month)

You can then go through the datatable to add your data (1 / Ryan / Mon / Jan) etc. using the add datarow inside a while loop

You can access the data into an array format (Datatable.Rows(x).Item(1).Tostring)

  • Assign - ID = (Datatable.Rows(x).Item(1).Tostring)
  • Assign - Name = (Datatable.Rows(x+1).Item(1).Tostring)
  • Assign - Day= (Datatable.Rows(x+2).Item(1).Tostring)
  • Assign - Month= (Datatable.Rows(x+3).Item(1).Tostring)
  • Add Datarow - {ID,Name,Day,Month}
  • You will then need to increment x by 4.
1 Like
5

Hi all

Thanks so much for your replies. I’m quite new to programming too so I’m rather slow at beginning to understand a lot of this.

I very much understand the logic of assigning the rows to variables but get confused with the (Datatable.Rows(x).Item(1).Tostring) part.

Now I’m assuming that the Datatable.Rows(x) part is where I assign ID to x and Name to x+1 right?. I don’t understand the .Item(1) part either.

Could you explain it in layman terms for me?

Thanks

6

@J0ska @TimK

7

@Jeavo
Please Copy all these files into a folder and run the Main xaml. You will get the Output. I tried to zip the folder and upload but it is showing some error.
It will dynamically transpose the Input Excel. You need to pass only Path of Input Excel Sheet.
I am using two queries to transpose here.

  LstdtSplitData= (From p In dtInput.Select().Take(Convert.ToInt32(dtInput.Rows.Count/dtTranspose.Columns.Count))
Select dtInput.Select().Skip(dtInput.Rows.IndexOf(p)*dtTranspose.Columns.Count).Take(4).CopyToDataTable).ToList

argdtTranspose = (From p In LstdtSplitData
Let x=String.Join(",",From q In p.Select
                                  Select Convert.ToString(q(1)))
Select dtTranspose.Clone.Rows.Add(x.Split({","},stringSplitOptions.None))).CopyToDataTable

You will get the output of Transposed DataTable.

Main.xaml (4.9 KB)
TransposeExcel.xaml (9.7 KB)
project.json (641 Bytes)
Transpose.xlsx (8.9 KB)

Please Copy all these files into a folder and run the Main xaml. You will get the Output. I tried to zip the folder and upload but it is showing some error.

Regards,
Mahesh

4 Likes
8

So the. Item(1) refers to the second column of the datatable. Where you read in the excel without headers it is indexed from 0 (Column A)

The x is the row index in accordance to the datatable so row 0 is row 1 in the excel spreadsheet

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.