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
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
-
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. -
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 -
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
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, -
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 -
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.
Please sign in to leave a comment.
Comments 5