Dedup - From excel to SQL Server

CloverDX Platform
1

I have to migrate a DB from excel to SQL Server. I have done it with the XLSDataReader, Reformat, DBOutputTable.
But now, I need to select only a column of the excel page and I need to take out the repeted values of that column.

For example, I have

id | Name
1 | Lorena
2 | Lorena
3 | Adriana
4 | Adriana

And I need to get:

id| Name
1| Lorena
2 | Adriana

I now that the dedup tool can do this, but I dont now how to connect the XLSDataReader, Reformat, DBOutputTable and dedup, and which metadata to select in each case.

2

Hello,
it depends on your transformation. If you need to paste only these 2 columns to the database, use XLSDataReader, Dedup and DBOutputTable:
XLSDataReader – two fields mapped by fieldMapping in XLSDataReder - → Dedup → DBOutputTable
if you need more complicated transformation, you can put the Dedup before the Reformat and cut there the rest of the fields or you can de-duplicate the records directly in Reformat:

//#CTL2
string key = "";
// Transforms input record into output record.
function integer transform() {
	
	if (key != $Name) {
		key = $Name;
		myTransformation();
		return ALL;
	} 
	
	return SKIP;	
}

function void myTransformation(){
	//do all needed transformation, eg. cut all fields except the 2 first:
	$Id = $Id;
	$Name = $Name;
}
3

Thank you,
I have already done it and it worked perfect.

Now I have another problem with the relationships between the two tables,

For example I have:

TABLE MACHINE
Field | Type
Machine | string
idProduct | int
idPersonEncharged | int

TABLE PRODUCT
Field | Type
ID | NameProduct
1 | Fish
2 | Meat
3 | Rice

TABLE PERSONENCHARGED
1 | Lorena
2 | Adriana

In excel I have the data in the table Machine with the name of the product instead of the id of the product
Like:

Machine1 | Fish | Lorena
Machine2 | Meat | Adriana

Now I need to relate the three tables and change the name of the product to the id of the prodcut that is on the other table. Is there any tool in clover that can do this, or I have to do it manual.

To get this:
NameMachine | idProduct | IdPersonEncharged
Machine1 | 1 | 1
Machine2 | 2 | 2

Thank you,

4

There is several ways to reach your requirements. The probably the easiest way is to use our lookup table functionality http://www.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/database-lookup-table.html

Directly in your reformat component you can convert name of product to identifier by something similar:

lookup(myLookupTableId).get($productName).ID

further details are available at http://www.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/lookup-table-functions-ctl2.html

You can also read something about DBJoin, LookupJoin components, which provide next possible approaches.

Let me know if you need more details.