# Dedup - From excel to SQL Server

**URL:** <https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956>\
**Category:** CloverDX Platform\
**Created:** [October 30, 2011, 12:00am UTC](https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956 "2011-10-30T00:00:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![lorecartagena723](https://avatars.discourse-cdn.com/v4/letter/l/f4b2a3/32.png) [@lorecartagena723](https://forum.cloverdx.com/u/lorecartagena723)\
**Post date:** [October 30, 2011, 12:00am UTC](https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956/1 "2011-10-30T00:00:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [October 31, 2011, 9:48am UTC](https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956/2 "2011-10-31T09:48:28Z")

</div>

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:

```auto
//#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;
}

```

---

<div class="post-metadata">

**Author:** ![lorecartagena723](https://avatars.discourse-cdn.com/v4/letter/l/f4b2a3/32.png) [@lorecartagena723](https://forum.cloverdx.com/u/lorecartagena723)\
**Post date:** [October 31, 2011, 7:39pm UTC](https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956/3 "2011-10-31T19:39:45Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![mzatopek](https://avatars.discourse-cdn.com/v4/letter/m/b5e925/32.png) [@mzatopek](https://forum.cloverdx.com/u/mzatopek)\
**Post date:** [November 7, 2011, 3:53pm UTC](https://forum.cloverdx.com/t/dedup-from-excel-to-sql-server/956/4 "2011-11-07T15:53:49Z")

</div>

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](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](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.