Dedup - From excel to SQL Server

lorecartagena723

• October 30, 2011 00:00

• Answered

Follow

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

  • October 31, 2011 09:48

  0

  Comment actions Permalink

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

• 

  lorecartagena723

  • October 31, 2011 19:39

  0

  Comment actions Permalink

  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

  • November 07, 2011 15:53

  0

  Comment actions Permalink

  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.

Please sign in to leave a comment.