Merge two database with different Column Name

Hi everyone,

In need you help with that.

I have two databases with 42 column names and need merge to another database with the same 42 columns but with different name. How do that without chanches the name to 42 column?

DT1

Column0 | Column1 | column3 | column… | column42
1234 example 345 final1
1234 example 567 final3
1234 example 789 final5
12345 example 7890 final9
12345 example 890 final10

DT2

ColumnA | ColumnB | columnC | column… | columnAP
4567 example 345 final1
4567 example 567 final3
4567 example 789 final5
45678 example 7890 final9
45678 example 890 final10

DT Result

Column0 | Column1 | column3 | column… | column42
1234 example 345 final1
1234 example 567 final3
1234 example 789 final5
12345 example 7890 final9
12345 example 890 final10
4567 example 345 final1
4567 example 567 final3
4567 example 789 final5
45678 example 7890 final9
45678 example 890 final10

Thanks!

1 Like

Hey @jvillalba9221

Kindly use Merge Data Table activity

Thanks
#nK

Hi @Nithinkrishna but activitie Merge only work with same columns names.

1 Like

HI @jvillalba9221

have a look on the thread

Regards
Gokul

Using merge datatable would result to columnstructure of dt1 cols count + dt2 cols count when MissingSchemaAction is set to Add

renaming we could do dynamicly within a loop

without renaming give a try on following (Assumption both datatables are of same structure with different col names, but same meaning on the columns)

dtAddResult | DataType: DataTable =

(From d in dt2.AsEnumerable
Select r = dt1.Rows.Add(d.ItemArray)).CopyToDataTable

then refer again to dt1 for all dt1 and dt2 rows

Hey @jvillalba9221

You can simply remove the columns row.

Are you getting these data tables from Excel ?

Thanks
#nK

Thank @Gokul001 but its solution is similar to use Join, If I do that generate 84 columns. Only need 42 columns that are the same that de other database only with different name.

Thanks @ppr, however, databases have 800000 rows each one, but yeas both datatables are of same structure with different col names and same amount columns

is doing this:
grafik
grafik

After the merge LINQ:
grafik

Find starter help here:
MergeDT_SameCStructureDifferenCNames.xaml (8.3 KB)

@Nithinkrishna use excel activities generate error of memory, because its so huge rows in datatable.

if this is the concern, then a dynamicly automated renaming of 48 col names should not be the blocker f merge datatable activity usage is prefererred

This is most likely caused by an excel configuration of your R1C1 setting on one or the other. If you wish to standardize the column headings on both you will need to ensure these settings match…Excel/File tab/Options/ExcelOptions/Formulas/ then make sure the R1C1 check box is either checked on both data sources or unchecked on both. This should then standardize your column headings and you can use the file merge

@ppr thanks! this script LINQ works with small tables, when I implement with real data not work. I have DT1 with 200000 rows and dt2 with 800000 rows

image

I supouse that i need to change the 42 column names.

@Chris_Bolin thanks for you support but the datatable are without R1C1 I divide database Excel in diferent size datatable for i can to use read range and generate diferente datatable. The idea is after merge this datatables.

just one by one:

not work is NOT working for us. Just tell us in detail what is not working (exception, Performance (do not expect an execution time of 0 miliseconds or less), )

we just moving dt2. In prodction projects we handled rows within range of multiple Mio rows and it was performing in acceptable range (ok, its vage and relative, but we allowed executions <= 20 seconds)

you can check when removing the for only psychological reasons added CopyToDataTable

iCount =

(From d in dt2.AsEnumerable
Select r = dt1.Rows.Add(d.ItemArray)).Count

How long is the execution of the LINQ?

@ppr I dont know why dont work, when I implement .Copydatatable() or . Count() his execution time is about 5 min. But show Error always.

Some idea why occurs that?

image

if you could transport this information to a sql database, it’s better work with queries than database inside Uipath. or its the same and not matter

would it be possible that you can share the xaml or screenshot on relevant parts (the way form whee dt1/dt2 are coming till including the merge?

What are you doing with the merged data?

off course!

its simple

image

I have a Excel, and to use read range 5 times:

  1. “A1:AP200000” with addheaders
  2. “A200001:AP400000” without addheaders
  3. “A400001:AP600000” without addheaders
  4. “A600001:AP800000” without addheaders
  5. “A8000001” without addheaders

i do that because if i read entire range “A1” show error, the same error mentioned before.

after

image

  1. Merge datatables “without addheaders”
  2. finally your LINQ script. The idea merge datatables with addheaders and without addheaders.

I use de datatable final, to filter with LINQ by client, its a datatable with 19000 clients.

Then try this… read all of your inputs without headers. Then do an Output Datatable Activity with a string variable for each read. Then do a quick log message…information… and pass the string of your Output Dts. If I’m correct UiPath will default with Column1; Column2;…etc. one note… that size of data will cause latency in your automaton

@jvillalba9221

Can you please try with Workbook Read Range activity to read the data from excel file and pass the range as “A1” and check it once.