Extract data from "old" DB.

[Ela]

Hello,
can anybody help me in the following problem.

I have to fill the table tblITEMS in my "new" DB with the data from the tblITEMS in "old" DB, but some fieleds in this DB don't match.

In "old" DB the tblITEMS has a field Format, which is a string of few items separted by spaces or commas like this:

-------------------------------------
|ItemID |Name |Format |
|1 |first |item1 item2 item3|
|2 |second|item2,item4,item5|
|3 |third |item1 item5 |
...

--------------------------------------

In the "new" DB I have now a separate table
tblFormats:

--------------------
|FormatID|Format|
|1 |item1 |
|2 |item2 |
|3 |item3 |
|4 |item4 |
...

--------------------

In "new" DB the tblITEMS and tblFormats are conected through a junction table.

My question: how to extract the data from "old" DB (tblITEMS) to the "new" DB
so the junction table will looks like this:

-----------------------------------

|Name |Format|
|first |item1 |
|first |item2 |
|first |item3 |
|second|item2 |
|second|item4 |
|second|item5 |
...

-------------------------------------

Thanks for any advice.
E.

 

[David R]

How much data are we talking about? How many rows in the oldDB? And are they all three possible options? (space, comma, or comma+space). Oi. Isn't database normalization fun?

I would try to get them all formatted in one manner, then break them apart into query fields with InStr(). Move your query fields into an Append Query for your new table, but all into one field.

Does that help?

Good luck,
David R


[This message has been edited by David R (edited 03-19-2002).]

 

[Ela]

Hi,
I have about 1000 records (raws) in "old" DB!

Yes there are only three possible options of separating the items: space, comma or space+comma.

Could you explain how this InStr() function works?

Thank you very much David.

Regards,
E.

 

[David R]

Unfortunately your best bet is going to be reading up on some existing examples. You've got a complicated situation on your hands. Open the search link on this forum, type in 'string pars instr', and start reading. I came up with about 6 posts, most of them along the vein that you are trying to follow.

Good luck. Post back if you get stumped.

David R

 

[Ela]

Hello David,
thank you very much help.

I have used the InStr(), Left() and Mid()functions to extract the data form old DB.
I think that I have solved my problem.
I extract each first item and put it to a new junction table:
----------------
|first |item1 |
|second|item2 |
----------------

Then to next junction table I put the second item:
----------------
|first |item2 |
|second|item4 |
---------------
and so on. Now all items are in separate rows. So I get few junction tables. I put them together using the union query.
It works, but I now that this is not very fine method to do this.

I will read the old posts about 'parse string' problems.

Thanks again.

Regards,
E.