troubleshooting Question

Need to parse one column into multiple fields..

Avatar of bganoush
bganoush asked on
Microsoft AccessVisual Basic Classic
9 Comments1 Solution486 ViewsLast Modified:
Hi Experts,

I have a table that was passed to me by a colleague. The table only has two columns, the first is just an index (specifying order) and the second contains the contents of a flat file that contains multiple rows which represent ONE record where each row has a text field with a keyword followed by a value.

Example:

Table: SOURCE_TABLE
IDX   |   KEYS
1     |    STARTKEY  333
2    |     KEY_A   4000
3    |     KEY_B    5555
4    |     KEY_C    6656
5    |    
6    |     STARTKEY  766
7    |     KEY_A   3000
8    |     KEY_B    2363
9 ....

I would like to parse through every "line" of this table and pull out the real rows to insert into another table.

For instance, the final table would look like this:

Table: DEST_TABLE
START_KEY   |   KEY_A   |   KEY_B   |   KEY_C   |   .....
333      |      4000      |     5555     |     6656     |     ....
766      |      3000      |     2363     |    .....

All key names are known, there are about 30 of them.  I guess I need something to first loop through all records in the source table and then for each row, look at the key names and record their values and also, if the key name changes to "START_KEY", then write out all of the keys found since the last "START_KEY"...

In VB, I would probably start by defining all known keys like this:

DIM START_VALUE=0
DIM KEY_A_VALUE=0
DIM KEY_B_VALUE=0
DIM KEY_C_VALUE=0
.....

But that's where I'm stuck...  How do I "select * from SORUCE_TABLE" and then feed the data to a parser that will split the data and record the values to "insert" a row in my other table?


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros