Dedup - From excel to SQL Server

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

lorecartagena723
Posts: 2
Joined: Sun Oct 30, 2011 4:31 pm

Dedup - From excel to SQL Server

Postby lorecartagena723 » Sun Oct 30, 2011 4:39 pm

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.

avackova
Posts: 841
Joined: Fri Jul 20, 2007 9:28 am

Re: Dedup - From excel to SQL Server

Postby avackova » Mon Oct 31, 2011 10:48 am

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:

Code: Select all

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

Agata Vackova
Javlin a.s.
[email protected]

lorecartagena723
Posts: 2
Joined: Sun Oct 30, 2011 4:31 pm

Re: Dedup - From excel to SQL Server

Postby lorecartagena723 » Mon Oct 31, 2011 8:39 pm

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,

mzatopek
Posts: 76
Joined: Fri May 11, 2007 9:49 am

Re: Dedup - From excel to SQL Server

Postby mzatopek » Mon Nov 07, 2011 4:53 pm

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.
Martin Zatopek
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com


cron