Importing Denormalized Data

[kettle_anonymous]

I'm creating a POC to import a delimited file from a vendor into my database. The data I need to import is denormalized into 1 delimited file and I need to normalize it into mutliple tables. For example, the input data looks like this:

bowls, cereal
bowls, soup
silverware, forks
silverware, spoons
silverware, knives

I would like to import this into two tables that look like this:
dish_type
* dish_type_id
- dish_type_name

dish
* dish_id
- dish_type_id
- dish_name

So the process is this:
1. Read Record
2. Lookup dish_type_id by dish_type_name
3. If not exists, add new dish_type
4. Add new dish

I've played around with a number of scenarios (Lookups, Insert/Update, Filters), but can't seem to figure it out. If I do the insert/update, how do I get the id back out? Do I need to do a subsequent lookup? How do I access the fields of the current row from the input file after I've done the insert/update to the dish_type table to do the insert/update on the dish table?

If anyone has a similar example that they can share, I'd appreciate it.

Thanks in advance,

Chris

[MattCasters]

Hi Chris,

We had this question before on the forum a couple of entries back: you can use the Combination Lookup step. This will store unique occurences of dish_type_name and add an ID.

HTH,

Matt

[kettle_anonymous]

Thanks Matt -- that's what I was looking for. I'm actually importing 5.8 million rows into a 4 table hierarchy. Does the combination lookup do any caching of lookup values?

Thanks again for your help.

-Chris

[MattCasters]

No, not yet. In this case we could implement it.
If you really need it, file a feature request for it under the trackers.

Thanks,

Matt