# Data import to relational tables

**URL:** <https://forum.cloverdx.com/t/data-import-to-relational-tables/1402>\
**Category:** CloverDX Platform\
**Created:** [April 30, 2014, 12:00am UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402 "2014-04-30T00:00:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![svsl11](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@svsl11](https://forum.cloverdx.com/u/svsl11)\
**Post date:** [April 30, 2014, 12:00am UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/1 "2014-04-30T00:00:00Z")

</div>

Hi,

I need to import data from a single Excel table to a database (multiple tables with relationships). Can I create relational tables in a database and import data to them using Clover ETL?

Thanks,  
Vlad

---

<div class="post-metadata">

**Author:** ![slechtaj](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@slechtaj](https://forum.cloverdx.com/u/slechtaj)\
**Post date:** [May 2, 2014, 12:39pm UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/2 "2014-05-02T12:39:39Z")

</div>

Hi Vlad,

Yes, you can do this with CloverETL for sure. There are multiple ways to reach this goal. In general, for creating tables you may use DBExecute component and for insert/update statements you can use DBOutputTable component. With CloverETL you can do even a lot more than just this – for example you may validate the data, join them with other different sources etc. Especially validation might be very helpful when you migrating data from Excel spreadsheets to DB, since Excel sheets usually contain a lot of duplicate or invalid records that you would not like to insert to your database.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![svsl11](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@svsl11](https://forum.cloverdx.com/u/svsl11)\
**Post date:** [May 2, 2014, 8:05pm UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/3 "2014-05-02T20:05:47Z")

</div>

Thanks, Jan.

Can I split a source table in order to load data to multiple destination tables with relationships? The ETL will have to generate unique id numbers for linking these tables.  
While importing to non-empty table, can I prevent import of rows if their records already exist in the destination table?

Vlad

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [May 7, 2014, 2:08pm UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/4 "2014-05-07T14:08:29Z")

</div>

Hi Vlad,

If you mean vertical splitting, i.e. field1 goes to table A and field2 goes to table B, it can be done in Reformat:

$out.0.field1 = $in.0.field1;  
$out.1.field2 = $in.0.field2;

For non-empty tables, we have a component named DataIntersection which can compare records from two different sources (e.g. csv file and DB table) based on a chosen key.

[http://doc.cloveretl.com/documentation/ … ction.html](http://doc.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/dataintersection.html)

If two records match (i.e. the same record is in DB and the in CSV file in this case), the record in the DB can be for example updated with newer values. If a record is present only in csv and not in DB, an INSERT query can be performed. And if a record is only in DB and not in CSV, you can either delete it from DB or do nothing.

Regards,

---

<div class="post-metadata">

**Author:** ![svsl11](https://avatars.discourse-cdn.com/v4/letter/s/c77e96/32.png) [@svsl11](https://forum.cloverdx.com/u/svsl11)\
**Post date:** [May 7, 2014, 6:54pm UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/5 "2014-05-07T18:54:10Z")

</div>

Thanks, Lubos.

I need to import a plain Excel table like this:

MsCity MsDecim1 Det1dow Det2nPers  
1 2.31 1 1  
1 2.31 2 2  
1 2.31 3 5  
1 2.31 4 11  
2 11.01 5 22  
2 11.01 7 2  
2 11.01 2 31  
2 11.01 3 9  
2 11.01 1 5

to two relational DB tables:

Id MsCity MsDecim1  
num1 1 2.31  
num2 2 11.01

and

Id Det1dow Det2nPers  
num1 1 1  
num1 2 2  
num1 3 5  
num1 4 11  
num2 5 22  
num2 7 2  
num2 2 31  
num2 3 9  
num2 1 5

The unique numbers num1 and num2 should be generated by ETL during an import to ensure proper linking between two DB tables.

How can I do it?

Thanks,  
Vlad

---

<div class="post-metadata">

**Author:** ![imriskal](https://avatars.discourse-cdn.com/v4/letter/i/b38774/32.png) [@imriskal](https://forum.cloverdx.com/u/imriskal)\
**Post date:** [May 13, 2014, 9:18am UTC](https://forum.cloverdx.com/t/data-import-to-relational-tables/1402/6 "2014-05-13T09:18:24Z")

</div>

I think the proper linking is already ensured by MsCity field and you do not need any artificial field for this purpose. But if you insist on it, the solution is Reformat component with the following CTL:

function integer transform() {

$out.0.MsCity = $in.0.MsCity;  
$out.0.MsDecim1 = $in.0.MsDecim1;

$out.1.MsCity = $in.0.MsCity;  
$out.1.Det1dow = $in.0.Det1dow;  
$out.1.Det2nPers = $in.0.Det2nPers;

if ($in.0.MsCity == 1) {  
$out.0.Id = “num1”;  
$out.1.Id = “num1”;  
} else if ($in.0.MsCity == 2) {  
$out.0.Id = “num2”;  
$out.1.Id = “num2”;  
}

return ALL;  
}

Then use Dedup component on the first output stream and the task is done.
