ETL and Data Integration tools - Modern ETL software for everyone

I seem to have opposite problem than many: one input file to many tables.

Have one "flat" file CSV to upload to "normalized" DB....need to parse the input file and appropriately populate the parent and child tables in a way that keeps the relational data integrity. Tables from a 5 deep straight heirarchy for which I get data in a single denormalized flat file with the successive heirarcy chain just appearing as the text "name" field for that level (not the DB ID key field number).

Can this product handle anything like that...or do you know of any product that does?

Thanks for answering...purchase decision will follow.

Steve

Steve
Can you email us example of your data please.
Mike

I have hit on something that seems to work, though not quite visual or elegant.

I upload the flat file straight into a "flat" table. Then execute a series of SQL inserts with subselects to get the key from the parent table, traversing & populating the heirarchy from top to bottom.

The biggest trick was that some of the text fields in the flat CSV file are Memo/longvarchar fields...which messed up select distinct and join operations. I got around that by concatenating the results from subselects that retrieve 255 byte chunks of the field.

Here is example of some of the SQL for the bottom 3 tables in the heirarchy:

Here the table above (BuildingBlock) is already populated, so I have to get the primary key to use as foreign key in table Initiative. Scorecard is the uploaded flat file.
INSERT INTO Initiative ( strGoal, intPriority, intObjectiveID )
SELECT DISTINCT Scorecard.Initiative, Scorecard.[Init Priority], BuildingBlock.intObjectiveID
FROM Scorecard INNER JOIN BuildingBlock ON Scorecard.[Building Block]=BuildingBlock.strObjective;


Here I need the distinct values of the memo field Scorecard.Objective, and the auto-generated primary key from the Iniative table populated above.
INSERT INTO Objective ( strApproach, intPriority, intGoalID )
SELECT obj1&obj2, objpriority, goalid
FROM (SELECT DISTINCT mid(Scorecard.Objective,1,255) as obj1, mid(Scorecard.Objective,256,255) as obj2 , Scorecard.[Obj Priority] as objpriority, Initiative.intGoalID as goalid
FROM Scorecard INNER JOIN Initiative ON Scorecard.Initiative=Initiative.strGoal);

Finally, I have to match the corresponding memo text fields from the flat table and the Objective table (loaded above) in order to get the foreign key for the Goal table.
INSERT INTO Goal ( strNewGoal, goalPriority, intApproachID )
SELECT Scorecard.Goal, Scorecard.[Goal Priority], Objective.intApproachID
FROM Scorecard INNER JOIN Objective ON (mid(Scorecard.Objective,256,255)=mid(Objective.strApproach,256,255)) AND (mid(Scorecard.Objective,1,255)=mid(Objective.strApproach,1,255));

Sorry for such a long post but wanted you to see what I had done since you were kind enough to ask for a sample....and thanks for your willingness to look into this....if your tool could make any of this simpler that would be way cool.

Thanks,
Steve

I think I found slightly better solution.
I used the example you send to me
I loaded file into flat table as you did the only difference it got ID field which is integer PK.

Your file presordted to it makes is easier to work with.

insert into level1table
select min(id),level1name from data group by level1name

insert into level2table
select t1.id,t2.id,level2name from
(select min(id) id ,level1name,level2name from data group by level1name, level2name) t1,
(select min(id) id ,level1name from data group by level1name) t2
where t1.level1name=t2.level1name

and so on

another solution would be using instead of trigger but i think it will make it much more complicated