Read range excel with unique column names

Help Studio
, ,
1

Hello everyone.
I have a process that require to join to datatables.

As an Input i have extract from different systems data in excel

When i use read range activity - an error occure that column names are not unique.
How to avoid this and keep headers in place as then i will need to join tables and needed columns for joining can’t be specified by index, only by name.

How i see a solution:

  • read first row
  • put it in a variable
  • check if there duplicated values
  • modify such values by adding 1,2,3 etc (like pivot table in excel)
  • read whole range with modified headers

Unfortunately i can’t implement this as i still very new in UiPath

Can you help me with this issue?

2

Hi @xagen

can you show the excel files that u need to join ?

3

excelforforum.xlsx (91.7 KB)

File like this. Data are not valid, but structure like this

4

Hi.
Data table can’t have two or more the same column names. This structure is incorrectly.
I can advise to read range without headers. Next build the temporary dt with correctly column names and in ‘for each’ activity add rows to temporary dt.

5

I understand, that to have 2 columns with equal names is cousing fault.
However i have such data in place and can’t change it on previous steps.

Datatables have a lot of rows, so to use for each activity will be very time consuming.
That’s why i think it is better to rename first row in DT and then use it.

However i don’t know how to do it technically:

*Need to extract first row
*Find duplicated column names
*Replace names by adding some digits in the end
*Re-Write first row in initial DT
*Read range with proper column names

Can you support me?

6

instead for each activity you can use
dtTemp = dt1.AsEnumerable.CopyToDatatable
to copy values to dt with new column names

7

find starter help here:
FixDuplicatedColNames.xaml (9.7 KB)

8

@ppr
Thanks! it works.

However can you explain me thist expression?

(From t In DT1SAP.Rows(0).ItemArray.Select(Function (x,i) Tuple.Create(i,x))
Group t By k=StrConv(t.Item2.toString, VbStrConv.ProperCase).Trim.Replace(" “,”") Into grp=Group
Let cg = grp.Select(Function (x,i) Tuple.Create(x.Item1, If(i = 0, k ,k & “_” & i.toString))).toList
Select cg).SelectMany(Function (x) x).OrderBy(Function (ot) ot.Item1).toList

9

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